Mysqli_Free_Result Necessary

what is the advantages using mysqli_free_result()?

The mysqli_free_result() function frees the memory associated with the result.

Thus, it's for the system's health and potentially avoid memory-related problem and thus provide better performance.

is it a good practice to use mysql_free_result($result)?

Quoting the documentation of mysql_free_result :

mysql_free_result() only needs to be
called if you are concerned about how
much memory is being used for queries
that return large result sets.
All
associated result memory is
automatically freed at the end of the
script's execution.

So, if the documentation says it's generally not necessary to call that function, I would say it's not really necessary, nor good practice, to call it ;-)

And, just to say : I almost never call that function myself ; memory is freed at the end of the script, and each script should not eat too much memory.

An exception could be long-running batches that have to deal with large amounts of data, though...

Is freeing result sets necessary for mysqli persistent connections?

Yes, it is optional. A persistent connection is a connection that is not closed when the PHP process ends. When a new HTTP request is processed by PHP, it will reuse the same connection. Thus, you don't need to close the connection. Calling mysqli_close() has no effect on persistent connections.

As for freeing the result sets, mysqli will clean up any pending result sets regardless of the mode, when the script ends. You never need to free the results manually. The only reason to free result sets is when you have remaining unbuffered rows that you want to fetch and discard, and then execute another operation within the same script. This is the only time that using mysqli_free_result() is justified.

Mysqli will also clean up the connection by default (it can be disabled).

More importantly, persistent connections are often a nightmare to work with and if possible avoid it at all cost. The performance impact of connection is usually so tiny that most apps can live with opening a new connection for every request.

PHP's mysqli functions, is freeing the statements necessary, and what's the difference between close and free_result?

_close

Closes a previously opened database connection

Documentation

_free_result

Frees the memory associated with a result
Note:
You should always free your result with mysqli_free_result(), when your result object is not needed anymore.

Documentation

And as mentioned in the comments, see this question regarding the out of sync error.

MySQLi query results: When do I close, free, or both?

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.

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 really no need to free the result set.

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.

This 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.

Warning: mysqli_free_result() expects exactly 1 parameter, 2 given

Edit this line

$pages = ceil(mysqli_free_result($page_query, 0) / $per_page);

$pages = ceil(mysqli_fetch_array($page_query)[0] / $per_page);
mysqli_free_result($page_query);

mysqli_fetch_object(): Couldn't fetch mysqli_result in

mysqli_free_result($query);

This discards the result, so it shouldn't be a surprise that you can't fetch from it after you free it!

http://www.php.net/manual/en/mysqli-result.free.php says:

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

(emphasis mine)

Move the call to mysqli_free_result() to after your while loop is done.