Drop-down filter menu
Moderator: General Moderators
-
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
There is an easy explanation. Let see if you can spot the explanation when you look at this code versus the code you posted.
Code: Select all
<?php
$rlsdate = array('last30', 'last60', 'last90', 'last6mth', 'lastyr', 'overayr');
$avgrating = array('4ormore', '3ormore', '2ormore', '1ormore');
$genre = array('action', 'animation', 'kids', 'comedy', 'docu', 'drama', 'horror', 'musical', 'scifi', '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="selected"' : '';
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="selected"' : '';
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="selected"' : '';
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";
?>-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
I think the main difference is this line:
I didnt know where in the code to put the method and action code. Ive tried changing the basename(__FILE__) to main.php but it doesnt work. I'm assuming you used basename() because you didnt know what the URL was or do I NEED to use basename()?.
The other main difference was the use of the equality operators to match the selected value to the validated array.
This code seems to post data, thankyou.
Now on to the SQL queries?
Code: Select all
echo '<form method="post" action="' . basename(__FILE__) . '">' . "\n";I didnt know where in the code to put the method and action code. Ive tried changing the basename(__FILE__) to main.php but it doesnt work. I'm assuming you used basename() because you didnt know what the URL was or do I NEED to use basename()?.
The other main difference was the use of the equality operators to match the selected value to the validated array.
This code seems to post data, thankyou.
Now on to the SQL queries?
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Bingo. You should always specify the form action and the method in the <form> tag. You should also provide a means to actuall submit the form (like the submit button I added).
As for basename(__FILE__)... that takes the current filename without its path. I use that to post the form back to itself. You can specify any file you name you want as the "action" value.
As for basename(__FILE__)... that takes the current filename without its path. I use that to post the form back to itself. You can specify any file you name you want as the "action" value.
-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
The only thing that doesnt echo are the menu names, e.g) releasedate, avgrating and genre
Dont understand why it doesnt because we have lines of code like:
I have now added my database connection SSI, main SQL query and echoed the full results in a table as below:
Full current code:
This code works displaying all results and forms. I'm guessing now I have to add SQL queries which use the variables from the $_POST data?, not sure how to start this though.
Dont understand why it doesnt because we have lines of code like:
Code: Select all
echo '<select name="releasedate">' . "\n"; Full current code:
Code: Select all
<?php
include 'dbcn.inc.php';
$rlsdate = array('last30', 'last60', 'last90', 'last6mth', 'lastyr', 'overayr');
$avgrating = array('4ormore', '3ormore', '2ormore', '1ormore');
$genre = array('action', 'animation', 'kids', 'comedy', 'docu', 'drama', 'horror', 'musical', 'scifi', '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="selected"' : '';
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="selected"' : '';
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="selected"' : '';
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";
// 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(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);
?>
This code works displaying all results and forms. I'm guessing now I have to add SQL queries which use the variables from the $_POST data?, not sure how to start this though.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
You have the code that assigns values from the post array into variables. Now use those variable in your queries. Quick example:
Code: Select all
<?php
$sql = "SELECT * FROM `table` WHERE `field_id` = $variable";
?>-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
I have made a rather dumb mistake. There are no field values in my dvd_titles table which match values in the form releasedate. I have a dvd_rlsdate field with dates in the form of 2007-06-11, so that would be classed as a 'last30' days $rlsdate array value. Regarding the $avgrating values form the values in my database field critics_ratings are not the average ratings but the actual rating. The average is calculated in the SQL query. However, regarding the $genre values, I have matching values in my database table.
I feel incredibly stupid for overlooking this obvious mistake, is there some way around this or should I consider changing my database tables or form field values?
Once again I apologise Everah.
I feel incredibly stupid for overlooking this obvious mistake, is there some way around this or should I consider changing my database tables or form field values?
Once again I apologise Everah.
-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
Until I work out what to do with my tables or form fields, I am trying to go ahead with the code for the "genre" form as their values match values in my dvd_genres table. I tried the following code but I get no results displayed. Please advise.
Code: Select all
<?php
include 'dbcn.inc.php';
$rlsdate = array('last30', 'last60', 'last90', 'last6mth', 'lastyr', 'overayr');
$avgrating = array('4ormore', '3ormore', '2ormore', '1ormore');
$genre = array('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="selected"' : '';
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="selected"' : '';
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="selected"' : '';
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($dvdgenre = true){
$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
AND 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);
?>
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Instead of this:
try this:
See if that changes anything.
Code: Select all
if($dvdgenre = true){Code: Select all
if(!empty($dvdgenre)) {-
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
-
phpflixnewbie
- Forum Contributor
- Posts: 132
- Joined: Fri Nov 17, 2006 11:46 am
Many many thanks for the help so far Everah. Regarding the other forms, the only way I can think of doing it is adding another field to my dvd_title table which contains the release dates.Everah wrote:Keep going. This is almost exciting seeing someone get the hang of it right in front of our eyes.
This field would be called something like html_form_name and then the form values would be added so that I can match them to the variable in my query. I' d also have to do the same for the critics_ratings table.
This database part I can handle myself.
Once I've setup these extra fields i'm not sure how I would use them in my PHP code. Would it require 3 seperate SQL queries for each variable array or should I try and include all 3 variables in the one query?
Something like:
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
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 dvd_genre=$dvdgenre OR dvd_rlsdate=$releasedate OR rounded_rating=$averagegrating
GROUP BY dvd_titles.dvd_id
ORDER BY dvd_title"; Your wise words are 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