Filter mysql results help

Question

Hi...

Im have a code that querys a mysql database against a certain criteria, and returns a result. The result then, is displayed in table rows inside a while loop, using the 'mysql_fetch_array' function:

    while($row = mysql_fetch_array($qry_result)){

By the way, this is a classifieds website... Here is my problem:

After the results are displayed, I want the user to be able to click a button or link and then filter the results in 3 ways ('ALL ADS', 'ONLY PRIVATE ADS' or 'ONLY COMPANY ADS'). Is there a smart way to do this without ten pages of code?

I already have a solution, that is to add an if statement before the while loop, to check if a variable is 1, 2 or 3, and then have three while loops:

      if ($ads_shown=='1'){ while loop and show "ALL ADS"}
      else if ($ads_shown=='2'){while loop and show "PRIVATE ADS"}
      else if ($ads_shown=='3'){while loop and show "COMPANY ADS"}

but this doesn't seem like the best way if I am seeking maximum speed in this case! So, any help?

Answer 1

You actually modify the MySQL query to filter the results for you and then run the loop as it would normally. This means one loop and you don't have to maintain 3 versions of it.

  if ($ads_shown=='1'){ $extra_query = "";}
  else if ($ads_shown=='2'){$extra_query = "WHERE `type` = 'Private'";}
  else if ($ads_shown=='3'){$extra_query = "WHERE `type` = 'Company'";}
  $qry_result="SELECT * FROM `ads` ".$extra_query." LIMIT 10";

     while($row = mysql_fetch_array($qry_result))
    {
    }

Of course you are going to need to adapt the code to suit your purposes, but it should work once you get it going. Make sure type and the values for type correctly correlate to whats in your MySQL table.

Update:

There is no real reason this cannot happen before the query but if you insist on it here goes:

     while($row = mysql_fetch_array($qry_result))
    {
        if ($row['type']==$ads_shown) //Where 'type' denotes the type of advert
        {
          //Do stuff
        }
    }

This is less efficient because all the data is being grabbed from MySQL and not just the stuff you need. But assuming there is a field in your table called 'type' that you use to store the type of advert and assuming you have set the value of $ads_shown to correspond, it should work fine.

Answer 2

This is based on what I believe you communicated. Maybe I am entirely wrong. Disregard my message then.

It seems you did not understand the AJAX concept if you are trying to filter MySQL results from the whole set after am user interaction.

You want to select specific ads based on what the user clicks on a website without said website to reload. You believe that you have to send the whole data table alongside the website when it is first loaded.

What you actually want to have is a Javascript that sends a HTTP request to a seperate script that creates an XML from your database which then is sent back to your site. Your still-non-reloading site then executes a JavaScript-based event that creates new content from your XML.

Short answer: Try to learn the concepts of AJAX, use this as a pointer (note: this is a PERL example, but the concept is exactly the same)