問(wèn)題描述
我正在用 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 函數(shù)來(lái)描述表.
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");
文檔說(shuō)對(duì)于成功的 SELECT、SHOW、DESCRIBE 或 EXPLAIN 查詢(xún)，mysqli_query() 將返回 mysqli_result 對(duì)象.
The documentation says For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object.
但是從那里我不知道如何打印 $result 以便它顯示查詢(xún)結(jié)果.如果可能，我想打印 $result 使其看起來(lái)像:
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 | |
+----------+--------------+------+-----+---------+----------------+
我的另一個(gè)問(wèn)題是如何打印查詢(xún)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");
推薦答案
我不得不承認(rèn)，mysqli_query() 手冊(cè)條目沒(méi)有包含關(guān)于如何獲取多行的清晰示例.可能是因?yàn)樘茁诽茁妨耍琍HP 人都知道幾十年了:
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>
";
}
}
如果要打印列標(biāo)題，必須先將數(shù)據(jù)選入嵌套數(shù)組，然后使用第一行的鍵:
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>";
}
}
某些主機(jī)可能不支持 fetch_all() 函數(shù).在這種情況下，按照通常的方式填充 $data 數(shù)組:
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;
}
我必須補(bǔ)充兩個(gè)重要的說(shuō)明.
您必須將 mysqli 配置為自動(dòng)拋出錯(cuò)誤，而不是手動(dòng)檢查每個(gè) mysqli 語(yǔ)句的錯(cuò)誤.為此，在 before
mysqli_connect()之前添加這一行:
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
最重要的提示:與mysql_query()不同，mysqli_query()的用途非常有限.僅當(dāng)查詢(xún)中不使用任何變量時(shí)，您才可以使用此函數(shù).如果要使用任何 PHP 變量，則永遠(yuǎn)不要使用 mysqli_query()，但始終堅(jiān)持準(zhǔn)備好的聲明，如下所示:
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();
這有點(diǎn)羅嗦，我不得不承認(rèn).為了減少代碼量，您可以使用 PDO 或采用簡(jiǎn)單的輔助函數(shù)來(lái)完成所有工作里面的準(zhǔn)備/綁定/執(zhí)行業(yè)務(wù):
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();
這篇關(guān)于如何在 PHP 中使用 mysqli_query()?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！