PDO "helper" Class to make queries super simple or am I wasting my time?

Hello all,

I am making the move to PDO but I’m lazy and don’t like the redundancy of writing the queries so I made this.
The queries are dirt simple.
Just looking for your comments on this approach.

pdo.php


$debug = 'y';

class sql {

	private function prepare($action, $data){
		$retwhere = '';
		$params = '';
		$retinsert1 = '';
		$retinsert2 = '';
		$retset = '';
		$paramsset = '';

// action can be u=update, i=insert or w=where
// remove tab indents		
		$data = str_replace("\	", '', $data);
		$data = rtrim($data, "\
");
		$pieces = explode("\
", $data);
	
		$find  = array("\
", "\\r");
		$replace = array('', '');
		$pieces = str_replace($find, $replace, $pieces);
		$i = 1;
		$len = count($pieces);
		
		foreach ($pieces as $piece){
			list($key, $value) = explode('=', $piece);
			$key = trim($key);	
			$value = trim($value);

// automatically puts in AND , OR - AND must come first in the query before OR.
			if ($len == 1 || $len == $i || $pieces["$i"] == '') {
				$and = '';
				$com = '';
			}
			else{
				$and = ' AND ';
				$com = ',';
			}
		
				$keyo = str_replace('.', '', $key);
			if ($action == 'w'){ // where
// take out the table alias dots ie... users.username			
//where, add a "w" here so there can be WHERE "username" and UPDATE "username" without collisions

// add $i for OR, where name = loren OR name = tom

				if (empty ($key)){
					$retwhere .= ' OR ';
				}
				else{
// $i will always be the next array element, first time match $or will not be set
					if (empty($pieces["$i"]) && !isset($or)){
						$lp = '(';
						$or = 'y';
					}
					if ($or == 'y' && $len == $i) {
						$rp = ')';
					}
					else {
						if (!isset($or)){ 
							$lp = '';
							$rp = '';
						}
					}
					
 					$retwhere .= $lp.$key.' = :w'.$keyo.$i.$and.$rp;
					$params[':w'.$keyo.$i] = $value;

				}
 		} // end action w
						
			elseif ($action == 'i') {
				$retinsert1 .= $keyo.$com;
				$retinsert2 .= ':w'.$keyo.$i.$com;
				$params[':w'.$keyo.$i] = $value;
				}
			elseif ($action == 'u'){ // update
				$retset .= $keyo.' = :'.$keyo.$com;
				$paramsset[$keyo] = $value;
			}
// we only need one $lp so reset
	$lp = '';
	$i++;
		}// loop
		return array(
			"retwhere" => $retwhere,
			"params" => $params,
			"retinsert1" => $retinsert1,
			"retinsert2" => $retinsert2,
			"retset" => $retset,
			"paramsset" => $paramsset
		);
	} // prepare
	
