| Previous | Up | Next |
| Caching | phpHtmlLib Framework | Form Engine |
The phpHtmlLib Database object uses php5's PDO object internall to do database requests. The Database object creates a common api for doing normal sql queries in an easy to use fashion. It also has apis for doing cached queries.
Why not just use PDO?
For large scale applications PDO can be combersome to do very simple things. Sometimes a database maybe set up to have a very fast connection timeout to help prevent too many stale connections. Currently with php 5.2.1, there is no way to have a PDO object automatically detect a connection timeout and re-connect. So to trap for this using PDO, you have to wrape EVERY query you do with a check for a very specific failure and then create a new PDO object once the failure is detected, then retry the query. Very much a pain in the ....
The phpHtmlLib Database object automatically detects theses errors for you and by default will retry to connect to the database twice before giving up.
How do you connect to a database?
<?php
//first create a DSN
$dsn = DataBase::dsn('mysql', 'mydbname', 'myhostname');
$db = new DataBase($dsn, 'myusername', 'mypassword');
?>
By default the DataBase object will set up PDO to use exceptions for errors and force all column names to be lower case. You can of course change that.
The DataBase object provides the same APIs as PDO does for doing querys with some additional APIs to make things easier.
disconnect() - disconnect to the db.
reconnect() - force a disconnect and then a new connection
set_cache_obj() - set the Cache object child for cached queries
insert() - insert a row into a table.
update() - update a row in a table.
delete() - delete a row(s) from a table.
queryBind() - simple select query with bind variables
queryBindOneRow() - bind query and return only 1 row.
queryBindOneRowCache() - cached version of queryBindOneRow.
queryBindAllRowsCache() - cached query to return ALL rows..
queryBindOneValue() - bind query and return 1 column.
queryBindOneValueCache() - cached version of queryBindOneValue
queryBindLimit() - bind query and return n rows with x offset
How do you insert 1 row into a table.
<?php
//assume we have a DataBase object already
try {
//insert a new user into the user table
//insert returns the id of the new record.
$id = $db->insert('user', array('firstname' => 'Walt',
'lastname' => 'Broing',
'email' => 'waboring@newsblob.com'));
} catch (Exception $e) {
echo "Something went wrong inserting into the user table '.$e->getMessage();
}
?>
How do you update an existing row in a table?
<?php
//assume we have a DataBase object already
try {
//lets fix my last name. we assume the id of the user
// in the table is 69.
//update internally uses a bindQuery to do the work.
//so all variables are passed as bind variables
//even in the where clause portion. This should
//make doing similar updates quicker.
$db->update('user', array('lastname' => ':last'),
'user_id=:id',
array(':last' => 'Boring', ':id' => 69));
} catch (Exception $e) {
echo "Something went wrong updating the user '.$e->getMessage();
}
?>
How do you delete a row in a table?
<?php
//assume we have a DataBase object already
try {
//delete me assuming my user_id is 69
$db->delete('user', 'user_id=:id', array(':id' => 69));
} catch (Exception $e) {
echo "Something went wrong deleting the user '.$e->getMessage();
}
?>
How do I get rows from a table?
<?php
//assume we have a DataBase object already
try {
//get a specific user
$sql = 'select * from user where lastname like :name';
$bind_vars = array(':name' => 'B%');
$stmt = $db->queryBind($sql, $bind_vars);
} catch (Exception $e) {
echo "Failed to get a user. '.$e->getMessage();
exit;
}
//now do something.
while ($row = $stmt->fetch()) {
echo $row->firstname.' '.$row->lastname."\n";
}
?>
How do I get 1 row from a table?
<?php
//assume we have a DataBase object already
try {
//get a specific user
$sql = 'select * from user where user_id=:id';
$bind_vars = array(':id' => 69);
$row = $db->queryBindOneRow($sql, $bind_vars);
} catch (Exception $e) {
echo "Failed to get a user. '.$e->getMessage();
exit;
}
//now do something.
echo $row->firstname.' '.$row->lastname."\n";
?>
How do I get 1 value from a table?
<?php
//assume we have a DataBase object already
try {
//get a specific user
$sql = 'select email from user where user_id=:id';
$bind_vars = array(':id' => 69);
$email = $db->queryBindOneValue($sql, $bind_vars);
} catch (Exception $e) {
echo "Failed to get a user email address. '.$e->getMessage();
exit;
}
//now do something.
echo $email"\n";
?>
How do I get 1 row from a table using a cached query? This uses the FileCache object, which will store the cache values hashing to 1 file on disk in the /tmp/phphtmllib-cache directory. The cache key will be constructed from the query parameters passed in.
<?php
//assume we have a DataBase object already
//make sure the cache object is set
$db->set_cache_obj(FileCache::singleton());
try {
//get a specific user
$sql = 'select * from user where user_id=:id';
$bind_vars = array(':id' => 69);
//how long do you want the cache to be valid
//in seconds.
$timeout = 120;
$row = $db->queryBindOneRowCache($sql, $bind_vars, $timeout);
} catch (Exception $e) {
echo "Failed to get a user. '.$e->getMessage();
exit;
}
//now do something.
echo $row->firstname.' '.$row->lastname."\n";
?>
phpHtmlLib is released under GNU LGPL
| Previous | Up | Next |
| Caching | phpHtmlLib Framework | Form Engine |
Documentation generated on Thu, 11 Oct 2007 12:05:14 -0700 by phpDocumentor 1.4.0