Cannot fetch all mysqli results $id to 2nd query

Question

I cannot get all user id with 1st query to use these ids to 2nd query.

When I count 1st query its show 1 always. That means it get only one result from sql. But I want, Use my 1st query's all id to 2nd query.

Please where is my wrong here?

My code:

$a = $session->username;
$b = $session->pname;
if (empty($b)) $b = $a;
$g = mysqli_query($dbh,"SELECT id FROM update WHERE `from_id`='$b' OR `to_id`='".$session->bdid."'") or die(mysqli_error($dbh));
while ($row = mysqli_fetch_assoc($g)) {
$id = $row['id'];
date_default_timezone_set('Asia/Dhaka');
$timestamp = date("M j, y; g:i a", time() - 2592000);
$count = mysqli_num_rows($g);

$u = mysqli_query($dbh,"SELECT * FROM update WHERE `id`='".$id."' AND `view` = '0' AND `created` > '".$timestamp."' ORDER BY created DESC") or die(mysqli_error($dbh));
$count1 = mysqli_num_rows($u);

$count2 = ($count - $count1);

echo ''.$count2.'';
}

Try calling enumeration method outside while cycle. And why don't you use OOP syntax for mysqli? — fillobotto, Jan 17 '15 at 15:39

maxpovver · Accepted Answer · 2015-01-21T05:14:27.217

mysqli_fetch_assoc returns only one row at a time, it is standard behavior so first you should get all data into you arrays

$a = $session->username;
$b = $session->pname;
if (empty($b)) $b = $a;
$g = mysqli_query($dbh,"SELECT id FROM update WHERE `from_id`='$b' OR `to_id`='".$session->bdid."'") or die(mysqli_error($dbh));
$ids=[];//init ids as empty array
while ($row = mysqli_fetch_assoc($g)) {
$ids[] = $row['id'];
}

And now all ids you need are in $ids array, so you can use them:

$timestamp = date("M j, y; g:i a", time() - 2592000);
foreach( $ids as $id ) {
   $u = mysqli_query($dbh,"SELECT * FROM update WHERE `id`='".$id."' AND `view` = '0' AND `created` > '".$timestamp."' ORDER BY created DESC") or die(mysqli_error($dbh));
  ///some code here////
  }

UPD To use all ids in ONE query, change second part into this:

$timestamp = date("M j, y; g:i a", time() - 2592000);
$u = mysqli_query($dbh,"SELECT * FROM update WHERE `id` IN( ".join(",",$ids).") AND `view` = '0' AND `created` > '".$timestamp."' ORDER BY created DESC") or die(mysqli_error($dbh));
  ///some code here////

If I echo ''.$count1.''; its display also 1 and if I echo ''.$id.''; its show only one id. But with my previous above code if echo ''.$id.''; its display all id but in case echo ''.$count1.''; display 1. — koc, Jan 18 '15 at 11:48

score 0 · Answer 2 · edited May 23 '17 at 12:31

First of all you should declare your SQL statement more accurately.

$sql = "SELECT `id` FROM `update` WHERE `from_id`='".$b."' OR `to_id`='".$session->bdid."'";

Furthermore in your while loop you will only fetch a single ID at a time, which means you have to define an array like:

while ($row = mysqli_fetch_assoc($g)) {
  $id[] = $row['id'];
}

After this you can use your ID array for your 2nd SQL statement, using the IN() operator (check this post for further reading). Be aware your code is very vulnerable in terms od Sql injections!

$sql2 = "SELECT * FROM `update` WHERE `id` IN (".$id.") AND `view` = '0' AND `created` > '".$timestamp."' ORDER BY `created` DESC"

Cannot fetch all mysqli results $id to 2nd query

2 Answers2