THE MYSQL/MYSQLI MODULES
This describes the modules MYSQL.PHP and MYSQLI.PHP, the only source files that have SQL strings. Both have the same set of functions, one using the mysql
functions, the other the mysqli
functions.
This module (recently made into two) has gone through the most changes of any other thing we have written for this code. With each version the code has gotten smaller and easier to understand (we believe).
There are four groups of functions here: records (aka posts), comments, users and visitors, each representing a database table. There is basically one function for each table, but with "front end" functions that call the main one; e.g. in the first group, the several functions call dbrecord()
, passing their argument(s) to it along with a "command". It is in these main functions that the SQL queries reside.
The main functions are:
dbrecord(); dbcomment(); dbuser(); dbvisitor();
There are also a few internal support functions, as well the function dbstart()
that is called once during start-up.
Here is an example. The function dbreadrecord()
reads a record (post) and it defined as:
function dbreadrecord($id) { return dbrecord('read',$id); }
The function dbrecord()
actually does the work, and it is a bit like:
function dbrecord($cmd, $id = NULL, $data = NULL) { $sql = array( 'list' => "SELECT id FROM `$db_table` ORDER BY id", 'read' => "SELECT body FROM `$db_table` WHERE id = $id", } $res = mysql_query($sql[$cmd]); }
The reason for the "front-end" functions (which kind are an encapsulation of an encapsulation) is that previously the SQL query string and call to mysql_query()
were in each front-end function, a bit like:
function dbreadrecord($id) { $res = mysql_query("SELECT body FROM `$db_table` WHERE id = $id"); }
The change to a common function that contains all the SQL query strings is just an easy first step to simplify things in the MYSQL code without having to change any of the other code — and we think that is a good thing.
And herein lies the basis for our thinking: It is not only inefficient but also problematic to have dozens of SQL query strings (and the code to fetch the rows each time they are used) strewn throughout dozens of files.
We like having all the code's SQL query strings in one file, with one call to the mysql_query()
function, with common code in one function and not duplicated throughout the file.
For an example of our process, compare the differences between MYSQLI.PHP and MYSQL.PHP. The latter was written after the former, based on the former but with an eye toward the efficiency of clarity. By which we mean "more easily understood" when you peruse the code.
The latter code has several functions reduced into one. As mentioned earlier, this is a step by step process. The next step (after converting MYSQLI.PHP to be like MYSQL.PHP) will be to eliminate all of the front-end functions by replacing those calls to them with their "command" versions throughout the code which (by design) are only in a few files.
The result will be the same functionality at half the size (of the previous versions of these MYSQL modules). Naturally, execution speed will increase. And the code shall be more easily understood.
This is code reduction taken to it's limit. And with each iteration even more pieces of the code will stand out as being "in common", enabling further reductions.
We expand on the process we put our code through in the file RESHAPING.
Notes
1. Making too many changes throughout the code at one time is the number one source of bugs.
2. In actuality there is one file per database/API with as few calls to the mysql_query()
function (or it's equivalent) as possible.
3. Maybe we can say, "code which is more easily understood is more efficient".