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