Using ORDER BY RAND() with multiple WHERE clauses mysql

Question

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'] . '
'; echo 'Does this top require pants?' . $row['needs_pants'] . '
'; echo 'What\'s the colour id of this top?' . $row['colour_id'] . '
'; 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…

Accepted Answer

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.

Using ORDER BY RAND() with multiple WHERE clauses mysql

Solution

Problem