Page 2 of 10
Posted: Wed May 16, 2007 3:50 pm
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:
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.
Posted: Wed May 16, 2007 3:56 pm
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:
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.
Posted: Wed May 16, 2007 3:58 pm
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.
Posted: Wed May 16, 2007 4:08 pm
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);
?>
Posted: Wed May 16, 2007 4:15 pm
by RobertGonzalez
dvd_title might be vague. Try addind the table name to it like the other fields.
Posted: Wed May 16, 2007 4:25 pm
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'

Posted: Wed May 16, 2007 4:55 pm
by RobertGonzalez
Unless $genre is a number, put single quotes around it.
Posted: Thu May 17, 2007 1:33 pm
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?
Posted: Thu May 17, 2007 1:44 pm
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.

Posted: Thu May 17, 2007 1:55 pm
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.
Posted: Thu May 17, 2007 2:00 pm
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>
Posted: Thu May 17, 2007 2:24 pm
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
Posted: Thu May 17, 2007 2:30 pm
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);
?>
Posted: Thu May 17, 2007 2:35 pm
by RobertGonzalez
My fault for assuming. I thought that was taken care of already.
Posted: Thu May 17, 2007 2:46 pm
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