如何在没有 FETCHALL 的情况下在 MYSQL PDO 中获取 2 次

2021-12-26 00:00:00 while-loop fetch php mysql pdo

我有这个示例查询:

$STH = $DBH->query("SELECT id FROM table");

我想获取第一行，然后循环显示所有行.所以我使用以下内容来获取第一行:

I want to get the first row and then loop and display all rows. So I use the following to get the first row:

$STH->setFetchMode(PDO::FETCH_ASSOC);
$first_row = $STH->fetch();
$first_row = $first_row['id'];

我使用 while 循环再次显示所有行:

I use while loop to display all rows again:

while ($list = $STH->fetch()) {      
$id = $list['id'];
echo $id;
}

现在 while 跳过第一行，我希望它被显示.是否有等效于 mysql_data_seek 将指针再次重置到第一行?我知道 fetchall 可以使用，但它对内存很不利，而且很浪费.我也可以运行查询并限制为 1，但不建议这样做，因为我有一个连接多个表的查询，并且速度非常慢.还有其他解决办法吗?

Now the while skips the first row and I want it to be displayed. Is there an equivalent to mysql_data_seek to reset the pointer again to the first row? I know fetchall can be used but it's bad on memory and wasteful. I could also run the query and limit to 1 but this is not recommended as I have a query that joins multiple tables and would be very slow. Is there any other solution?

谢谢

推荐答案

我认为你可以使用光标方向常量来选择结果......示例代码即将推出......我还没有尝试过，所以你可以需要玩一会儿.这也是基于这样一个假设:PDO::FETCH_ORI_FIRST 的作用类似于 data_seek，并将光标留在第一个位置，而不是将其返回到之前的位置.

I take that back looks like you can use the cursor orientation contants to select the result... sample code coming... I havent tried this so you may need to play a bit. This is also based on the assumption that a PDO::FETCH_ORI_FIRST acts like a data_seek and leaves the cursor on the first position as opposed to returning it to whatever it was before.

$stmt = $pdo->prepare('SELECT id FROM table', array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();

$first = $pdo->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_FIRST);
$first_row = $first['id'];

// other stuff

// first iteration we rewind to the first record;
$cursor = PDO::FETCH_ORI_FIRST;

while (false !== ($row = $stmt->fetch(PDO::FETCH_ASSOC, $cursor))) {
   $id = $row['id'];
   // successive iterations we hit the "next" record
   $cursor = PDO::FETCH_ORI_NEXT; 
   echo $id;
}

<小时>

我不认为你可以倒带一个语句......假设这些块没有被一堆中间逻辑 id 分开，只需在循环中进行即可.

I dont think you can rewind a statement... Assuming these blocks arent seprated by a bunch of intermediary logic id just do it in the loop.

$STH->setFetchMode(PDO::FETCH_COLUMN); // no need to pull an array
$count = 0;
while ($id = $STH->fetch()) {      
  if($count === 0) {
   $first_row = $id;
  }
  echo $id;
  $count++;
}

相关文章