What is the issue with my php/mysql syntax using min() function?

This is the error I'm getting "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id #2' at line 1"

Below is the php/mysql code

<?php
// Creates connection with database
$link = mysql_connect("webdb","db","password");
if (!$link)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("jokes", $link);
// the line below is where the issue is according to the error
$min_views = "SELECT min(views) FROM db.jokes";
$min = mysql_query($min_views) or die(mysql_error());
$query = "SELECT question, answer, id, views FROM db.jokes WHERE views = $min";
$result = mysql_query($query) or die(mysql_error());

$row = mysql_fetch_array($result) or die(mysql_error());
$question = $row['question'];
$answer = $row['answer'];
$id = $row['id'];
$update = "UPDATE db.jokes SET views = (views + 1) WHERE id = $id";
mysql_query($update) or die ("Error. Please refresh the page or click the home button <br />");
?>


The goal is to find the lowest value in the 'views' column and store it in variable $min

Then, find all rows where the view column = $min and pick a random one of those rows.

The code was working fine until I added in the twist involving finding the lowest view value before selecting the random row.

ALSO - what is a better site to post such a question.. something tells me there are better places to ask these type of questions as to not bore the 99% of the population to which this is all a foreign language.

Anwered 2010-03-16 00:44:36 by Robin T

No, you don't need to 'GROUP BY' when using MIN().

I'm a bit confused with your database schema. It seems as if you have a database named 'jokes', then you are doing stuff on 'db' with a table named also 'jokes'. Can you confirm what your database and table names are?

Also, if the above queries have been modified, the error message that you specified can also be caused by using a field name that is a reserved word (i.e.: it has a different meaning). To be safe, always enclose your field names within the backquotes (the character next to the number '1' on your keyboard).
e.g.: SELECT `question`, `answer`, `id`, `views` FROM ...

Anwered 2010-03-15 23:26:36 by jimbot

I think you have to use a GROUP BY clause if you use the MIN function. Try
SELECT MIN(views) FROM jokes GROUP BY id

However, the best way would be to order the recordset by views and just take the first record. Also, you could add a random component so that if many jokes had the same views value, it would pick one at random:
SELECT id FROM jokes ORDER BY views ASC, RAND() LIMIT 0,1

Note that this has now combined your first two queries. Always better to do it with one query than two. (You can select the other fields as well if you want)