How to use mysqli_query() in PHP?

Question

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

Answer 1

Well I have to admit that 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 = mysqli_query($link,"DESCRIBE students");
while ($row = $result->fetch_assoc())
{
    foreach($row as $value) echo "<td>$value</td>";
}

In case you want to print column titles, you have to select your data into 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 $data array usual way:

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

Two important notes I have to add.

1. You have to make mysqli 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. Most important note: unlike mysql_query(), mysqli_query() has 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. So, for the everyday use, instead of using mysqli functions as is, one have to adopt some sort of wrapper, which will hide all the repeated code inside. There are many wrappers, but surely I think my own is the best ^_^. It is called Safemysql and it will let you to write safe and concise code at once:

$data = $db->getAll("DESCRIBE ?n", $table);

But if you don't like to use third-party libraries, then stick to PDO.