Viewed   367 times

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;

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 />";



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.

Saturday, October 15, 2022

Get all the values from MySQL:

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

Then, to get each value:

     foreach ($posts as $row) 
            foreach ($row as $element)
                echo $element."<br>";

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

Sunday, October 16, 2022

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.

Wednesday, October 12, 2022

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)

Friday, August 12, 2022

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->bind_result($name, $filter, $email);
            while ($stmtUsersAlerts->fetch()) {
                /* Send email */
Tuesday, September 27, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :