Page 1 of 2

Drop-down filter menu

Posted: Fri Feb 02, 2007 10:52 am
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.

Posted: Fri Feb 02, 2007 10:54 am
by RobertGonzalez
Can you clear this question up a bit? When you say filter, are you revising search criteria?

Posted: Fri Feb 02, 2007 11:00 am
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>';

Posted: Fri Feb 02, 2007 11:05 am
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.

Posted: Fri Feb 02, 2007 11:17 am
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,

Posted: Fri Feb 02, 2007 11:59 am
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.

Posted: Sun Feb 18, 2007 11:44 am
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?

Posted: Sun Feb 18, 2007 11:46 am
by feyd
Using the value given to you from the drop-down.

Posted: Sun Feb 18, 2007 12:09 pm
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;
    }

Posted: Mon Feb 19, 2007 2:08 pm
by phpflixnewbie
Would really appreciate some help to progress my code further.

Posted: Mon Feb 19, 2007 2:56 pm
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.

Posted: Mon Feb 19, 2007 3:12 pm
by phpflixnewbie
I would like to go down the drop-down select list route. But dont know where to start.

Posted: Thu Feb 22, 2007 1:19 pm
by phpflixnewbie
Any further help would be greatly appreciated!

Posted: Thu Feb 22, 2007 1:32 pm
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.

Posted: Fri Feb 23, 2007 12:47 pm
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";