Drop-down filter menu
Moderator: General Moderators
-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
Drop-down filter menu
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.
Any advice/tutorials much appreciated.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
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.Everah wrote:Can you clear this question up a bit? When you say filter, are you revising search criteria?
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>';- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.
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
-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
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
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.
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
-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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 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.
-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
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";