具有复杂连接的SQL查询

Question

I want to allow users to rate logos. The query should select the next logo that this user hasn't rated yet.

On review.php we select a logo:

<?php 
$dbconnect = mysqli_connect("localhost", "***", "***", "***");
if (!isset($_SESSION)) {
session_start();
}
$thisuser = $_SESSION['uid']

$Recordsetlogos = mysqli_query($dbconnect, "SELECT * FROM tbllogos WHERE status = 'live' LIMIT 1"); //query
$row_Recordsetlogos = mysqli_fetch_assoc($Recordsetlogos); 
?>

So far so good.

The user sees the logo, selects a rating and submits a form.

On the next page we collect the data from the form the user filled out and we insert a record into another table in the database:

<?php 
$dbconnect = mysqli_connect("localhost", "***", "***", "***");

$did = $_POST['did']; // user ID
$lid = $_POST['lid']; // logo ID
$review = $_POST['review']; // rating
$desrank = $_POST['desrank']; // user's rank

$updateSQL = mysqli_query($dbconnect, "INSERT INTO tblreviews (did, lid, vote, desrank) VALUES ('$did', '$lid', '$review', '$desrank')");

mysqli_close($dbconnect);

header("Location: review.php"); // send user back
exit;

The user arrives back at review.php, to rate the next logo.

When review.php loads, it shows the same logo that the user just rated.

I realize that this is probably a simple join, but I can't get my head around it.

Here is what it should do, in English...

Select from tbllogos
where status = 'live'
AND
logo and $thisuser do not appear as a pair in tblreviews. 

Thanks for your help!

Answer 1

You need a LEFT JOIN in your select query and filter in WHERE only rows without joined row.

SELECT l.* FROM tbllogos l 
LEFT JOIN tblreviews r ON r.lid = l.id AND r.did = $thisuser
WHERE l.status = 'live' AND r.lid IS NULL
LIMIT 1

I assumed that there is a column tbllogos.id as primary key. If it's named somehow else, you of course need to modify the query.

This query says "select logos that doesn't have review made by this user"