Drop-down filter menu

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Drop-down filter menu

Post by phpflixnewbie »

I want to add drop-down menus that allow the visitor to filter the table of results which is displayed. Is it just a case of adding a seperate sql query to each item in the drop-down menu and when the user clicks on an item in the menu the query runs and filters the table?
Any advice/tutorials much appreciated.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Can you clear this question up a bit? When you say filter, are you revising search criteria?
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Everah wrote:Can you clear this question up a bit? When you say filter, are you revising search criteria?
Below is the current code im using to display all data in my database. I want the user to be able to filter the table by Average Rating (eg, 1 out of 5, 2 out of 5, 3 out of 5 etc) and/or by Genre and/or by DVD Release date.

Code: Select all

// Performing SQL query
$query =  "SELECT dvd_titles.dvd_id
         	        , dvd_title
         	        , ROUND((coalesce(sum(totalfilm),0)
         	          +coalesce(sum(empire),0)
         	          +coalesce(sum(radiotimes),0)
			          +coalesce(sum(times),0)
			          +coalesce(sum(independent),0)
			          +coalesce(sum(mirror),0)
			          +coalesce(sum(guardian),0)
			         ) /
			         coalesce( count(totalfilm)
			                    +count(empire)
			                    +count(radiotimes)
			                    +count(times)
			                    +count(independent)
			                    +count(mirror)
			                    +count(guardian)
			               ),1) as rounded_rating
			        , prodn_year
			        , date_format(dvd_rlsdate,'%d %b %y') as rlsdate
			        , dvd_genre
			        , totalfilm
			        , empire
			        , radiotimes
			        , times
			        , independent
			        , mirror
			        , guardian
			     FROM dvd_titles
			        , dvd_genres
			        , critics_ratings
			    WHERE dvd_genres.dvd_id=dvd_titles.dvd_id
			      AND dvd_titles.dvd_id=critics_ratings.dvd_id
			    GROUP BY dvd_titles.dvd_id
		        ORDER BY dvd_title";



$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML


echo '<table>';
echo "<tr><th>Title</th><th>Avg.<br>Rating</th><th>Year</th><th>DVD<br>Release Date</th><th>Main Genre</th><th>TF</th><th>EM</th><th>RT</th><th>TI</th><th>ID</th><th>MR</th><th>GN</th></tr>";  // Setting Column Names
while( $row=mysql_fetch_array($result, MYSQL_ASSOC) ) {

           echo '<tr>',
                        '<td><a href="detail.php?id=', $row['dvd_id'], '">', htmlentities($row['dvd_title']), '</a></td>',
                        '<td>', htmlentities($row['rounded_rating']), '</td>',
                        '<td>', htmlentities($row['prodn_year']), '</td>',
                        '<td>', htmlentities($row['rlsdate']), '</td>',
                        '<td>', htmlentities($row['dvd_genre']), '</td>',
                        '<td>', htmlentities($row['totalfilm']), '</td>',
                        '<td>', htmlentities($row['empire']), '</td>',
                        '<td>', htmlentities($row['radiotimes']), '</td>',
                        '<td>', htmlentities($row['times']), '</td>',
                        '<td>', htmlentities($row['independent']), '</td>',
                        '<td>', htmlentities($row['mirror']), '</td>',
                        '<td>', htmlentities($row['guardian']), '</td>',

                '</tr>';

}


echo '</table>';
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Ok, so filter implies that you want the user to be able to see only certain rows where the filter criteria is met. That means you put in a check to see if the criteria was passed, and if it was, modify the WHERE clause of the query so that the data fetched is what they want.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

That means you put in a check to see if the criteria was passed....
Would this involve using some kind of if() clause or while loop, im still learning PHP, so would much appreciate a snippet of code which would point me in the right direction,
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Yes, it would involve an if().

Think about the logic involved... if there are not filter settings then you should show the entire query result set. Else, if the filter setting is 'Name' then modify the query to pull only 'Name' related rows elseif the filter setting is 'Title' then modify the query to pull only 'Title' related rows elseif the filter setting is 'Average' then modify the query to pull only 'Average' related rows.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

I understand I will need a variable for each filter, but how do I do a check to see if the user has selected a filter or not?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Using the value given to you from the drop-down.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

I've started some code below, but i dont know how I would use more than one filter variable, and therefore sql query. Please advise.


Code: Select all

$usefilter = false;

