Query inside while loop repeats results from 2 tables

Question

I am not sure if the title expresses the problem accurately or not. Anyways, here is the explanation:

I have 2 tables, the first one holds users IDs, the other one holds their posts.

The fist query selects user IDs from the fist table, and it loop through the second table to find the users (IDs) posts.

The problem is that when the query finds eg. 5 results (user IDs 1, 6, 999.. etc) in the fist table, then it loops 5 times to search in the second table, it shows 5 results even if the real results is 2 post only created by user 1 and 6. How can I avoid this repeatation?

Here is the code:

$stmt = $conn->prepare('select userid from table where para=?');
$stmt->bind_param('i', $para);
$stmt->execute();
$result = $stmt->get_result();
while( $row = $result->fetch_assoc()) {

$userid  = $row["userid "];

$qname = "select postid,title from  posts where uid='$userid'";
$result2 = $conn->query($qname);
$row2 = $result2->fetch_array(MYSQLI_ASSOC);
if ($row2 > 0) { 
$postid= $row2['postid'];
$title= $row2['title'];
}
echo $postid." ".$title."<br>";
}

You should be using a `join` rather than putting the query in a loop in php. — Gordon Linoff, Apr 20 '15 at 01:43
It seems that it's the best solution, I am reading some tutorials, because I never used 'join' before, and I will look at the solutions the respected developer suggested here. — DevManX, Apr 20 '15 at 15:51
@GordonLinoff yes, it was the right solution, I used this query and it worked well: SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID WHERE Orders.ID = 12345 The resource: http://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause — DevManX, Apr 20 '15 at 21:41

score 1 · Accepted Answer · answered Apr 20 '15 at 01:55

Try

$qname = "select postid,title from posts as P left join table as T on T.userid=P.uid where where para=?";

Or You can store the results in a common array during the loop. like

$tempResult = array();
while( $row = $result->fetch_assoc()) {

$userid  = $row["userid "];

$qname = "select postid,title from  posts where uid='$userid'";
$result2 = $conn->query($qname);
$row2 = $result2->fetch_array(MYSQLI_ASSOC);
    if ($row2 > 0) { 
    $tempResult[$userid][] = $row2['postid'];
    $tempResult[$userid][] = $row2['title'];
    }

}

I really didn't understand that your solution is exactly what I need, because I am new in 'join' query, but now after I reread it, I found it's the right solution for my problem. thank you :) — DevManX, Apr 20 '15 at 21:48

pala_ · Answer 2 · 2015-04-20T02:08:38.673

1

You can avoid it by only running one query that joins the two tables together. Something like this:

<?php
  $stmt = $conn->prepare('select posts.* from table t inner join posts p on t.userid = p.uid where t.para = ? order by uid');
$stmt->bind_param('i', $para);
$stmt->execute();
$result = $stmt->get_result();
while( $row = $result->fetch_assoc()) {
   // $row now has userid, and all post details
}
?>

edited Apr 20 '15 at 02:08

answered Apr 20 '15 at 01:57

pala_

8,901
1
15
32

hello pala, thank you for your help. I am just confused, I never used 'join' before. First I select userid from 'table' where 'para' equal a variable value, then I use 'userid' to select from the table posts. Note: the common values is the userid/uid, the para is not common, it's only found in 'table'. I tried the solution and I got this error: Fatal error: Call to a member function bind_param() on boolean. – DevManX Apr 20 '15 at 17:06
Thank you, I appreciate your help. I found the solution. – DevManX Apr 20 '15 at 21:44

score 1 · Answer 3 · answered Apr 20 '15 at 02:01

1

you can try this query using a JOIN MYSQL.

SELECT u.userid,p.postid,p.title FROM TABLE `user` u
JOIN posts p ON
p.uid = u.userid
WHERE para=?

answered Apr 20 '15 at 02:01

John Christian De Chavez

312
1
3
19

Thank you, I appreciate your help. I found the solution. – DevManX Apr 20 '15 at 21:44

Query inside while loop repeats results from 2 tables

3 Answers3