Fetch data based on another MySQL query

Question

I have the following two queries. The first query is fetching a key called srNumber from first table called tags and then the second query is using that srNumber to fetch details from a second table called nexttable.

$tagQuery = "SELECT * FROM tags WHERE status = 0 AND currentStage = '1' AND assignedTo = '1' ORDER BY 
deliveryDate ASC";
$tagQueryExecute = mysqli_query($conn, $tagQuery);
while($rows = mysqli_fetch_array($tagQueryExecute)){
  $srNumber = $rows['srNumber'];

  $nextQuery = "SELECT * FROM nexttable WHERE srNumber='$srNumber'";
  $nextQueryExecute = mysqli_query($conn, $nextQuery);
  $detailsFromNextTable = mysqli_fetch_array($nextQueryExecute);

  //Show these details

}

For a small result this is not a big issue. But if the first query got so many results, then second query has to run as many times as number of loop. Is there any other way to do this efficiently?

NB: Please ignore the SQL injection issues with these queries. I just simplified it to show the problem

Using an `INNER JOIN` query would likely be a good solution, so you can run one single query to fetch everything from both tables in one result. — ADyson, Jul 06 '20 at 09:38
I didn't downvote you, but downvoters are not obliged to comment. Hover your mouse over the downvote button on any question and you'll see the reasons why downvotes can be given. You can also read https://stackoverflow.com/help/privileges/vote-down for more explanation. — ADyson, Jul 06 '20 at 13:24
No problem. Ppl sometimes just assume the person who wrote a comment is the same one who downvoted, but there isn't necessarily any link. But sounds like you know that already. I don't suppose the downvoter will see your question unfortunately, they likely moved on to something else a long time ago. — ADyson, Jul 06 '20 at 13:41

score 1 · Accepted Answer · answered Jul 06 '20 at 09:40

As you appear to have only 1 row in the second table, you would be better off with a join, MySQL: Quick breakdown of the types of joins gives some more info on the types of joins.

SELECT * 
    FROM tags t
    JOIN nexttable n on t.srNumber = n.srNumber
    WHERE t.status = 0 AND t.currentStage = '1' AND t.assignedTo = '1' 
    ORDER BY t.deliveryDate ASC

This also removes the SQL injection as well.

I would also recommend removing the * and just list the columns you intend to use, this also helps if you have columns with the same names in the different tables as you can add an alias to the specific columns.

FYI - the original problem you have is similar to What is the "N+1 selects problem" in ORM (Object-Relational Mapping)?

Fetch data based on another MySQL query

1 Answers1