如何在 PHP 中使用 mysqli_query()？

How to use mysqli_query() in PHP?

提问by cooldood3490

I'm coding in PHP. I have the following mySQL table:

我正在用 PHP 编码。我有以下 mySQL 表：

CREATE TABLE `students` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `Start` int(10) DEFAULT NULL,
  `End` int(10) DEFAULT NULL,
   PRIMARY KEY (`ID`)
 ) ENGINE=InnoDB;

I'm trying to use the mysqli_queryfunction in PHP to DESCRIBE the table.

我正在尝试使用PHP中的mysqli_query函数来描述表。

Here's my code:

这是我的代码：

$link = mysqli_connect($DB_HOST, $DB_USER, $DB_PASS, $DATABASE);
$result = mysqli_query($link,"DESCRIBE students");

The documentation says For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_resultobject.

文档说对于成功的 SELECT、SHOW、DESCRIBE 或 EXPLAIN 查询 mysqli_query() 将返回一个mysqli_result对象。

But from there I don't know how to print $resultso that it shows the query results. If possible I want to print $result so that it looks like:

但是从那里我不知道如何打印$result以显示查询结果。如果可能，我想打印 $result 使其看起来像：

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| ID       | int(10)      | NO   | PRI | NULL    | auto_increment |
| Name     | varchar(255) | YES  |     | NULL    |                |
| Start    | int(10)      | YES  |     | NULL    |                |
| End      | int(10)      | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+ 

My other question is how to print the query SHOW CREATE TABLE students.

我的另一个问题是如何打印查询SHOW CREATE TABLE students。

$result = mysqli_query($link,"SHOW CREATE TABLE students");

回答by Your Common Sense

I have to admit, mysqli_query()manual entry doesn't contain a clean example on how to fetch multiple rows. May be it's because the routine is so routine, known to PHP folks for decades:

我不得不承认，mysqli_query()手动输入不包含有关如何获取多行的清晰示例。可能是因为套路太套路了，几十年来PHP人都知道：

$result = $link->query("DESCRIBE students");
while ($row = $result->fetch_assoc())
{
    // to print all columns automatically:
    foreach($row as $value) echo "<td>$value</td>";
    // OR to print each column separately:
    echo "<td>"$row['Field'],"</td><td>",$row['Type'],"</td>\n";
}

In case you want to print the column titles, you have to select your data into a nested array first and then use keys of the first row:

如果要打印列标题，则必须先将数据选择到嵌套数组中，然后使用第一行的键：

// getting all the rows from the query
// note that handy feature of OOP syntax
$data = $link->query("DESC students")->fetch_all(MYSQLI_ASSOC);
// getting keys from the first row
$header = array_keys(reset($data));
// printing them
foreach ($header as $value) echo "<td>$value</td>";
// finally printing the data
foreach ($data as $row)
{
    foreach($row as $value) echo "<td>$value</td>";
}

Some hosts may have no support for the fetch_all()function. In such a case, fill the $dataarray the usual way:

某些主机可能不支持该fetch_all()功能。在这种情况下，$data以通常的方式填充数组：

$data = [];
$result = $link->query("DESC students");
while ($row = $result->fetch_assoc())
{
    $data[] = $row;
}

Two important notes I have to add.

我必须补充两个重要的说明。

1. You have to configure mysqli to throw errors automatically instead of checking them for each mysqli statement manually. To do so, add this line beforemysqli_connect():

   mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
   

2. The most important note:unlike mysql_query(), mysqli_query()has a very limited use. You may use this function only if no variables are going to be used in the query.If any PHP variable is going to be used, you should never use mysqli_query(), but always stick to prepared statements, like this:

   $stmt = $mysqli->prepare("SELECT * FROM students WHERE class=?");
   $stmt->bind_param('i', $class);
   $stmt->execute();
   $data = $stmt->get_result()->fetch_all();
   

1. 您必须将 mysqli 配置为自动抛出错误，而不是手动为每个 mysqli 语句检查它们。为此，请在以下行之前添加mysqli_connect()：

   mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
   

2. 最重要的注意事项：与 不同mysql_query()，mysqli_query()使用非常有限。仅当查询中不使用任何变量时，您才可以使用此函数。如果要使用任何 PHP 变量，则永远不要使用mysqli_query()，而是始终坚持使用准备好的语句，如下所示：

   $stmt = $mysqli->prepare("SELECT * FROM students WHERE class=?");
   $stmt->bind_param('i', $class);
   $stmt->execute();
   $data = $stmt->get_result()->fetch_all();
   

It's a bit wordy, I have to admit. In order to reduce the amount of code you can either use PDO or adopt a simple helper functionto do all the prepare/bind/execute business inside:

这有点罗嗦，我不得不承认。为了减少代码量，你可以使用 PDO 或者采用一个简单的辅助函数来完成里面的所有准备/绑定/执行业务：

    $sql = "SELECT * FROM students WHERE class=?";
    $data = prepared_select($mysqli, $sql, [$class])->fetch_all();