Just started using mysqli. If I'm working with small data sets on small websites (traffic-wise), do I really need to use these all the time?
$result->close();
$mysqli->close();
Also, for someone doing custom PHP and MySQL work without a framework, is mysqli the general preferred way of interacting with MySQL?
PHP will close all open files and DB connections at the end of the script. It's good practice to do it manually when you are done with the connections, but it's no disaster if you don't. If you have a DB connection that will be used throughout the whole script you can as well leave it open.
+1 on PDO
I felt an update to this thread was needed...
According to current documentation, you should always use $mysql->kill() in addition to $mysql->close().
$thread = $mysqli->thread_id;
$mysqli->kill($thread);
$mysqli->close();
(As a side note, I asked Oracle developers about using PDO with MySQL and they discouraged it. They use MySQLi exclusively. PDO hasn't been maintained and it doesn't support many of MySQL's current features.) Edit: obsolete comment.
Edit: switched the statement order, as suggested.
You should get in the habit of doing cleanup right (calling close as soon as you're done), or the resource leaks can gradually accumulate until they impact performance.
As far as what DB layer, learning PDO should be worthwhile because it is well-designed and compatible with all the major databases.
It is a good practice to release resource early when it is no more needed, this may avoid resource peek out when there are more number of concurrent user accessing the same page