$query =  "SELECT dvd_titles.dvd_id
                        , dvd_title
                        , ROUND((coalesce(sum(totalfilm),0)
                          +coalesce(sum(empire),0)
                          +coalesce(sum(radiotimes),0)
                                  +coalesce(sum(times),0)
                                  +coalesce(sum(independent),0)
                                  +coalesce(sum(mirror),0)
                                  +coalesce(sum(guardian),0)
                                 ) /
                                 coalesce( count(totalfilm)
                                            +count(empire)
                                            +count(radiotimes)
                                            +count(times)
                                            +count(independent)
                                            +count(mirror)
                                            +count(guardian)
                                       ),1) as rounded_rating
                                , prodn_year
                                , date_format(dvd_rlsdate,'%d %b %y') as rlsdate
                                , dvd_genre
                                , totalfilm
                                , empire
                                , radiotimes
                                , times
                                , independent
                                , mirror
                                , guardian
                             FROM dvd_titles
                                , dvd_genres
                                , critics_ratings
                            WHERE dvd_genres.dvd_id=dvd_titles.dvd_id
                              AND dvd_titles.dvd_id=critics_ratings.dvd_id
                            GROUP BY dvd_titles.dvd_id
                        ORDER BY dvd_title"; 


$result = mysql_query($query) or die('Query failed: ' . mysql_error());



if (!empty($usefilter))
    {
        $usefilter = true;
    }
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Would really appreciate some help to progress my code further.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

What you are talking about doing is either going to involve a dropdown menu (like a select list) or sortable header fields. You can do that by either passing the form value from the select list or by pass the querystring value of the table header links (like a sort by this column type of thing).

There are a number of ways to accomplish what you want to do.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

I would like to go down the drop-down select list route. But dont know where to start.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Any further help would be greatly appreciated!
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

phpflixnewbie wrote:I've started some code below, but i dont know how I would use more than one filter variable, and therefore sql query. Please advise.
What you want to do is put an if clause somewhere in the code that handles if there was a form post. It would also use the posted data to set some vars that tell the query what to do. If the vars aren't set, then run the query as is. If the vars are set, use them to modify the query to return what you want.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Below is the form code and a variable which would use a query to find all the titles which have an avg. rating of more than 4. But i dont know how to use the query which displays ALL the data along with this code and how to add more variables and queries that will work for the other form select options.

Code: Select all

<form action="main.php" method="post">
<select name="rlsdate">
<option value=""selected="selected">Released</option>
<option value="3ormore">in the last 30 days</option>
<option value="2ormore">in the last 60 days</option>
<option value="1ormore">in the last 90 days</option>
<option value="1ormore">in the last 6 months</option>
<option value="1ormore">in the last year</option>
<option value="1ormore">over a year ago</option>
</select>
<select name="avgrating">
<option value="" selected="selected">Avg.Rating</option>
<option value="4ormore">> 4 </option>
<option value="3ormore">> 3 </option>
<option value="2ormore">> 2 </option>
<option value="1ormore">> 1 </option>
</select>
<select name="genre">
<option value="" selected="selected">Genre</option>
<option value="action">Action</option>
<option value="animation">Animation</option>
<option value="kids">Childrens</option>
<option value="comedy">Comedy</option>
<option value="docu">Documentary</option>
<option value="drama">Drama</option>
<option value="horror">Horror</option>
<option value="musical">Musical</option>
<option value="scifi">Science-Fiction</option>
<option value="thriller">Thriller</option>
</select>

<input type="submit" value="Apply Filter" />
</form>


$4ormore = $_POST["avgrating"];

$query =  "SELECT dvd_titles.dvd_id
	        , dvd_title
	        , ROUND((coalesce(sum(totalfilm),0)
	          +coalesce(sum(empire),0)
	          +coalesce(sum(radiotimes),0)
          +coalesce(sum(times),0)
          +coalesce(sum(independent),0)
          +coalesce(sum(mirror),0)
          +coalesce(sum(guardian),0)
         ) /
         coalesce( count(totalfilm)
                    +count(empire)
                    +count(radiotimes)
                    +count(times)
                    +count(independent)
                    +count(mirror)
                    +count(guardian)
               ),1) as rounded_rating
        , prodn_year
        , date_format(dvd_rlsdate,'%d %b %y') as rlsdate
        , dvd_genre
        , totalfilm
        , empire
        , radiotimes
        , times
        , independent
        , mirror
        , guardian
     FROM dvd_titles
        , dvd_genres
        , critics_ratings
    WHERE dvd_genres.dvd_id=dvd_titles.dvd_id
      AND dvd_titles.dvd_id=critics_ratings.dvd_id
      AND rounded_rating >4
    GROUP BY dvd_titles.dvd_id
      ORDER BY dvd_title";
Post Reply