CODEWITHSUNDEEP
phpmysqlrandomsql-order-by
Tori Henri
Tori Henri

Reputation: 763

Using ORDER BY RAND() with multiple WHERE clauses mysql

I know that order by rand() isn't the fastest way of drawing a random value from a database, but my database is small, and at this point; I just want it to work! haha. Here's my code:

include('includes/dbc.php');
$top_query = "SELECT * FROM top WHERE 'occasion_id =" . $occasion . "' AND 'temperature_id = " . $temperature . "' AND 'user_id = " . $user_id . "'ORDER BY RAND() LIMIT 1";
$top_result = mysqli_query($dbc, $top_query) or die (' The top SELECT query is broken.');
mysqli_close ($dbc);

while($row= mysqli_fetch_array($top_result)) {
    echo 'This top has an id of:' . $row['top_id']  . '<br> ';
    echo 'Does this top require pants?' . $row['needs_pants']  . '<br>';
    echo 'What\'s the colour id of this top?' . $row['colour_id'] . '<br>';
    echo  $row['value'];
}

For some reason this just doesn't work and I will just show up blank when I try to run my array. It worked before I threw in the order by "rand() limit 1" bit, but obviously I got every value instead of just one random one.

Can anyone see where I went wrong? Thanks so much!

Upvotes: 3

Views: 979

Answers (1)

Jason McCreary
Jason McCreary

Reputation: 72991

Your query is malformed. It's not dying, it's just not returning results.

Note the errant single quotes:

$top_query = "SELECT * FROM top WHERE 'occasion_id =" . $occasion . "' AND 'temperature_id = " . $temperature . "' AND 'user_id = " . $user_id . "'ORDER BY RAND() LIMIT 1";

You can prove this by outputting your query then running it in MySQL directly (such as through PHPMyAdmin):

echo $top_query;

Also functions like mysqli_error() and mysqli_num_rows() help determine query results.

To move you on your way, the SQL should look more like:

$top_query = "SELECT * FROM top WHERE occasion_id = '" . $occasion . "' AND temperature_id = '" . $temperature . "' AND user_id = '" . $user_id . "' ORDER BY RAND() LIMIT 1";

Some additional notes:

  • Read up on SQL Injection.
  • Familiarize yourself with when you need to quote literal values in MySQL. In your case these are all ID columns (theoretically integers) and therefore don't need to be quoted.
  • Be mindful not to prematurely close your MySQL connection. PHP will actually do this automatically at the end of the script.
  • As commented by webbiedave, also look into Prepared Statements.

Upvotes: 8

Related Questions