无法获取所有 mysqli 结果$id到第二个查询 - Cannot fetch all mysqli results $id to 2nd query

Cannot fetch all mysqli results $id to 2nd query

我无法通过第一个查询获取所有用户 ID 以将这些 id 用于第二个查询。

当我计算第一个查询时，它总是显示 1。这意味着它只从 sql 获得一个结果。但我想，将我的第一个查询的所有 id 用于第二个查询。

请问我的错在哪里？

我的代码：

$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.'';
}

mysqli_fetch_assoc一次

只返回一行，这是标准行为所以首先你应该把所有数据都放到你的数组中

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

现在你需要的所有 id 都在$ids数组中，所以你可以使用它们：

$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若要在一个查询中使用所有 id，请将第二部分更改为：

$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////

首先，你应该更准确地声明你的SQL语句。

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

此外，在 while 循环中，您一次只会获取一个 ID，这意味着您必须定义一个数组，例如：

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

在此之后，您可以使用 IN（） 运算符将 ID 数组用于第二个 SQL 语句（查看这篇文章以获取进一步阅读）。请注意，您的代码在 Sql 注入方面非常脆弱！

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