Drop-down filter menu
Moderator: General Moderators
- 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
-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
This is my current full code, I've tried adding a Show All value to the array so if selected everything will be displayed. I've added it to the OR conditions but now it displays all results whatever form value you choose.
I would like to keep the Show All option though.
You mentioned there could be a problem with capturing of the values, any ideas whats causing it?
I would like to keep the Show All option though.
You mentioned there could be a problem with capturing of the values, any ideas whats causing it?
Code: Select all
<?php
include 'dbcn.inc.php';
$rlsdate = array('Show All','Last Quarter 2006', 'First Quarter 2007', 'Second Quarter 2007', 'Third Quarter 2007', 'Fourth Quarter 2007');
$genre = array('Show All','Action', 'Animation', 'Childrens', 'Comedy', 'Documentary', 'Drama', 'Horror', 'Musical', 'Science Fiction', 'Thriller');
/***********************************************
If there are form posts, assign and handle here
***********************************************/
$releasedate = '';
if (!empty($_POST['releasedate']) && in_array($_POST['releasedate'], $rlsdate)) {
// The posted form field is valid
$releasedate = $_POST['releasedate'];
}
$dvdgenre = '';
if (!empty($_POST['genre']) && in_array($_POST['genre'], $genre)) {
// The posted form field is valid
$dvdgenre = $_POST['genre'];
}
/***********************************************
Produce some forms
***********************************************/
echo '<form method="post" action="' . basename(__FILE__) . '">' . "\n";
echo '<select name="releasedate">' . "\n";
foreach($rlsdate as $date) {
$selected = $date == $releasedate ? ' selected="selected"' : '';
echo "\t" . '<option value="' . $date . '"' . $selected . '>' .$date.'</option>' . "\n";
}
echo '</select>' . "\n";
echo '<select name="genre">' . "\n";
foreach($genre as $genreItem) {
$selected = $genreItem == $dvdgenre ? ' selected="selected"' : '';
echo "\t" . '<option value="' . $genreItem . '"' . $selected . '>' . $genreItem . '</option>' . "\n";
}
echo '</select>' . "\n";
echo '<input type="submit" name="submit" value="Apply Filter" >' . "\n";
echo '</form>' . "\n";
if(!empty($dvdgenre)) {
$query = "SELECT dvd_titles.dvd_id
, dvd_title
, ROUND((coalesce(sum(totalfilm),0)
+coalesce(sum(empire),0)
+coalesce(sum(radiotimes),0)
+coalesce(sum(independent),0)
+coalesce(sum(mirror),0)
+coalesce(sum(guardian),0)
) /
coalesce( count(totalfilm)
+count(empire)
+count(radiotimes)
+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
, independent
, mirror
, guardian
, rls_quarter
, form_display_all
FROM dvd_titles
, dvd_genres
, critics_ratings
, rls_quarters
WHERE dvd_genres.dvd_id=dvd_titles.dvd_id
AND dvd_titles.dvd_id=critics_ratings.dvd_id
AND dvd_titles.dvd_id=rls_quarters.dvd_id
AND (rls_quarter='$releasedate' OR dvd_genre='$dvdgenre' OR form_display_all='Show All')
GROUP BY dvd_titles.dvd_id
ORDER BY dvd_title";
}
else
// Performing Main 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(independent),0)
+coalesce(sum(mirror),0)
+coalesce(sum(guardian),0)
) /
coalesce( count(totalfilm)
+count(empire)
+count(radiotimes)
+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
, independent
, mirror
, guardian
, form_display_all
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 '<table id="maintable">';
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>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['independent']), '</td>',
'<td>', htmlentities($row['mirror']), '</td>',
'<td>', htmlentities($row['guardian']), '</td>',
'</tr>';
}
echo '</table>';
// Free resultset
mysql_free_result($result);
// Closing connection
mysql_close($Link_ID);
?>- 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
-
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
Show All is being passed as the $_POST var, so when you are using:
in your query, if show all was selected, the query gets transformed into this:
Code: Select all
AND (rls_quarter='$releasedate' OR dvd_genre='$dvdgenre' OR form_display_all='Show All')Code: Select all
AND (rls_quarter='Show All' OR dvd_genre='Show All' OR form_display_all='Show All')-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
OMG!!!!! Just tried one more query and it works!!!!!!. I had added a show_all table and the field containing the Show All values is called form_value.
I think the OR operator will only compare against two values at a time, so it will ignore the third. By putting them in seperate conditions they work seperately, one condition for the Releasedate form and one for the Genre form!!!!!
Everah just ONE last thing PLEASE mate. Is it possible to define a default selected value, so that when the page is loaded the DVD Release Date form always has for example 'Second Quarter 2007' value selected??
I tried adding this value to the selected value where the "produce some forms" code is but nothing happened.
Code: Select all
if(!empty($dvdgenre)) {
$query = "SELECT dvd_titles.dvd_id
, dvd_title
, ROUND((coalesce(sum(totalfilm),0)
+coalesce(sum(empire),0)
+coalesce(sum(radiotimes),0)
+coalesce(sum(independent),0)
+coalesce(sum(mirror),0)
+coalesce(sum(guardian),0)
) /
coalesce( count(totalfilm)
+count(empire)
+count(radiotimes)
+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
, independent
, mirror
, guardian
, form_value
FROM dvd_titles
, dvd_genres
, critics_ratings
, rls_quarters
, show_all
WHERE dvd_genres.dvd_id=dvd_titles.dvd_id
AND dvd_titles.dvd_id=critics_ratings.dvd_id
AND dvd_titles.dvd_id=rls_quarters.dvd_id
AND dvd_titles.dvd_id=show_all.dvd_id
AND (rls_quarter='$releasedate' OR form_value='$releasedate')
AND (dvd_genre='$dvdgenre' OR form_value='$dvdgenre')
GROUP BY dvd_titles.dvd_id
ORDER BY dvd_title";
}Everah just ONE last thing PLEASE mate. Is it possible to define a default selected value, so that when the page is loaded the DVD Release Date form always has for example 'Second Quarter 2007' value selected??
I tried adding this value to the selected value where the "produce some forms" code is but nothing happened.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
In this bit of code:
change $releasedate = '' to:
so that it now looks like:
That should do it for a default value.
Code: Select all
<?php
$releasedate = '';
if (!empty($_POST['releasedate']) && in_array($_POST['releasedate'], $rlsdate)) {
// The posted form field is valid
$releasedate = $_POST['releasedate'];
}
?>Code: Select all
<?php
$releasedate = 'Second Quarter 2007';
?>Code: Select all
<?php
$releasedate = 'Second Quarter 2007';
if (!empty($_POST['releasedate']) && in_array($_POST['releasedate'], $rlsdate)) {
// The posted form field is valid
$releasedate = $_POST['releasedate'];
}
?>-
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
-
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