	public function pdo($action, $table, $set, $where, $select = '', $ol = ''){
	global $debug;

		$stmt = '';	
		$opt = array(
			PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
			PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
		);
		$pdo = new PDO("mysql:host=localhost;dbname=loren", 'root', 'poi', $opt);
		
		
		if($action == 'update'){
			$prepareU = $this->prepare('u', $set);
				if ($debug == 'y'){
					echo'<pre>paramsset '; print_r($prepare['paramsset']); echo '</pre>';
				}
		}
// prepare
		if ($action == 'insert'){
			$prepare = $this->prepare('i', $where);
		}
		else{		
			$prepare = $this->prepare('w', $where);
		}		
			if ($debug == 'y'){
				echo'<pre>where '; print_r($prepare['params']); echo '</pre>';
			}
		if ($prepareU['paramsset']){ // used for update
			$params = array_merge((array)$prepareU['paramsset'], (array)$prepare['params']);
			if ($debug == 'y'){
				echo'<pre>merge if update '; print_r($params); echo '</pre>';
			}
		}
		else {
			$params = $prepare['params'];
		}


		if ($action == 'select'){
		
// put in ,'s and get rid of the last one
			$select = str_replace("\
", "\
 ,", $select);
			$select = substr($select, 0, -1);  

			if ($where == ''){
				$q = "SELECT  $select FROM $table";
			}
			else{
				$q = "SELECT  $select FROM $table  WHERE ".$prepare['retwhere']." $ol";
			}
		}
		if ($action == 'insert'){
			$q = "INSERT INTO $table (".$prepare['retinsert1'].") VALUES (".$prepare['retinsert2'].")";
		}
		if ($action == 'update'){
			$q = "UPDATE $table SET ".$prepareU['retset']." WHERE ".$prepare['retwhere']."";
		}
		if ($action == 'delete'){
			$q = "DELETE FROM $table  WHERE ".$prepare['retwhere']."";
		}
		if ($action == 'query'){
			$q = "$select";
		}
		if ($debug == 'y'){
			echo'<pre>params to execute, WHERE and UPDATE '; print_r($params); echo '</pre>';
			echo '<br>'.$q.'<br>';
		}
		
		$stmt = $pdo->prepare($q);
		$stmt->execute($params);
		
		if($action == 'select'){
			return $stmt->fetchAll(PDO::FETCH_ASSOC);
		}
		elseif ($action == 'insert'){
			return $pdo->lastInsertId(); 
		}
		else {
			return $stmt->rowCount();
		}
	$pdo = null;
	}
//	$this->sql = null;
} // class pdo


The queries format


include('./pdo.php');
$sql = new sql;

$table = 'customers';

//select
$select = 'firstname
lastname
phone
';
// single new line = AND, two new lines = OR. AND has to be first before OR
$where = 'phone = 1231231234
state = FL

state = WA
';
$rows = $sql->pdo('select', $table, '',  $where, $select, $ol);
	foreach ($rows as $row){
		echo'<pre>'; print_r($row); echo '</pre>';
	}


// insert
$set = 'phone = 1231231234
firstname = loren
lastname = wolsiffer
state = WA
';
//	notice position of $set, this gets processed the same as $where
$row = $sql->pdo('insert', $table, '', $set);
echo 'last id '.$row.'<br>';


 // update
$set = 'firstname = fred
lastname = barney
';
$where = 'phone = 1231231234
';
$rows = $sql->pdo('update', $table, $set, $where);

echo 'affected '.$rows.'<br>';

// delete
$where = 'firstname = fghgfhfhg
';
$rows = $sql->pdo('delete', $table, '', $where);
echo 'deleted rows '.$rows.'<br>';

This is my first class so I may not be doing it entirely correct.
It’s good to learn PDO and this was a good learning experience . This makes it easy to update all of my old mysql queries very quickly.

Thanks for looking and any comments you may have.

One thing it’s missing is IN clause, I have been slowly adding to this and it works fine with joins and table aliases.
I hope it’s not a waste of time, I doubt it could ever be a one size fits all solution but works for what I do.

There are quite a few projects that set out to do this sort of thing, so it’s certainly a worthwhile endeavor, but I don’t think this one is where we’d want it to be yet.

We would be replacing:

$stmt = $pdo->prepare(
    'SELECT
        firstname, lastname, phone
    FROM
        customers
    WHERE
        phone = :phone AND (state = :state1 OR state = :state2)'
);
$stmt->execute(array(
    ':phone' => '1231231234',
    ':state1' => 'FL',
    ':state2' => 'WA',
));
$rows = $stmt->fetchAll();

with:

$table = 'customers';

//select
$select = 'firstname
lastname
phone
';
// single new line = AND, two new lines = OR. AND has to be first before OR
$where = 'phone = 1231231234
state = FL

state = WA
';
$rows = $sql->pdo('select', $table, '',  $where, $select, $ol);

That doesn’t look simpler to me. Plus, the data I bind to the statement can never have tabs, newlines, or equal signs, among other characters that will get stripped out. Plus, the way we denote ANDs and ORs and parenthese grouping in the WHERE clause seems unintuitive and can’t represent all conditions.

