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

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

Post by RobertGonzalez »

Does it show any results when you run the query in an admin tool?
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Yes the main query works fine in sql admin. The query also works when you first load the page, but after you have selected a form value and then DESELECTED a form value it displays nothing.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

That means there is something happening wrong in the capturing of values that are being sent into the query in your code.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

This is my current full code, I've tried adding a Show All value to the array so if selected everything will be displayed. I've added it to the OR conditions but now it displays all results whatever form value you choose.
I would like to keep the Show All option though.
You mentioned there could be a problem with capturing of the values, any ideas whats causing it?

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


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

Post by RobertGonzalez »

Am I missing something or does the else not have an opening brace?
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Added opening brace, but still no results :cry:
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

After trying numerous different queries it seems that the 'Show All' value data is definitely not being captured. I feel I am so close to completing this application - Please Help! :cry:
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Show All is being passed as the $_POST var, so when you are using:

Code: Select all

AND (rls_quarter='$releasedate' OR dvd_genre='$dvdgenre' OR form_display_all='Show All')
in your query, if show all was selected, the query gets transformed into this:

Code: Select all

AND (rls_quarter='Show All' OR dvd_genre='Show All' OR form_display_all='Show All')
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

OMG!!!!! Just tried one more query and it works!!!!!!. I had added a show_all table and the field containing the Show All values is called form_value.

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

}
I think the OR operator will only compare against two values at a time, so it will ignore the third. By putting them in seperate conditions they work seperately, one condition for the Releasedate form and one for the Genre form!!!!! 8O

Everah just ONE last thing PLEASE mate. Is it possible to define a default selected value, so that when the page is loaded the DVD Release Date form always has for example 'Second Quarter 2007' value selected??
I tried adding this value to the selected value where the "produce some forms" code is but nothing happened.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

In this bit of code:

Code: Select all

<?php
$releasedate = '';
if (!empty($_POST['releasedate']) && in_array($_POST['releasedate'], $rlsdate)) {
  // The posted form field is valid
  $releasedate = $_POST['releasedate'];
} 
?>
change $releasedate = '' to:

Code: Select all

<?php
$releasedate = 'Second Quarter 2007';
?>
so that it now looks like:

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'];
} 
?>
That should do it for a default value.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

OK, that selects it, but is there a way for it also to post that default value on page load?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

When the form posts, it will have that value in that form field $_POST value.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Yeh but it only posts the value when a user clicks the submit button, can it post the value automatically when the page is opened?
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

I'm so dumb sometimes (Everah thinks "only SOMETIMES?!!!" :rofl: )
All I need to do is change my main query to rls_quarter='Second Quarter 2007' and it will display the required results on page load, with the corresponding form value chosen using the ammended code from your last post. :D
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

So are you good now?
Post Reply