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 the query im trying:
I've also tried:
First query crashes my PC (even though theres only 162 rows in my tables), second query gives mysql syntax error near: ", dvd_genres, critics_ratings, rls_quarters"
Please help :s
Code: Select all
$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
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'
GROUP BY dvd_titles.dvd_id
ORDER BY dvd_title";I've also tried:
Code: Select all
$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
FROM dvd_titles LEFT JOIN
, dvd_genres
, critics_ratings
, rls_quarters
ON 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'
GROUP BY dvd_titles.dvd_id
ORDER BY dvd_title";Please help :s
- 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
-
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
OK made some progress. Using the following query either $releasedate or $dvdgenre values selected filters the displayed results:
However, I now have a bigger problem to get the $avgrating values working. The average ratings are calculated within the query involving an alias field rounded_rating. Therefore this field doesnt actually exist in the database but is created on the fly, so I can't add matching form values to the database for the query to refer to.
UNLESS, I create yet another table with just average ratings obtained from running this query.
Any other suggestions appreciated.
Code: Select all
$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
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')
GROUP BY dvd_titles.dvd_id
ORDER BY dvd_title";UNLESS, I create yet another table with just average ratings obtained from running this query.
Any other suggestions appreciated.
-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
I've just discovered if I select 'Second Quarter 2007' from the form menu I'm getting no results even though there are definitely Second Quarter 2007 values in the database. Last Quarter 2006 and First Quarter 2007 form values work perfectly though, HELP! :cry
Code: Select all
<?php
include 'dbcn.inc.php';
$rlsdate = array('Released', 'Last Quarter 2006', 'First Quarter 2007', 'Second Quarter 2007', 'Third Quarter 2007', 'Fourth Quarter 2007');
$avgrating = array('Avg.Rating', 'Less Than 1', 'More Than 1', 'More Than 2', 'More Than 3','More Than 4');
$genre = array('Genre', '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'];
}
$averagegrating = '';
if (!empty($_POST['avgrating']) && in_array($_POST['avgrating'], $avgrating)) {
// The posted form field is valid
$averagegrating = $_POST['avgrating'];
}
$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="Released"' : '';
echo "\t" . '<option value="' . $date . '"' . $selected . '>' .$date.'</option>' . "\n";
}
echo '</select>' . "\n";
echo '<select name="avgrating">' . "\n";
foreach($avgrating as $arating) {
$selected = $arating == $averagegrating ? ' selected="Avg.Rating"' : '';
echo "\t" . '<option value="' . $arating . '"' . $selected . '>' . $arating . '</option>' . "\n";
}
echo '</select>' . "\n";
echo '<select name="genre">' . "\n";
foreach($genre as $genreItem) {
$selected = $genreItem == $dvdgenre ? ' selected="Genre"' : '';
echo "\t" . '<option value="' . $genreItem . '"' . $selected . '>' . $genreItem . '</option>' . "\n";
}
echo '</select>' . "\n";
echo '<input type="submit" name="submit" value="Send the form" >' . "\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
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')
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
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);
?>
-
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 decided to remove the $avgrating array and form from the page. However, my small problem now is, when I value is not selected in the form and I refesh the page or click send the form it not displaying all the results, its as if the else statement is not working.
Please help, once I have this sorted the page is virtually complete.
Please help, once I have this sorted the page is virtually complete.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
How many results are showing and how many are you expecting? Have you counted at all using mysql_num_rows()?
-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am