Click to change color scheme

Notes on codes, projects and everything

Database transactions involving prepared statements

I was thinking whether it is possible to avoid exposing PDO and PDOStatement objects to the users of my database library (mainly just me). While I was working on my project I sort of notice that there is a almost fixed pattern whenever I work with the database. With this in mind, I added in some new functions to the library, and decided to make a quick release for this.

Most of the time, issuing a database statement in PHP involves 3 steps. Firstly, one would want to prepare a statement. Then the statement is to be executed. If there’s no problem with the execution one would proceed with fetching the result. Writing these is fairly easy, and I am sure most people are familiar enough with the following statements,


$statement = $pdo->prepare('SELECT :foo');
$statement->execute(array(':foo' => 'bar'));
statement->fetch(); // or fetchAll(), fetchColumn() etc.

Then I started thinking whether I can abuse the power of closure to this. After some hacking, I have made the 3-step process into the following code,


use \coolsilon\database as db;
// step 0: make database object
$database = db\init($pdo);
// step 1: create the statement
$statement = call_user_func($database, 'SELECT :foo');
// step 2: execute the query
$result = call_user_func($statement, array(':foo' => 'bar'));
// step 3: fetching row(s) from the result
call_user_func($result); // which is the same as call_user_func($result, db\FETCH);
call_user_func($result, db\FETCH_ALL);
call_user_func($result, db\FETCH_OBJECT);
call_user_func($result, db\FETCH_COLUMN);
// I don't like this form, but should be perfectly OK
$result();
$result(db\FETCH_ALL);
$result(db\FETCH_OBJECT);
$result(db\FETCH_COLUMN);

Nothing much has changed at the relation-based query side though. Except when creating a relation generator, instead of passing in a PDO object to relation_generator function, a database closure is passed in instead. The function statement_execute is obviously dropped, in favor of this new form of interacting with database.


use \coolsilon\database as db;
// instead of this
$generator = db\relation_generator($pdo, json_reader());
// a generator is now
$generator = db\relation_generator(db\init($pdo), json_reader());
// getting a relation
$relation = call_user_func($generator, 'relation.json');
// getting a statement
$statement = call_user_func($relation, $statement_key);
// executing the statement
$result = call_user_func($statement, $data);
// getting row(s) from the result
call_user_func($result); // same as call_user_func($result, db\FETCH);
call_user_func($result, db\FETCH_COLUMN);
call_user_func($result, db\FETCH_OBJECT);
call_user_func($result, db\FETCH_ALL);

Basically this is all for now. An added bonus gained by simplifying function calls involving database is that I can further isolate the form library from the database library. While there is still a long way to go in making the

There is also some small changes made to the form library, where new widgets are introduced. To get an idea on what new widgets are being introduced, you may want to check the following schema.


"field": {
"favorite-number": {
"widget": {
"type": "text"
},
"validation": {
"integer": []
}
},
"email": {
"widget": {
"type": "text"
},
"validation": {
"email": []
}
},
"url": {
"widget": {
"type": "text"
},
"validation": {
"url": []
}
},
"photo": {
"widget": {
"type": "upload"
},
"validation": {
"type": ["image/jpg", "image/jpeg", "image/png", "image/gif"]
}
}
}

Basically there are a couple of new validation functions are added to handle integer data, URL and email. A new file upload widget is also added, and a list of allowed file types can be added. Considering I have poured in enough code, I guess tagging it as 0.2 is appropriate. Kindly check the code if you’re interested.

leave your comment

name is required

email is required

have a blog?

This blog uses scripts to assist and automate comment moderation, and the author of this blog post does not hold responsibility in the content of posted comments. Please note that activities such as flaming, ungrounded accusations as well as spamming will not be entertained.

Comments

@Ian Broadmore
Sorry for the late reply

The tarot reading script was not meant to be a serious working project, just a good enough attempt (read: proof-of-concept) for a college assignment project.

Also, it isn’t perfect as there’s no way to generate real random numbers via computer code in an easy way (you may want to view this video for more information <

>). The problem with this is that afaik, tarot reading rely a lot on randomness?

author
Jeffrey04
date
2013-10-5
time
17:05:58

Hi Im interested in your automated Tarot reading Script for my website. Or could you design me one?

author
Ian Broadmore
date
2013-10-2
time
21:25:26