Drop-down filter menu
Moderator: General Moderators
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
OK, I think it is time to step back and write some HTML then put your PHP code into it. What you are trying to do at the moment is some pretty basic HTML coding and should be something that you are comfortable with before moving in to PHP.
Clean up your HTML and make that work. When it is done, integrate your PHP code into it. If you are still having problems, post your entire code back here.
Clean up your HTML and make that work. When it is done, integrate your PHP code into it. If you are still having problems, post your entire code back here.
-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
Ok this is my full code including a complete form, but I dont know how to integrate the PHP into it.
Code: Select all
<form action="main.php" method="post">
<select name="gid">
<option selected value="">Genre</option>
<option value="">Action</option>
<option value="">Animation</option>
<option value="">Childrens</option>
<option value="">Comedy</option>
</select>
</form>Code: Select all
<?php
$Host = 'localhost';
$User = 'username';
$Password = 'password';
$Database = 'db_name';
if (!$Link_ID = mysql_connect($Host, $User, $Password)) {
echo 'Failed to connect to Server=' . $Host;
exit;
}
if (!mysql_select_db($Database)) {
echo '<br>Cannot use database=' . $Database;
exit;
}
$genres = array('Action', 'Animation', 'Childrens', 'Comedy');
echo '<select name="genre">';
foreach ($genres as $k => $v) {
echo '<option value="' . $k . '">' . $v. '</option>';
}
echo '</select>';
//Handle posts of the select from above
if (isset($_POST['genre'])) {
$genre = null;
$gid = intval($_POST['genre']);
if ($gid < count($genres)) {
$genre = $gid;
}
if (!is_null($genre)) {
// Handle the selection of the users choice here
}
}
$genre = $_POST['genre'];
$sql = 'select genre_id, genre from genre_ids';
if (!$result = mysql_query($sql)) {
exit('<p>unable to obtain genre list from database using SQL: ' . $sql . '.</p>');
}
$genre = $_POST['genre'];
// Performing SQL query
$sql = "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
, genre
, genre_id
, dvd_genre
FROM dvd_titles
, dvd_genres
, critics_ratings
, genre_ids
WHERE dvd_genres.dvd_id=dvd_titles.dvd_id
AND dvd_titles.dvd_id=critics_ratings.dvd_id
AND genre_ids.genre = '$genre'
GROUP BY dvd_titles.dvd_id
ORDER BY dvd_titles.dvd_title";
$result = mysql_query($sql) or die("Query ($sql) failed: " . mysql_error());
// Printing results in HTML
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)) {
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>';
// Closing connection
mysql_close($Link_ID);
?>-
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
- 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
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Right, but you are asking the database to give you all records where genre_id is equal to a PHP variable called $genre, which happens to be set to a $_POST (or form field) value, which there doesn't appear to be. So in essence you are telling the database to give you all records where genre_id is NULL, which there probably aren't any which would lead me to believe you are getting an empty result set.
-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
I thought $genre was the variable which matched $gid and therefore a genre value from my database table, the code in question below was written by yourself and I dont fully understand it:
//Handle posts of the select from above
//Handle posts of the select from above
Code: Select all
if (isset($_POST['genre'])) {
$genre = null;
$gid = intval($_POST['genre']);
if ($gid < count($genres)) {
$genre = $gid;
}
if (!is_null($genre)) {
// Handle the selection of the users choice here
}
}- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Right, in the code I posted I assumed there was a field in the HTML form called genre, which would be the value of the genre_id. When passed to the page, the code looks to see if there is a form field that was passed called 'genre'. If it is set, then the integer value of that field is then assigned to the $genre variable as long as the genre integer value is less that the total number of members of the $genres array.
The code I gave was meant as a guide, not to be taken as-is and placed in your app.
The code I gave was meant as a guide, not to be taken as-is and placed in your app.
-
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
Appreciate the help, heres the form i'd like to use:
Code: Select all
<form action="main.php" method="post">
<select name="rlsdate">
<option value=""selected="selected">Released</option>
<option value="last30">in the last 30 days</option>
<option value="last60">in the last 60 days</option>
<option value="last90">in the last 90 days</option>
<option value="last6mth">in the last 6 months</option>
<option value="lastyr">in the last year</option>
<option value="overayr">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>
</form>