SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,098
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    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
    PHP Code:
    $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("\t"''$data);
            
    $data rtrim($data"\n");
            
    $pieces explode("\n"$data);
        
            
    $find  = array("\n""\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 == || $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("\n""\n ,"$select);
                
    $select substr($select0, -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
    PHP Code:
    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.
    Last edited by lorenw; Dec 1, 2013 at 14:32. Reason: Forgot a few things.
    What I lack in acuracy I make up for in misteaks

  2. #2
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,246
    Mentioned
    16 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code php:
    $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:

    Code php:
    $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.
    "First make it work. Then make it better."

  3. #3
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    $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.

  4. #4
    SitePoint Addict bronze trophy
    Join Date
    Apr 2013
    Location
    Ithaca
    Posts
    351
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)
    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.

  5. #5
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,098
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    $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.
    What I lack in acuracy I make up for in misteaks

  6. #6
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lorenw View Post
    PHP Code:
    $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>';

    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.

  7. #7
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,098
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    What I lack in acuracy I make up for in misteaks

  8. #8
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Let's look at what a normal class would look like extending / injecting PDO.

    Code PHP:
    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.

  9. #9
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lorenw View Post
    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.
    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:

    PHP Code:
    $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.

  10. #10
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    I don't think you wasted your time - what you've learned by your attempts is yours.
    Meant to include something like this as well. This is how you advance!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •