A few years back I wrote some PHP code in rather a hurry and grabbed chunks of code from here there and everywhere. Now going back to it, I want to better understand the code and to refine it. At the same time checking that it remains an acceptable way of doing things and hasn't been outdated.
Going PDO
The first challenge is to create a class (following on from yesterday's post) that will connect to a database. In order to do this I use a PDO (or PHP Data Object) to connect to a MySQL database:class Connect {
public $dbh;
private $error;
static function dsn($type,$db,$host,$port) {
return $type.':dbname='.$db.';host='.$host.';port='.$port;
}
function __construct($dsn,$user,$password) {
try {
$this->dbh = new PDO($dsn, $user, $password);
}
catch (PDOException $e) {
$this->error=$e->getMessage();
exit('Not able to connect to database. Error message: '.$this->error);
}
}
}
I want the code to be as flexible as possible but not require much thought to implement, so if we know all the basic details then it will connect and then supply a reference to that connection.To obtain a reference to the PDO instance we do the following:
$user = 'root'; $password = 'root'; $db = 'BasicDatabase'; $host = 'localhost'; $port = 3306; $type = 'mysql'; $dsn = Connect::dsn($user,$password,$db,$host,$port,$type); $c = new Connect($dsn,$user,$password); $dbh = $c->dbh;Note: I don't really know why the abbreviation $dbh is used. I'm guessing it stands for database handler. I used it because that is the standard on the php.net site for naming the variable returned by the PDO instantiation.
Added values
To insert values into a table we use the PDO prepare() and execute() methods in combination with an SQL statement.$username = 'sketchyTech';
$user_id = 101;
$user = "INSERT INTO
user (
username,
userid
)
VALUES (
:username,
:userid
)";
$q = $dbh->prepare($user);
$q->execute(
array(
':username'=>$username,
':userid'=>$user_id
));
Where we have a table called user contained in our database.
Note: If the PDO fails the whole script ends, thanks to the exit() call but this probably isn't what you'd want in real use but will help with debugging.
Do you exist?
Other SQL statements can be entered in a similar way. This one checks for a username in a table.function doesUserExist($dbh,$username)
{
$sql = "SELECT count(*) FROM user WHERE username = '$username'";
$result = $dbh->prepare($sql);
$result->execute();
if($result->fetchColumn()) {
return true;
}
else return false;
}
doesUserExist($dbh,$username);
Retrieve data
You'll notice in the above method the use of fetchColumn() to identify the presence of an entry, but it is more than just a true/false check. We could use the result of this method to return data to us. For example:function userID($dbh,$username)
{
$sql = "SELECT count(*), userid FROM user WHERE username = '$username'";
$result = $dbh->prepare($sql);
$result->execute();
return $result->fetchColumn(1);
}
userID($dbh,$username);
Notice that there are two items after the SELECT command. These are indexed, so when we enter a number within the parentheses of the fetchColumn() method, this index refers to the items selected.Zero is the same as no number, both reference the first item, and 1 is the second item, etc. The thing about the fetchColumn() method is that it can only be used once and to retrieve the data from a single column. If it is called a second time then the data will be from the next row.
Retrieve even more data
There are alternative fetch methods, including simply fetch() which can be used to retrieve an array of values. For example:
function userData($dbh,$username)
{
$sql = "SELECT username, userid FROM user WHERE username = '$username'";
$result = $dbh->prepare($sql);
$result->execute();
return $result->fetch(PDO::FETCH_ASSOC);
}
foreach (userData($dbh,$username) as $key => $value) {
echo "Key: $key; Value: $value
\n";
}
Change data
function updateUsername($dbh,$username,$userid)
{
$sql = "UPDATE user SET username = :username WHERE userid = :userid";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(
':username'=>$username,
':userid'=>$userid
));
}
$userid = 106;
$new_username = "Smith";
updateUsername($dbh,$new_username,$user_id)
As with insert, a binding syntax is used to update the values.Deleting data
Deleting data follows a similar pattern of binding to variables:
function deleteUser($dbh,$userid)
{
$sql = "DELETE FROM user WHERE userid = :userid";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(
':userid'=>$userid
));
}
deleteUser($dbh,101);
Here we'll delete rows that have the userid 101.Transactions
Finally, I'll mention transactions. These are started with the beginTransaction() method and are followed by one or more actions before either the commit() method or the rollBack() method is called. The former of which will make the requested changes and the latter of which will roll them back and cancel them.
As explained in the documentation, beginTransaction() does the following:
Turns off autocommit mode. While autocommit mode is turned off, changes made to the database via the PDO object instance are not committed until you end the transaction by calling PDO::commit(). Calling PDO::rollBack() will roll back all changes to the database and return the connection to autocommit mode.
So whereas a standalone execute() command will update the database immediately, by using commit all actions will take place after the commit() statement (note: DROP TABLE or CREATE TABLE are an exception to this).
There are benefits to this behaviour:
There are benefits to this behaviour:
any work carried out in a transaction, even if it is carried out in stages, is guaranteed to be applied to the database safely, and without interference from other connections, when it is committed.
Conclusion
That's it for this post, to learn more about PHP and SQL visit the PDO pages of php.net.
Comments
Post a Comment