index

The Mysql Module

Mysql

This 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 smaller) has gone through the most changes of any other thing we have written for this code — and it can be improved still. We believe this code to be pretty good.

There are four groups of functions: records (aka posts), comments, users and visitors, each representing a separate database table. 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 has it's own SQL query string — we saw that as 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 is 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_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 — better code.

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 plan to expand on the process we put our code through (see file RESHAPING).

Notes

1. Making too many changes throughout the code at one time is the number one source of bugs. Ours is a step by step process.
2. Maybe we can say, "code which is more easily understood is more efficient".