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 »

The error message means that your MySQL version probably doesn't support that function. You can find out your version of MySQL by running:

Code: Select all

SELECT VERSION();
as a query. You can also do this:

Code: Select all

<?php
echo 'MySQL version is: ' . mysql_get_server_info();
?>
As for passing what was captured, use the $_POST array value for the form fields name as an index (like $_POST['genre']) and feed that to the WHERE clause of the query as a variable:

Code: Select all

<?php
$genre = $_POST['genre'];
$sql = "SELECT * FROM `mytable` WHERE category = '$genre'";
?>
Please do not use the code above as it is just an example of how to do what you want. You should thouroughly inspect the information passed by the form and validate/sanitize it as needed before sending it through mysql_real_escape_string() on its way into the query.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Everah wrote:The error message means that your MySQL version probably doesn't support that function. You can find out your version of MySQL by running:

Code: Select all

SELECT VERSION();
as a query. You can also do this:

Code: Select all

<?php
echo 'MySQL version is: ' . mysql_get_server_info();
?>
As for passing what was captured, use the $_POST array value for the form fields name as an index (like $_POST['genre']) and feed that to the WHERE clause of the query as a variable:

Code: Select all

<?php
$genre = $_POST['genre'];
$sql = "SELECT * FROM `mytable` WHERE category = '$genre'";
?>
Please do not use the code above as it is just an example of how to do what you want. You should thouroughly inspect the information passed by the form and validate/sanitize it as needed before sending it through mysql_real_escape_string() on its way into the query.
The thing is that query has always worked, the only thing Ive changed is using the cleaned up version of the code that you did for me. For reference im using mysql version 5.0.27.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

It is possible that borked the query when I cleaned it up. Try removing it and using the one from your code up the thread a little bit and see if that fixes it.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

OK query working again using old code I've now added the $_POST['genre'] and edited the where clause to include the variable but im getting an SQl syntax error: 'GROUP BY dvd_titles.dvd_id ORDER BY dvd_title'


Code: Select all

 <?php

$Host = 'localhost';
$User = 'username';
$Password = 'password';
$Database = 'dbname';

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
			     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 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);
?>
Last edited by phpflixnewbie on Wed May 16, 2007 4:23 pm, edited 1 time in total.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

dvd_title might be vague. Try addind the table name to it like the other fields.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Hmmm, ok I edited my previous post to show the edited query and now I get a syntax error near:

'GROUP BY dvd_titles.dvd_id ORDER BY dvd_titles.dvd_title'



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

Post by RobertGonzalez »

Unless $genre is a number, put single quotes around it.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Added single quotes and now get error:

failed: Unknown column 'genre_ids.genre' in 'where clause'

Which simply isnt true, does the rest of my PHP look ok?
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Whooops, just realised I hadnt added genre to the select statement and genre_ids to from statement, sorry.
The query now works but no results are being displayed, just the table header is showing.
Also the form doesnt display the select name genre category first, it just lists all the options.

Please advise further when you can.

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

Post by RobertGonzalez »

Run your page again, the view the source of the page in the browser and post back the form code from the source HTML.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Heres the forms source output:

Code: Select all

<select name="genre"><option value="0">Action</option><option value="1">Animation</option><option value="2">Childrens</option><option value="3">Comedy</option></select>
User avatar
guitarlvr
Forum Contributor
Posts: 245
Joined: Wed Mar 21, 2007 10:35 pm

Post by guitarlvr »

is there anywhere in the source where you begin your form? Such as:

Code: Select all

<form action="#" name="MyForm" method="post">form stuff</form>
Wayne
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

No their isnt, should their be? this is the current complete code:

Code: Select all


<?php

$Host = 'localhost';
$User = 'username';
$Password = 'password';
$Database = 'dbname';

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

Post by RobertGonzalez »

My fault for assuming. I thought that was taken care of already.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

OK i've added this form code

Code: Select all

<form action="main.php" method="post">
<select name="gid">
<option selected value="">Genre</option>
</form>
But now I get two seperate drop-down lists, the first just called Genre, the second lists the options and I still dont get any results displayed
Last edited by phpflixnewbie on Thu May 17, 2007 2:52 pm, edited 1 time in total.
Post Reply