php 使用 PDO 语句选择表数据

Selecting table data with PDO statements

提问by RaGe10940

I have a php script that selects data via mysql_, however recently I have been reading that PDO is the way to go and that mysql_ is becoming depreciated. Now I am converting that script to PDO.

我有一个通过 mysql_ 选择数据的 php 脚本，但是最近我一直在读 PDO 是要走的路，而且 mysql_ 正在贬值。现在我正在将该脚本转换为 PDO。

My question is though, I am not using $_POST to select. I just want to select the entire table with all of its data so I enter this query :

我的问题是，我没有使用 $_POST 进行选择。我只想选择整个表及其所有数据，所以我输入以下查询：

$query = $dbh->prepare("SELECT * FROM students");
$query->execute();
$result = $query->fetchall(); // or you can just $result = $query as hakre proposed!

so then like I did with my old depreciated mysql_ version of the script I used the echo to echo a table with the data in it.

因此，就像我使用旧的贬值 mysql_ 版本的脚本所做的那样，我使用 echo 来回显包含数据的表。

    echo 
    "<table border='2'>
    <tr>
    <th>ID</th>
    <th>A Number</th>
    <th>First Name</th>
    <th>Last Name</th>
    <th>Why</th>
    <th>Comments</th>
    <th>Signintime</th>
    </tr>"
    ;

    foreach($result as $row)
    {
  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td><a href=Student.php?studentA_num=" . $row['anum'] . ">" .$row['anum'] . " </a></td>";
  echo "<td>" . $row['first'] . "</td>";
  echo "<td>" . $row['last'] . "</td>";
  echo "<td>" . $row['why'] . "</td>";  
  echo "<td>" . $row['comments'] . "</td>";
  echo "<td>" . $row['signintime'] . "</td>";
  echo "<td> <input type=\"button\" value=\"Start Session\"onClick=\accept.php?id=" . $row['id'] . "&start=true></td>";
}

  echo "</tr>";
  echo "</table>";

now using this, I can not get a single output to my table.

现在使用它，我无法在我的表中获得单个输出。

My question is am I missing something from my select statements? Or am I not fetching any rows? Also I the connection settings set in another script called connect.php that is required by init.php (at the top of all of my pages)

我的问题是我的选择语句中遗漏了什么吗？或者我没有获取任何行？我还在另一个名为 connect.php 的脚本中设置了 init.php 所需的连接设置（在我所有页面的顶部）

Edit : 1

编辑：1

Edited the code so it now works, also adding a picture to show others how it should look! Hopefully some one can put this to some sort of use!

编辑了代码使其现在可以工作，还添加了一张图片以向其他人展示它的外观！希望有人可以将其用于某种用途！

回答by hakre

You are doing too much actually:

你实际上做得太多了：

$query = $dbh->prepare("SELECT * FROM students");
$query->execute();
$result = $dbh->query($query);

The problematic line is:

有问题的线路是：

$result = $dbh->query($query);

Check with http://php.net/pdo.query, the parameter is a string, actually the SQL string you already use above, not the result value of a PDO::prepare()call.

检查http://php.net/pdo.query，参数是一个字符串，实际上是你上面已经使用的 SQL 字符串，而不是PDO::prepare()调用的结果值。

For your simple query you can just do:

对于您的简单查询，您可以执行以下操作：

$result = $dbh->query("SELECT * FROM students");

Or if you like to prepare:

或者，如果您喜欢准备：

$query = $dbh->prepare("SELECT * FROM students");
$query->execute();
$result = $query;

The later is some boilerplate if you want to insert variables into the query, that's why you prepare it.

如果您想在查询中插入变量，后者是一些样板，这就是您准备它的原因。

---

---

The next problem is with the foreachline:

下一个问题是该foreach行：

foreach($result as $row);

You are terminating the loop immediately because of the semicolon ;at the end. Remove that semicolon so that the following angle-bracketed code-block becomes the body of the foreach-loop.

由于;末尾的分号，您将立即终止循环。删除该分号，以便以下带尖括号的代码块成为 foreach 循环的主体。

回答by Tom van der Woerdt

Your code is wrong:

你的代码是错误的：

$query = $dbh->prepare("SELECT * FROM students");
$query->execute();
$result = $dbh->query($query);

After executing a prepared statement, you can just call fetchAll()on it:

执行准备好的语句后，您可以调用fetchAll()它：

$query = $dbh->prepare("SELECT * FROM students");
$query->execute();
$result = $query->fetchAll();

The rest of your code will work fine once you remove the semicolon after the foreach.

一旦在 foreach 之后删除分号，其余的代码就可以正常工作。