I wrote a very elegant database interaction class for PHP some time ago. The class is a simple layer between a PHP application and its database and provides a very clean and efficient interface to the database. It does not generate the SQL code for you, but rather it makes a cleaner method of calling your SQL code. It allows you to generate repeatable queries as objects, provides parameter substitution in queries, and allows reading a record via class accessors. Some samples of these features are shown below.
I have not posted the source code itself as I feel this is one of my more exquisite projects and I don't want to see it taken without credit. I may be willing to provide the code on request though.
Examples: A simple reader query. (Select)
$users = new query("SELECT userID, username, lastAccess, enabled FROM users;");
if(!$users->is_valid())
return false;
while($users->fetch())
{
echo $users->usersID;
}
A simple non-reader query. (Insert, Update, Delete)
if(!query::run("UPDATE users SET session = '$userSessionID', lastAccess = NOW() WHERE userID = $userID;"))
throw new Exception("Database update failed.");
The same update only using parameters instead of string substitution. There are two ways to do this and both generate identical SQL code.
return query::run("UPDATE users SET session = @1, lastAccess = NOW() WHERE userID = @2;", $userSessionID, $userID);
return query::run("UPDATE users SET session = @sessionID, lastAccess = NOW() WHERE userID = @usersID;",
array(sessionID => $userSessionID, usersID => $userID));
All three of the above update calls will generate the following statement. Notice how the second two statements automatically quote string and escape any special characters in strings.
UPDATE users SET session = 'SESSION', lastAccess = NOW() WHERE userID = UID;
You can also use parameters in reader queries exactly the same way as above. Also you can prepare the query and then set parameters/execute the query as a seperate step. Again the folloing are identical.
$users = new query("SELECT userID, username, lastAccess, enabled FROM users WHERE username = @username;", false);
$users->username = $username;
$users->execute();
$users = new query("SELECT userID, username, lastAccess, enabled FROM users WHERE username = @1;", false);
$users->execute(true, $username);
$users = new query("SELECT userID, username, lastAccess, enabled FROM users WHERE username = @username;", false);
$users->execute(true, array(username => $username);
To read the results there are a few other options as well.
$users = new query("SELECT userID, username, lastAccess FROM users WHERE username = @username;", false);
foreach($users as $user) {
$users->username = $user;
if(!$users->execute())
continus;
echo $users->usersID; // Get a coulmn value.
print_r($users->get_row()); // Print the entire row.
echo $users->get_md5(); // Time dependent hash.
echo $users->get_md5('userID', 'username'); // UserID/Username dependent hash.
echo $users->get_columns(); // Get a list of loaded columns.
}
Also there are a few other calls that may be useful. You can get the number or records and raw SQL statement like so.
$users = new query("SELECT userID, username, lastAccess, enabled FROM users;");
echo $users->get_length();
echo $users->get_last_sql();
query::run("UPDATE users SET groupName = @1, lastEdit = NOW() WHERE groupName = @2;", $newGroupName, $groupName);
echo query::length();
echo query::last_sql();
