Drop-down filter menu

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Yes, I get no PHP errors and the form menus are still there, but I dont know how to check if its posting anything or not when I select a select value. :?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

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

Post by phpflixnewbie »

I think the main difference is this line:

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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

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.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

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:

Code: Select all

echo '<select name="releasedate">' . "\n"; 
I have now added my database connection SSI, main SQL query and echoed the full results in a table as below:



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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

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

Post by phpflixnewbie »

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.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

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);


?>
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Instead of this:

Code: Select all

if($dvdgenre = true){
try this:

Code: Select all

if(!empty($dvdgenre)) {
See if that changes anything.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Ok i think thats working, its now displaying all my results until I choose a value and submit it. But I tried choosing the 'Action' option from the genre menu and it says:

Query failed: Unknown column 'Action' in 'where clause'


Obviously I need to take another look at my query.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Werhoooooooooooooo, just put the $dvdgenre variable in single quotes in my query and it works!!!!!! 8O

Now just need to work out what im gonna do with the other form values :?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Keep going. This is almost exciting seeing someone get the hang of it right in front of our eyes.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Everah wrote:Keep going. This is almost exciting seeing someone get the hang of it right in front of our eyes.
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.
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
:)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Have you looked at JOIN queries yet?
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

This is using implicit joins. I guess I should be using a LEFT join? But could I in theory use the OR operator to include the three variables in the query?
Post Reply