CODEWITHSUNDEEP
phpmysqlsql
blogo
blogo

Reputation: 329

SQL query with complex join

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!

Upvotes: 0

Views: 76

Answers (2)

Jakub Matczak
Jakub Matczak

Reputation: 15656

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"

Upvotes: 2

e4c5
e4c5

Reputation: 53734

you mean "On the next page we collect the data from the form the user filled out and allow him to carry out an SQL injection attack on our database"

Leaving that aside for the moment, look at your first query

 "SELECT * FROM tbllogos WHERE status = 'live' LIMIT 1"

Now look at your insert

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

The two tables are different. Unless you have a trigger on the tblreviews table, there is no way that the status of any of the records in tbllogos is going to change.

Fix: do an update n the tbllogos table.

 UPDATE tbllogos set status = 'dead' where id = ?

Note also that you are redirecting the user without checking if the insert succeed.

Upvotes: 1

Related Questions