I always use a wrapper class for db queries because I don’t like the redundancy, either. But I’m not too fond of this type of simplification methods you chose because they don’t really simplify much and seeing full SQL query is much more readable than the ‘simplified’ code you have provided, even if it takes a bit more characters, and much easier for debugging.

Also, there is the problem of expanding your queries - let’s say you have a simple select that you execute with your simplified methods. After some time you want to add an inner join - your methods don’t cover inner joins so you add this functionality to your methods and pass data for the join in some new parameters. After some time you want to add a left join - so you need to expand your methods again. But then the join order may matter so you need to code that, too. After some time you may want to add another type of join, you may want to fetch a few columns from joined tables as well, you may want to use SQL functions in your select columns or in the ORDER BY clause or the WHERE clause, you may want to group several logical AND/OR conditions just like with parentheses, or you may want to add a special flag to your SQL like SQL_NO_CACHE or FOR UPDATE or FORCE_INDEX, etc. The list goes on and on and you may end up with a huge ‘simplification’ class. Sure, you may want to simplify only the simple queries but then any expansion will require changing the whole method of interacting with the db - if that is so I prefer to write SQL from the beginning.

It’s a slightly different thing if your goal is not query simplification but database type independence. Then you need some common way of executing queries and then this way makes sense - there are already many libraries that achieve this goal. This will always have its drawbacks because you may find it hard to fine-tune and optimize your queries for each database type where necessary but depending on the project it may not be a big problem. It all comes down to what requirements you have.

What I do is I don’t try to simplify SQL but the PHP methods I use. So instead of the too verbose mysqli or PDO methods I use one-liners like this:


$phone_no = $db->fetchOneValue("SELECT phone_no FROM clients WHERE id=" .(int) $id);

// or with prepared statements:

$phone_no = $db->fetchOneValue("SELECT phone_no FROM clients WHERE id=:id", array("id" => $id));

And so I have one-liners for fetching all field values from a row, all rows from the result set, etc. This saves me typing while the queries look natural and I don’t have to learn another ala-SQL language or interface apart from SQL (that I need to know anyway) and a few convenience one-liner methods.

You are definitely not wasting your time, there’s nothing wrong with being ‘lazy’. In fact, it’s the need for lazy programming that results in so many reusable softwares and even modern programming languages themselves. In your code, you are effectively generating dynamic SQL queries, some are even reusable and easily modifiable.

You can take one step further to use a Query object with methods such as table(), select(), insert(), update(), delete(), where(), order(), limit(), etc to simplify the execution of SQL queries. You can even create Criterion objects used for WhereClause to get more abstraction/encapsulation. The advantage is that you do not even have to write SQL in PHP(except the Query/Criterion classes itself which encapsulates this process), it achieves separation of concerns. It also allows your client coders who dont know SQL syntax to use your library easily(assuming you work in a team). The disadvantage is, of course, a performance penalty. Whether the advantage outweighs the disadvantage depends on the complexity of your application, generally the advantages are more evident on larger/growing softwares that execute a good number of queries.

Thank you all for your replies.
@Lemon Juice, I did test with joins and it goes a long way without problem and I agree, it does have scale-ability issues. It also has the optional $ol (order by, group and limit).

@Hall of Famer, Thanks. It amazes me how hard I work to be lazy and I like your idea of Query objects.
I really had no blueprint and just started building it. Perhaps a redesign in the future.

Just to push it and see what it could do, It ran this radius query fine.


$table = "radacct AS acct

LEFT JOIN radlookupnas as rlnas
ON rlnas.user = acct.UserName 

LEFT JOIN radcheck as chktime
ON chktime.UserName = rlnas.user
AND chktime.Attribute = 'Max-All-Session'


LEFT JOIN radcheck as chkexp
ON chkexp.UserName = rlnas.user
AND chkexp.Attribute = 'Expiration'

LEFT JOIN radreply as repbw
ON repbw.UserName = rlnas.user
AND repbw.Attribute = 'ChilliSpot-Max-Total-Octets'
";


