PHP

PEAR::DB Tutorial

This tutorial will help the reader come to terms with using PEAR:DB. Maybe the first thing I should tell you all is that DB is in fact depreciated, replaced by MDB2. If you want to use MDB2 that is fine, however, the functions have changed a little and not many people (well none according to Google) have written proper tutorials other than the 20000000000000000 page documentation that comes with it.

This is a continuation of a previous post. I decided to make a new post for this since this article could and should really be on its own.

What is PEAR:DB? DB is a database abstract layer. Still a bit confused? Okay, basically imagine your PHP code with the SQL in it, underneath that is the SQL server right? Wrong, DB acts as a layer between those taking in standardized functions and spitting them out as database queries only difference is that DB can spit these queries out for almost every main stream server out there.

This means in layman’s terms that you would never have to rewrite SQL code so long as you live and not only that you get some pretty cool functions that make your life so much easier. I love easy, I never have time for hard.

To install DB simply type into terminal:

sudo apt-get install php-db

Now in order to use DB you must first specify your database like:

<?php
	function db_connect() {
		require_once 'DB.php';
		PEAR::setErrorHandling(PEAR_ERROR_DIE);
		$db_host = 'localhost';
		$db_user = 'root';
		$db_pass = 'me';
		$db_name = 'thedb';
		$dsn = "mysql://$db_user:$db_pass@$db_host/$db_name";
		$db = DB::connect($dsn);
		$db->setFetchMode(DB_FETCHMODE_OBJECT);
		return $db;
	}
?>

Now if you have done MySQL in PHP before this should be easily understood. I am getting the servers details and then $dsn is a compilation of these strings which can be sent to DB through DB::connect to connect to the database. Even though the folder for DB has been included within PHP itself I have still included the actual file within the db_connect() function. This is the one and only time youll have to do this.

Name this file whatever you want just not dbconnect.php/inc (unless its a isolated localhost server then you haven’t got many problems). I read a blog once, I can’t remember the blogs location, but the author was talking about how a lot of sites name their database connection files dbconnect.inc/php or something similar. The author decided to try and see if he could hack database servers by exploiting this vulnerability. What he discovered horrified me. He was able to hack 1000+ sites by just downloading this single PHP file. So the first and foremost rule is:

Do not name your connection settings dbconnect.php/inc

Okay, the connection is made now you need to include the file into another (called….I dunno I’ll just call it commonfuncs.php just as a quick example, hopefully you won’t) which is then included into all your pages.

Now the main commonfuncs.php would look something like:

<?php

	require_once('dbconnection.php');
        $db = db_connect();

?>

This $db is an object (Object Oriented Programming concept). This object acts as your database now so that if you want to make changes to your database you say “$db->something”, example:

        //include the file above that we made with the $db object in
        require_once("commonfuncs.php");

	function _isLocked($username){
		
		$username = $this->db->quote($username);
		//can I access my account?
		$sql = "SELECT * FROM tbluser WHERE " .
		"Username = $username";
		
		$result = $this->db->getRow($sql);
		
		//has account been disabled
		if ($result->Locked) {
			return true;
		}else{
			//phew
			return false;
		}
	}

In this example I am using two very key functions of DB. Now in most MySQL escape functions they tend to add symbols to stop malicious sets of data entering the table query base. DB can escape strings without having to enter any back slashes or nothing. It simply double quotes so it enters the table single quoted (as it should) so it reads back out fine without messing up the database. This DB function is $db->quote($string);. Remember this function, it is your friend.

The next function is getRow($sql). the getRow function gets only a single row. This saves space and time meaning that instead of querying the database for a set you are only querying for one row which means it is only looking for one row and not many…get it yet? Of course after pulling the row you can use is_object($result) on the resulting variable to judge if there is a row or now (object true if row). To use the rows data the resulting variable acts exactly as an object so i.e. $result->Locked (Locked being a field).

We now move onto getting multiple rows. We have learnt how to get a single row and how to escape strings to make safe our data. Here is a perfect example of selecting multiple rows:

     $sql = "SELECT * FROM tblvideo INNER JOIN tbluser ON tblvideo.UserID = tbluser.UserID ORDER BY tblvideo.DateAdded DESC".$limit;
     $qresult = $user->db->query($sql);
     
     while($qresult->fetchInto($qrow, DB_FETCHMODE_ASSOC)){
         //extract($qrow);   
         $qrow["VideoThumbUrl"]  = "loadVideoThumb.php?location=" . $qrow["VideoThumbUrl"]; 
         $qrow["AvatarUrl"]  = "avatar/load_avatar.php?page=search&amp;atr=" . $qrow["AvatarUrl"];
         
         $expdate = $qrow["DateAdded"];
         $temp1=explode(" ",$expdate);  
         $temp=explode("-",$temp1[0]);
         $time=explode(":",$temp1[1]);  
         $year = $temp[0];  
         $month= $temp[1];  
         $day = $temp[2];  
         $hour = $time[0];  
         $minute = $time[1];  
         $second = $time[2];   
         $countdown_date = mktime($hour, $minute, $second, $month, $day, $year);  
         $today = time();  
         $diff = $today - $countdown_date;  
         if ($diff < 0)$diff = 0;  
         $dl = floor($diff/60/60/24);  
         $hl = floor(($diff - $dl*60*60*24)/60/60);  
         $ml = floor(($diff - $dl*60*60*24 - $hl*60*60)/60);  
         $sl = floor(($diff - $dl*60*60*24 - $hl*60*60 - $ml*60)); 
         list($dl,$hl,$ml,$sl) = array($dl, $hl, $ml, $sl);    
         
         //OUTPUT  
         $timesince = '';
                
         if($dl != '0'){
         	$timesince .= $dl." days ";
         }elseif($hl != '0'){
         	$timesince .= $hl." hr ";
         }elseif($ml != '0'){
            $timesince .= $ml." min ".$sl." sec ";
         }       
         if($timesince == ''){
            $timesince .= $sl." sec ";
         }
                
         $qrow["DateAdded"] = $timesince;  
         $trisql = "SELECT AVG(Rating) FROM tblvrating WHERE VideoID = ".$qrow["VideoID"];
                
         $triresult = $user->db->query($trisql);
                
         while($triresult->fetchInto($trirow, DB_FETCHMODE_ASSOC)){      
         	$qrow["Rating"] = floor($trirow['AVG(Rating)']);
         }
                
      $rows[] = $qrow;
         
     }
     
     $qresult->free();            

Okay, so it’s not a good example of programming techniques and standards but for what I want to display it is good. See at the top instead of using getRow I just say query. This query function will work for updates, inserts, deletes and any other SQL statement. It is effectively the normal select compared to the specific select of getRow.

Straight after I begin a while loop. This while loop condition is what you use to extract the rows from the query result set. Within the fetchInto function you put the variable (array) you want each row to go into and the fetch mode. You can see that by my fine example you can dynamically perform functions on the data so you don’t need to form the rows and then perform actions on them you just do as you go along. At the bottom you will find that every $qrow (extracted row) gets added to a new main array that will house the readable and altered result set. After completion of the while loop I just free the memory and variable by saying “$qresult->free()”. This frees result sets from memory allowing for huge result sets to effectively be deleted before proceeding.

Now you have seen briefly the power of DB, hopefully with these new functions within your head you will be able to start programming with DB as well. Remember if you have any questions just ask, I know I’m a terrible blog writer.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s