PHP MySQL: Select to display table using fetch_assoc

This entry is part 11 of 17 in the series PHP & MySQL

Let us revisit this table

create table artist
 id int auto_increment primary key, 
 artiste varchar(30), 
 album varchar(50),
 year int(10)

insert into artist
(artiste, album, year)
('Rod Stewart', 'Maggie May', 1973),
('New Order', 'Substance', 1987),
('Nirvana', 'Smell Like Teen Spirit', 1992),
('Rod Stewart', 'Out of Order', 1988);
Run the SQL
1Rod StewartMaggie May1973
2New OrderSubstance1987
3NirvanaSmell Like Teen Spirit1992
4Rod StewartOut of Order1988
$mysqli = new mysqli("localhost", "root", "", "allartist");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s", mysqli_connect_error());

$query = "select id, artiste, album, year from artist";

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        printf ("%d %s %s %s", $row["id"], $row["artiste"], $row["album"], $row["year"] );
        echo "<br>";

    /* free result set */

/* close connection */


fetch_assoc() returns an associative array that corresponds to the fetched row or null if there are no more rows.  Thus when there are no more rows, the while loop will not be ran.

The good thing about using fetch_assoc() is that the code is almost self explanatory.

We can use the column name, such as $row[“artiste”], in the array and print out each row in the table.

Series Navigation<< PHP MySQL: Select Count Using fetch_assoc()
PHP MySQL: Display records using fetch_row() >>