如何在PHP中使用mysqli_query()? [英] How to use mysqli_query() in PHP?

问题描述

我正在用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;

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

这是我的代码:

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

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

但是从那里我不知道如何打印$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    |                |
+----------+--------------+------+-----+---------+----------------+ 

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

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

我必须承认，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";
}

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

// 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数组:

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

我必须添加两个重要的注释.

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();
   

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

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

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;

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

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_result object.

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

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

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

解决方案

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>\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 $data array the usual way:

$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 before mysqli_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();
   

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()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！