PHP – Saving Database Queries
Posted May 18th, 2009 by ConorThis is something I wrote today while building botb SNS – a project that I will have to start blogging about soon! It should be used on large scale projects that have multiple database queries. It will be very useful if you have so many queries that you can’t keep track of them and are afraid of re-querying incidentally. Enough said – down to the code:
<?php
class user{
var $id;
var $name='';
var $username='';
var $email='';
function __construct($id){
$this->id=$id;
}
function about($t){
if($this->$t!=”) return $this->$t;
$q=dbRow(‘select ‘.$t.’ from users where id=”‘.$this->id.’”‘);
$this->$t=$q[$t];
return $this->$t;
}
}
?>
You will obviously have to adjust this to suit your database setup. It also requires the following function:
function dbRow($q){
$s=mysql_query($q);
$r=mysql_fetch_assoc($s);
return $r;
}
As you can probably see this example simply echos database information. The main benefit of the class is it will never make the same query twice. Once you ask for the username it is stored within the class so if you ask for it again in the same page it doesn’t have to query the database again. You could look at it as a type of internal page caching. Also it is incredibly easy to use. All you need to do is:
$u=new user($id);
echo 'Query Database: '.$u->about('name');
echo '<br/> From Memory: '.$u->about('name');
Easy as that!
this will not work perfectly.
If you go “
$a=new user(1); var_dump($a->about()); var_dump($a->about());“, then there will only be one query.But, if you go “
$a=new user(1); var_dump($a->about()); $b=new user(1); var_dump($b->about());“, then there will be two queries.to solve that, you need to use a variant of the Singleton pattern. I’ll describe it later in a post on my own blog, but as an example, see this class. You would never call it using “$a=new Poll(1);”. instead, it’s called with “$a=Poll::getInstance(1);”, which ensures that previously created instances are not recreated
I am aware of the method you are talking about but I opted not to use it. You may at some point in the page change the contents of the users table and therefore you would need to create a new class, or even overwrite the old class $u=new user($id); in order to not display out dated information. I think that the method above works well because you could initiate the class at the top of your document, then continue to use it for the length of the document with the added advantage of every time you create a new database query being able to restate the value of $u and therefore avoid displaying old info.
Not only that, but sometimes you may need to retrieve information on two different users on one page. Hence the reason i think that the current method can be very useful, if you use it right.
I was actually just thinking about something like this on the luas in this morning for a project my Dad is doing, will point him at it
Mick