如果两个表中都有结果，则两个pdo在一个中选择

Question

So i have a problem i have a user table and a website table i need to select a random user from the users table just there username were there is a row in the website table with there username.Is this possible ?

So i am getting a random user table

$stmt9 = $db->prepare('SELECT * FROM users WHERE coins >= ? ORDER BY RAND() LIMIT 1');
$stmt9->execute( array('1') ) ;
$row = $stmt9->fetch();

Now some how i need to grab a random website were the owner = there username like so

$stmt21 = $db->prepare('SELECT * FROM websites WHERE owner = ?  ORDER BY RAND() LIMIT 1');
$stmt21->execute( array($row['username']) ) ;
$row21 = $stmt21->fetch();

The problem i have is it will grab the username fine but then if the user has not submitted a website the second select will fail so some how i need to put both these select together. And grab a row of the website table were there is a row and were the coins in the user table is over 1.

Answer 1

You could check if the first query returns an empty set, anyway if you want one query and you are not interested in the username you could use this:

SELECT 
  * 
FROM 
  websites w
WHERE w.owner 
  IN (SELECT u.username FROM users u WHERE u.coins >= ?)  
ORDER BY 
  RAND() 
LIMIT 
  1

If you want you can limit the username selected in the subquery adding the random order by and the limit clause.