mysqli query results to show all rows

Question 1

I have the following code:

include $_SERVER['DOCUMENT_ROOT'].'/include/conn.php'; 

$query = "SELECT title FROM news_event";
$result = $mysqli->query($query);
$row = $result->fetch_array(MYSQLI_BOTH);
$row_cnt = $result->num_rows;
$result->free();
$mysqli->close();

This is fine if there is only one result as I can just echo $row['title'] but if there are lots of results, how do I get this to loop through and print every row?

I'm sure this is really simple but I'm just not sure what I need to search for in Google.

I'm looking for a mysqli equivalent of this:

while( $row = mysql_fetch_array($result) )
{
    echo $row['FirstName'] . " " . $row['LastName'];
    echo "<br />";
}

Question 2

Just replace it with mysqli_fetch_array or mysqli_result::fetch_array :)

while( $row = $result->fetch_array() )
{
    echo $row['FirstName'] . " " . $row['LastName'];
    echo "<br />";
}

Almost all mysql_* functions have a corresponding mysqli_* function.

Question 3

Get all the values from MySQL:

    $post = array();
    while($row = mysql_fetch_assoc($result))
    {
        $posts[] = $row;
    }

Then, to get each value:

<?php 
     foreach ($posts as $row) 
        { 
            foreach ($row as $element)
            {
                echo $element."<br>";
            }
        }
?>

To echo the values. Or get each element from the $post variable

Question 4

When I assign the results of the second query to $results, what happens to the memory associated with the previous results?

When you execute this:

$results = $db->query($query);

If there was something in $results before, this old content cannot be accessed anymore, as there is no reference left to it.

In such a case, PHP will mark the old content of the variable as "not needed anymore" -- and it will be removed from memory when PHP needs some memory.

This, at least, is true for general PHP variables; in the case of results from an SQL query, though, some data may be kept in memory on the driver-level -- over which PHP doesn't have much control.

Should I be freeing that result before assigning the new one?

I never do that -- but, quoting the manual page of mysqli_result::free:

Note: You should always free your result with mysqli_free_result(), when your result object is not needed anymore

It probably doesn't matter for a small script... And the only way to be sure would be to test, using memory_get_usage before and after calling that method, to see whether there is a difference or not.

Related to 1, when I do clean up at the end, is cleaning up just the last results enough?

When the scripts end:

The connection to the database will be closed -- which means any memory that might be used by the driver should be freed
All variables used by the PHP script will be destroyed -- which means the memory they were using should be freed.

So, at the end of the script, there is probably really no need to free the resultset.

When I do try to clean up a result, should I be freeing it as above, should I be closing it, or both?

If you close the connection to the database (using mysqli::close like you proposed), this will disconnect you from the database.

Which means you'll have to re-connect if you want to do another query! Which is not good at all (takes some time, resources, ... )

Generally speaking, I would not close the connection to the database until I am really sure that I won't need it anymore -- which means I would not disconnect before the end of the script.

And as "end of the script" means "the connection will be closed" even if you don't specify it; I almost never close the connection myself.

Question 5

If you want to get all rows from the result set then you need to fetch all. Right now you are fetching only one.

To fetch all rows use fetch_all()

$coquery = "Select distinct coName from avgcarcompany";
$crun = $con->query($coquery);

$arrey = $crun->fetch_all(MYSQLI_ASSOC)

print_r($arrey);

Question 6

stmt->store_result() can not run before stmt->execute().

$stmtUsers = $conn->prepare("SELECT username, setting1 FROM Users");
if ($stmtUsers->execute() === FALSE) {
    die("Could not execute prepared statement");
} else {
    $stmtUsers->store_result();                // After execute()
    $stmtUsers->bind_result($user, $setting1);
    while ($stmtUsers->fetch()) {
        /* Check if each user has setting 1 disabled */
        if ($setting1 == '0'){
            /* Check if any alerts exist for each user */
            $stmtUsersAlerts = $conn->prepare("SELECT name, filter, email FROM Alerts WHERE user='".$user."' AND type='1'");
            $stmtUsersAlerts->execute();        // This line was missing
            $stmtUsersAlerts->store_result();
            $stmtUsersAlerts->bind_result($name, $filter, $email);
            while ($stmtUsersAlerts->fetch()) {
                /* Send email */
            }
            $stmtUsersAlerts->close();
        }
    }
    $stmtUsers->close();
}

mysqli query results to show all rows

Answers

php,mysqli,while-loop