Adam Adam - 1 month ago 6
HTML Question

Prepared Statement Results to Fill HTML table with MySQL Table Fields

I'm trying to fill an HTML table with user information like this:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = mysqli_connect($host, $username, $password, $db);
$query = "SELECT * from `users`";
$stmt = $mysqli->prepare($query);
$stmt->execute();
$res = $stmt->get_result();
$data = $res->fetch_all();

foreach ($data as $row)
{
echo "<tr><td>" . $row['username'] . "</td><td>" . $row['email'] . "</td></tr>";
}


It seems the array indices are empty, however, because it results in a number of blank HTML table rows equal to the number of users in the MySQL table.

If I bind_param to the $stmt like so:

$query = "SELECT * from `users` WHERE userId = ?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("i", $userId);
$stmt->execute();
$res = $stmt->get_result();
$data = $res->fetch_all();


There are no rows created at all in the HTML table, which leads me to believe that the array is empty.

I've gotten this far using information from this answer:

SELECT * from SQL table using prepared statement

I suppose my issue is in the prepare() parameter, $query. I can't seem to figure out exactly what it is, though.

Answer

Change

$data = $res->fetch_all();

to

$data = $res->fetch_all(MYSQLI_ASSOC);

This way you will get associative results (e.g. $row['username'] will work) instead of ordinal results (e.g. $row[1]).

Comments