如何在 PHP 中使用 mysqli_query()?

问题描述

我正在用 PHP 编码.我有以下 mySQL 表:

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

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;

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

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

这是我的代码:

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

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

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

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

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

+----------+--------------+------+-----+---------+----------------+
| 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    |                |
+----------+--------------+------+-----+---------+----------------+ 

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

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

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

推荐答案

我不得不承认，mysqli_query() 手册条目没有包含关于如何获取多行的清晰示例.可能是因为套路太套路了，PHP 人都知道几十年了:

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:

$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>
";
    }
}

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

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>";
    }
}

某些主机可能不支持 fetch_all() 函数.在这种情况下，按照通常的方式填充 $data 数组:

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

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

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

1. 您必须将 mysqli 配置为自动抛出错误，而不是手动检查每个 mysqli 语句的错误.为此，在 before mysqli_connect() 之前添加这一行:

 mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

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

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

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 function to do all the prepare/bind/execute business inside:

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

这篇关于如何在 PHP 中使用 mysqli_query()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！