Posted: Sun Jun 17, 2007 3:36 pm
Does it show any results when you run the query in an admin tool?
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
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);
?>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')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";
}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'];
}
?>