$select = 'rlnas.user as user
chkexp.value as expires
repbw.value AS maxoctets
chktime.value AS maxsession
SUM(acct.AcctInputOctets) AS Upload
SUM(acct.AcctOutputOctets) AS Download
SUM(acct.AcctSessionTime) AS timeused
SUM(acct.AcctInputOctets + acct.AcctOutputOctets) as bwused
max(acct.AcctStartTime) as LastLogin
min(acct.AcctStartTime) as FirstLogin
COUNT(acct.RadAcctId) AS logins
rlnas.nasid as nasid
min(acct.AcctStopTime) AS online
';

$where = "rlnas.nasid = $nasid
";

 $ol = 'GROUP BY acct.UserName 
ORDER BY max(acct.AcctStartTime) DESC
';

$rowj = $sql->pdo('select', $table, '',  $where, $select, $ol);

foreach ($rowj as $val){
echo'<pre>val '; print_r($val); echo '</pre>';
}

Thanks again for the comments.

If I may comment on this piece of code - to me this kind of implementation makes no sense. What are you trying to achieve? You are not providing support for joins, order by clauses, functions, etc. - what you are basically doing is building your sql query by concatenating strings in a convoluted manner. In this case just plain sql in one chunk would be much more useful and readable. Either you go for solid sql abstraction (like db query objects) or just leave plain sql without such half cooked recipes. I can’t see how this solution can simplify anything - it just makes things more complicated because your query appears segmented, you have to know the order of your method parameters, etc. and you gain no benefit of database abstraction.

I’m probably wasting my time as I suspected BUT I did learn PDO which was the “object” anyway.
I’ll be back to writing out my queries and had home fun with this.
Thanks.

Let’s look at what a normal class would look like extending / injecting PDO.


class User {
     private $pdo;
    public function __construct(PDO $pdo) {
         $this->pdo = $pdo;
    }

    public function addAccount($email, $firstName, $LastName) {
         $stmt = $pdo->prepare("SQL?");
         $stmt->execute(array(':email' => $email, ':firstName' => $firstname, ':lastname' => $lastName));
         return $stmt->fetchAll();
    }

   public function checkExp($foo) {
         $stmt = $pdo->prepare("SQL?");
         $stmt->execute(array(':foo' => $foo));
         return $stmt->fetchAll();
    }
}

The only redundant code here I see that you could potentially write once somewhere else is $stmt = $pdo->prepare(), $stmt->execute(), and the return. A bit redundant yes, but these are the parts that can vary greatly. I might not always want to return a fetchAll() for example. Your gain is to make sure you are building proper objects so that now all you have to do is $user = new User; $user->addAccount($email, $firstName, $LastName);

IMO the biggest time saver you can come across is knowing that all of your errors / notices / exceptions or being handled. Ehemm (signature) cough.

I don’t think you wasted your time - what you’ve learned by your attempts is yours. Making life easy is a very good goal for a programmer but it’s important to ask yourself if the solution is really making your life easier.

What I know is that what you were trying to accomplish is not an easy goal. There are many huge frameworks for simplifying and abstracting db access - you may want to look at Doctrine or Proper. Interacting with db via objects is not an easy thing if you want to go there to the full and get rid of SQL completely. You often end up with a php framework that takes quite some time to learn so you need to ask yourself if it’s worth the effort.

As to your implementation, I also use such shortcuts but only for some simplest use cases, for example:


$db->insert("my_table", array(
  "id" => 22,
  "name" => "Lemon",
  "active" => 1
));

$db->update("my_table", array(
  "active" => 0),
  "id=22"  // WHERE clause
);

The first one is quite common and I like it because it offers quite some level of abstraction and I don’t have to pass any sql to it. I like the second one less because partially it does string concatenation to build the query so it’s not that elegant any more. If I have to pass literal parts of sql to my ‘simplifying’ method then I think this whole idea becomes less sensible because writing plain sql is often simpler and the code is easier to understand.

Meant to include something like this as well. This is how you advance!