CODEWITHSUNDEEP
phpmysql
MillerMedia
MillerMedia

Reputation: 3671

MySQL NOT IN statement with other parameters

I've got two pages. On the first page, a random row from my database is selected and displayed. A form is then submitted. On the second page, I want a random row from the database to be selected, but I need the one that was already chosen at random to be excluded from that query.

I've seen NOT IN as a way to do this but it doesn't seem to work in conjunction with RAND() or LIMIT though I may just have the order of the operators wrong. Here's the code I've got so far that's not working:

$sql_new = "SELECT * FROM table ORDER BY RAND() LIMIT 1 NOT IN (SELECT * FROM table WHERE id='$id')";
$result_new = $mysqli->query($sql_new);

This doesn't return any information. Any help on syntax would be great.

Upvotes: 1

Views: 100

Answers (2)

Shankar Narayana Damodaran
Shankar Narayana Damodaran

Reputation: 68486

As MySQL Docs say

To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

You need to provide a column name before the NOT IN keyword. Something like this

SELECT val1 FROM tbl1 WHERE val1 NOT IN (1,2,'a');

Upvotes: 0

Spontifixus
Spontifixus

Reputation: 6660

Try this:

$sql_new = "SELECT * FROM table WHERE id != '$id' ORDER BY RAND() LIMIT 1;";
$result_new = $mysqli->query($sql_new);

Upvotes: 1

Related Questions