CODEWITHSUNDEEP
phpsql
can
can

Reputation: 33

Combine Two Sql Statement

I am trying to create sql statement. It will be find a random user id in database and also from VOTES table, visitor(user id=10) have not give vote yet to a user which determined by randomly.

$userid = 10
//1-Find a user randomly 
$query = $db->query("SELECT id FROM users ORDER BY RAND() LIMIT 1")->fetch(PDO::FETCH_ASSOC);
$randid = $query["id"];

//2-Visitor have not give vote to the user which we determine. This sql result will be empty
("SELECT * FROM votes WHERE userid = '{$userid}' AND receiver = '{$randid}'")

I have to combine these 2 sql statement. I need this because the website will open a user profile which a visitor have not give vote to that user. Thanks

I used these codes before but these codes mostly show a profile which visitor gave vote so i need to add check from votes table

$query = $db->query("SELECT id FROM users ORDER BY RAND() LIMIT 1")->fetch(PDO::FETCH_ASSOC);
$randid = $query["id"];
header("location:index.php?id=$randid");

example link

Upvotes: 0

Views: 30

Answers (1)

sticky bit
sticky bit

Reputation: 37527

Honestly I don't understand what you're trying to do from your description.

But to combine the two queries, the simplest method would be to include the first in the second as a subquery.

SELECT *
       FROM votes
       WHERE userid = '{$userid}'
             AND receiver = (SELECT id
                                    FROM users
                                    ORDER BY rand()
                                    LIMIT 1);

Edit:

Your comment makes me believe you might be searching for something like:

SELECT *
       FROM users u
       WHERE NOT EXISTS (SELECT *
                                FROM votes v
                                WHERE v.receiver = u.id
                                      AND v.userid = '{$userid}')
       ORDER BY rand()
       LIMIT 1;

It gets a random record from users, where no record in votes exists, that has this user from users as receiver and the userid equal to the "visitor" or what ever that 10 is.

That is, if I understand correctly, votes.receiver is the user for whom another one casts a vote and votes.userid is the one who casts the vote.

Upvotes: 1

Related Questions