CODEWITHSUNDEEP
phpmysql
DevManX
DevManX

Reputation: 486

Query inside while loop repeats results from 2 tables

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>";
}

Upvotes: 0

Views: 107

Answers (3)

pala_
pala_

Reputation: 9010

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
}
?>

Upvotes: 1

John Christian De Chavez
John Christian De Chavez

Reputation: 312

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=?

Upvotes: 1

Suman Singh
Suman Singh

Reputation: 1377

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'];
    }

}

Upvotes: 1

Related Questions