1

I am trying to have the script pick a row from a table at random, and keep searching if it exists in another table.

The code might explain it better

do {
    // get a random row from table A
    $search = mysqli_query($db, 'SELECT * FROM `a` ORDER BY RAND() LIMIT 1');

    // get the id
    $row = mysqli_fetch_assoc($search);
    $row_id = $row['id'];

    // check table B to see if the row id exists
    $check = mysqli_query($db, 'SELECT `id` FROM `b` WHERE `item` = "' . $row_id . '" LIMIT 1');
    $result = mysqli_num_rows($check);
} while ($result === 1);

From my understanding, it should loop until it finds a song which exists in table A, but not B, and then move on, and I can use $row_id.

Now this works, but every so often it will return with a row that exists in table B

Improve this question
3
  • ORDER BY RAND is performance murder, do not use on large table, but for a small one its ok Commented Oct 30, 2018 at 21:51
  • Performing iterated queries is performance murder, try to achieve your desired result with minimal trips to the database. Commented Oct 30, 2018 at 21:57
  • A do while, loops one time, then checks the condition and then loops again if it's true. $result === 1 means result must be an int, not true not "1" but 1 and only 1, is possible you have duplicate data? this would also fail Commented Oct 30, 2018 at 22:41

3 Answers 3

Reset to default
3

This would be easier with a LEFT JOIN query to find all songs in a that don't exist in b and then select a random one from that list. Note also that it's always preferable to select the columns you actually want, rather than using *. Based on your code, this query should work:

SELECT a.id
FROM a
LEFT JOIN b ON b.item = a.id
WHERE b.id IS NULL
ORDER BY RAND()
LIMIT 1

You can then remove your do loop and replace it with this code. Note I've added a check that there is actually a song in a which doesn't exist in b:

$search = mysqli_query($db, 'SELECT a.id FROM a LEFT JOIN b ON b.item = a.id
                              WHERE b.id IS NULL ORDER BY RAND() LIMIT 1');
// get the id
$row;
if (!$row = mysqli_fetch_assoc($search)) {
    echo "No non-matching songs found!"
    $row_id = 0;
}
else {
    $row_id = $row['id'];
}
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

@mickmackusa gotcha. Edited answer - much better now, thanks for your input.
@mickmackusa it does need to be a LEFT JOIN, JOIN will return only songs in a that do exist in b, which is not what OP wants.
Guess I'd better reread the question. Yes, you are right. I misinterpreted the logic. I'm deleting my comments (as I do).
2

Why not run one query instead, which fetches a random row from table a that does not exist in table b? Generally, a query inside a loop is a bad idea and can often be avoided. Might not be the most efficient query out there, but this should work better than having to loop through multiple queries. 

SELECT a.id
FROM a 
WHERE a.id NOT IN (SELECT b.item FROM b)
ORDER BY RAND()
LIMIT 1

Then run the query as you would normally, and fetch the single row that it returns.

Improve this answer

Comments

1

Not sure what your bug is, your code looks good. I think the data in your tables may be the problem.

However, instead of looping, you should try doing it in one query. Something like this:

SELECT * FROM `a`
LEFT JOIN `b` ON `a`.`id` = `b`.`item`
WHERE `b`.`item` = NULL
ORDER BY RAND() LIMIT 1
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.