Code: Select all
SELECT VERSION();Code: Select all
<?php
echo 'MySQL version is: ' . mysql_get_server_info();
?>Code: Select all
<?php
$genre = $_POST['genre'];
$sql = "SELECT * FROM `mytable` WHERE category = '$genre'";
?>Moderator: General Moderators
Code: Select all
SELECT VERSION();Code: Select all
<?php
echo 'MySQL version is: ' . mysql_get_server_info();
?>Code: Select all
<?php
$genre = $_POST['genre'];
$sql = "SELECT * FROM `mytable` WHERE category = '$genre'";
?>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.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
SELECT VERSION();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 echo 'MySQL version is: ' . mysql_get_server_info(); ?>
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.Code: Select all
<?php $genre = $_POST['genre']; $sql = "SELECT * FROM `mytable` WHERE category = '$genre'"; ?>
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);
?>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>Code: Select all
<form action="#" name="MyForm" method="post">form stuff</form>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);
?>Code: Select all
<form action="main.php" method="post">
<select name="gid">
<option selected value="">Genre</option>
</form>