PHP MySQL: Outputing to table Using fetch_assoc

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

Continued from previous post.

In an earlier post, we have used printf to output a table records.

printf ("%d %s %s %s", $row["id"], $row["artiste"], $row["album"], $row["year"] );

If we stick to the same table format, we will have to embed the php code into the HTML table. This is what made it hard for beginners to learn PHP.

Once we have the connection to the DB set, we can use fetch_assoc to fetch the row and use echo to build up the  HTML table.

And finally we just have to use printf to output the data to the HTML table.

<table border="1">
    <tr>
        <th>ID</th>
        <th>artiste</th>
        <th>album</th>
        <th>year</th>
    </tr>
    <?php
    $mysqli = new mysqli("localhost", "root", "", "allartist");

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

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


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

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

        /* free result set */
        $result->free();
    }

    /* close connection */
    $mysqli->close();
    ?>
</table>

If you look at the code above, and compare it with the earlier post, you could see that the main line that I have added is:

echo "<tr><td>" . $row["id"] . "</td><td>" . $row["artiste"] . "</td><td>" . $row["album"] . "</td><td>" . $row["year"] . "</td></tr>";

Running the above code will display the table below.

html_table_output

PHP MySQL: Outputing data to HTML table I

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

For HTML table, a basic table would look like this:

<table border="1">
    <tr>
        <th>ID</th>
        <th>artiste</th>
        <th>album</th>
    </tr>
    <tr>
        <td>1</td>
        <td>Rod Stewart</td>
        <td>Maggie May</td>
    </tr>
</table>

We can always start from the above table and built from there.  We can for example, add one more column or one more row to the above table.

For the table that we have used, the HTML will be as below:

<table border="1">
    <tr>
        <th>ID</th>
        <th>artiste</th>
        <th>album</th>
        <th>year</th>
    </tr>
    <tr>
        <td>1</td>
        <td>Rod Stewart</td>
        <td>Maggie May</td>
        <td>1973</td>
    </tr>
    <tr>
        <td>2</td>
        <td>New Order</td>
        <td>Substance</td>
        <td>1987</td>
    </tr>
    <tr>
        <td>3</td>
        <td>Nirvana</td>
        <td>Smell Like Teen Spirit</td>
        <td>1992</td>
    </tr>
    <tr>
        <td>4</td>
        <td>Rod Stewart</td>
        <td>Out Of Order</td>
        <td>1988</td>
    </tr>
</table>

The output from running the above HTML script is:

html_table

PHP MySQL: using data_seek to fetch row

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

The data_seek function in PHP allow us to fetch a particular row.

Syntax:

bool mysqli_result::data_seek (int $offset)

Data_seek adjusts the result pointer to an arbitrary row specified by offset in the result.

<?php
/* Open a connection */
$mysqli = new mysqli("localhost", "root", "", "allartist");

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

$query = "select * from artist";
if ($result = $mysqli->query( $query)) {

    /* seek to row no. 4 */
    $result->data_seek(3);

    /* fetch row */
    $row = $result->fetch_row();

    printf ("Artiste: %s Album: %s Year: %s", $row[1], $row[2], $row[3]);

    /* free result set*/
    $result->close();
}

/* close connection */
$mysqli->close();
?>

The offset here is 3 and as it starts from index 0, it points to row 4 in the table.

This function can only be used with buffered results attained from the use of the mysqli_store_result() or mysqli_query() functions.

Here is the output after running the script above.

data_seek

PHP MySQL: Select a single record using fetch_assoc

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

A single record in the table can be retrieved by using fetch_assoc().

We have this table as below and let’s say we ask ourselves when was Nirvana’s Smell Like Teen Spirit released.

IDartistealbumyear
1Rod StewartMaggie May1973
2New OrderSubstance1987
3NirvanaSmell Like Teen Spirit1992
4Rod StewartOut of Order1988
Run the SQL

We first have to make some adjustments to the query statement:

select year from artist where artiste='Nirvana' and album='Smell Like Teen Spirit'
Run the SQL
<?php
$mysqli = new mysqli("localhost", "root", "", "allartist");

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

$query = "select year as yr from artist where artiste='Nirvana' and album='Smell Like Teen Spirit'";

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

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        printf ("Smell Like Teen Spirit was released in: %d", $row["yr"]);
        echo "<br>";
    }

    /* free result set */
    $result->free();
}

/* close connection */
$mysqli->close();
?>

We have used associative array method fetch_assoc().

If the above script is ran, the output in the browser is as below:

select4

PHP MySQL: Display records using fetch_array

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

We have looked at retrieving records using associative array and numeric array.

  • fetch_assoc()
  • fetch_row()

There is a third type fetch_array provided by PHP where you can use both types of array.

Syntax:  mixed mysqli_result::fetch_array ([ int $resulttype = MYSQLI_BOTH ] )

We take a brief look at this type of method.

<?php
$mysqli = new mysqli("localhost", "root", "", "allartist");

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

$query = "select * from artist";
$result = $mysqli->query($query);

/* numeric array */
$row = $result->fetch_array(MYSQLI_NUM);
printf ("%s %s", $row[0], $row[1]);
echo "<br>";

/* associative array */
$row = $result->fetch_array(MYSQLI_ASSOC);
printf ("%s %s", $row["id"], $row["artiste"]);
echo "<br>";

/* associative and numeric array */
$row = $result->fetch_array(MYSQLI_BOTH);
printf ("%s %s", $row[0], $row["artiste"]);

/* free result set */
$result->free();

/* close connection */
$mysqli->close();
?>

The output from running the above script is as below:

fetch_array

resulttype

This optional parameter is a constant indicating what type of array should be produced from the current row data.

The possible values for this parameter are the constants:

  • MYSQLI_ASSOC,
  • MYSQLI_NUM, or
  • MYSQLI_BOTH.

By using the MYSQLI_ASSOC constant this function will behave identically to the mysqli_fetch_assoc(), while MYSQLI_NUM will behave identically to the mysqli_fetch_row() function.

The final option MYSQLI_BOTH will create a single array with the attributes of both.