The Mysql Module
Mysql
The MYSQL.PHP
module is the only source file that has SQL strings. The code chooses the mysqli
functions if available else it will use the mysql
functions.
This module (first one, then made into two, and now back into one and much, much smaller) has gone through the most changes of any other we have written for this code — and it can be improved still. We believe this code to be pretty good (though odd).
There are four groups of database functions, each representing a separate database table: records (aka posts), comments, users and visitors. They each have common names:
dblistrecords($id)
dbreadrecord($id)
dbnewrecord($data)
dblistcomments($entryid)
dbreadcomment($id)
dbnewcomment($entryid, $record)
dbreaduser($userid)
dbnewuser($userid, $record)
dbreadvisitor($from)
dbnewvisitor($from, $record)
In one of the previous versions of the code each of those functions had it's own SQL query string, query call, result checking and row processing — we saw that as too redundant and too complex. So now there is something odd. There is one function for each database table:
dbrecord(); dbcomment(); dbuser(); dbvisitor();
But we left all those other functions is as they are called from several files. What we did was to change all the previous functions to be something like these:
function dblistrecords($id = NULL) {
return dbrecord('list',$id);
}
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) {
if ($data)
$data = mysql_real_escape_string($data);
$db_table = get_stored_table_name();
$sql = array(
'list' => "SELECT id FROM `$db_table` ORDER BY id",
'read' => "SELECT body FROM `$db_table` WHERE id = $id",
'new' => "INSERT INTO `$db_table` (body) VALUES ('$data')",
}
$res = mysql_query($sql[$cmd]);
...
}
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.
The next step will be to replace all the database calls to their "command" equivalents, further reducing code size and complexity.
Notes- For now. See next note.
- Making too many changes throughout the code at one time is the number one source of bugs (known by experience). This is a step by step process.