Page 1 of 2

Pagination Class

Posted: Thu Oct 01, 2009 12:50 am
by dude81
Hi
This is my DB class

Code: Select all

 
<?php
class DB{
    
    protected $db;
    
    public function __construct($config_path = 'config/config.ini'){
        $db_array = parse_ini_file($config_path,true);
        $this->getDsn($db_array);
        //print_r($this->db);
    }
    
    private function getDsn($db_array){
        
        $dbhost = $db_array['DB']['host'];
        $dbuser = $db_array['DB']['dbuser'];
        $dbpass = $db_array['DB']['dbpass'];
        $persistent = $db_array['DB']['dbpersistent'];
        $dbtype = $db_array['DB']['dbtype'];
        $dbname = $db_array['DB']['dbname'];
        
        switch ($dbtype){
            
            case "mysql":
                
                if(!$persistent){
                    $this->db = new PDO("$dbtype:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
                }else{
                    $this->db = new PDO("$dbtype:host=$dbhost;dbname=$dbname",$dbuser,$dbpass,array( PDO::ATTR_PERSISTENT=>true));
                }
                    $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                break;
                
            case "oracle":
                //Code for oracle dsn  
                break;
        }
        //return $this->db; 
        
        
    }
    
    public function selectArrayNumeric($sql, $params = array()){
        try{
            $sth = $this->db->prepare($sql);
            $sth->execute($params);
            $result = $sth->setFetchMode(PDO::FETCH_NUM);
            return $sth->fetchAll();
        }catch(PDOException $e){
            print $e->getMessage();
            //Log this to a file later when in production
            exit;
        }
        
    }
    
    public function selectArrayAssoc($sql, $params = array()){
        try{
            $sth = $this->db->prepare($sql);
            $sth->execute($params);
            $result = $sth->setFetchMode(PDO::FETCH_ASSOC);
            return $sth->fetchAll();
        }catch(PDOException $e){
            print $e->getMessage();
            //Log this to a file later when in production
            exit;
        }
    }
    
    public function selectObj($sql, $params = array()){
        
        try{
            $sth = $this->db->prepare($sql);
            $sth->execute($params);
            $result = $sth->setFetchMode(PDO::FETCH_OBJ);
            return $sth->fetchAll();
        }catch(PDOException $e){
            print $e->getMessage();
            //Log this to a file later when in production
            exit;
        }
    }
    
    
    public function executeInsertSQL($fields=array(), $values=array(),$table=''){
        if(!empty($fields) && !empty($values) && !empty($table)){
            if(count($fields) !=count ($values)){
                throw new Exception("Field count does not match the values count");
                exit;
            }
            
            $sql = "INSERT INTO $table ";
 
            $sql_fields = "(";
            $sql_values = "(";
 
            for($i=0; $i<count($fields);$i++){
                $sql_fields .=  $fields[$i].",";
                $sql_values .=  ":".$fields[$i].",";
            }
 
            $sql_fields = substr($sql_fields,0,strlen($sql_fields)-1);
            $sql_values = substr($sql_values,0,strlen($sql_values)-1);
            $sql_values = substr($sql_values,1,strlen($sql_values));
            $sql_params = explode(',',$sql_values);
            $params =array_combine($sql_params,$values);
            $sql = $sql." ".$sql_fields.") VALUES (".$sql_values.");";
            $sth = $this->db->prepare($sql) ;
            $sth->execute($params);
            $last_insert_id = $this->db->lastInsertId();
            return $last_insert_id;
            
        }
        
    }
    
    public function executeQuery($query){
        try{
            $pass = false;
            $query = trim($query);
            if($query === NULL || empty($query)){
                return true;
            }
            if(!$pass){
                $affected_rows = $this->db->exec($query);
            }
            
        }catch (PDOException $e){
            //echo "Caught Exception:".$e->getMessage();
        }
     return $affected_rows;
    }
    
    public function buildMultiInsertSQL($table, $coloumnnames, $rowvals){
        
        $data = "";
        
        for($i=0; $i<count($rowvals); $i++){
                
            $actual_rowvals = array_values($rowvals[$i]);
            
            if(count($coloumnnames) != count($actual_rowvals)){
                $msg = true;
            }
            
            if(!$msg){
                $data   .= "(".implode(',',$actual_rowvals)."),";
            }
        }
        
        if($msg == true){
            throw new Exception("An array field count and column names does not match");
            exit;
        }
        
        $dbcolumnames = implode(',',$coloumnnames);
        $sql = "insert into $table ($dbcolumnames) values $data";
        $sql = substr($sql,0,-1);
        
        return $sql;
    }
    
}
?>
 
This is a model class of the product I'm devleoping

Code: Select all

 
<?php
class Client extends DB{
    
    public $client_id=0;
        
    public $short_code = '';
    
    public $email ='';
    
    public $phone = '';
    
    protected $dbconn = NULL;
    
    public function __construct($dbObj){
        $this->dbconn = $dbObj; 
    }
    
    
    public function addClient($fields, $values){
        $table = "clients"; 
        $client_id = $this->dbconn->executeInsertSQL($fields,$values,$table);
        return $client_id; 
    }
    
    public function getClients($client_id = ''){
        if(!empty($client_id)){
            $sql = "SELECT client_id,client_name, client_short_name,email,phone, web from clients where client_id = :client_id";
            $params = array(':client_id'=>$client_id);
        }else{
            $sql = "SELECT client_id,client_name, client_short_name,email,phone, web from clients ";
            $params = array();
        }
        $result = $this->dbconn->selectArrayAssoc($sql,$params);
        return $result;
    }
    
    public function getClientDetails($client_id =0){
        if($client_id !=0){
            $sql = "SELECT client_id, client_name, client_short_name, email,phone, web, client_description, city, state,fax FROM clients WHERE client_id = :client_id";
            $params = array(':client_id'=>$client_id);
            $result = $this->dbconn->selectArrayAssoc($sql,$params);
            return $result;
            
        }else{
            throw new Exception("Not a valid parameter");
            exit;
        }   
    }
 
    public function getClientShortName(){
        
    }
    
    public function updateClient($fields,$values,$condition){
        $table="clients";
        $sql = "UPDATE $table set";
        for($i=0; $i<count($fields);$i++){
            $sql .= " $fields[$i]= '$values[$i]',"; 
        }
        
        $sql =  substr($sql,0,strlen($sql)-1);
        
        $sql .= " $condition" ;
        
        $rows = $this->dbconn->executeQuery($sql);
        return $rows;
        
    }
    
    public function deleteClient($client_id){
        $sql = "DELETE FROM clients WHERE client_id = '$client_id'";
        $rows =$this->dbconn->executeQuery($sql);
        return $rows;
    }
    
    
}
?>
 
I tried to make db declaration a singleton pattern, but some how failed on that, due to time restraint, I'm somehow continuing in this fashion. Now I would like to write a helper pagination class. I do not want to write count SQL statements for every method. I would like to use the same model queries, use their rowset count, to get all the result, rather than running two queries (one normal sql, one count sql). I was thinking of using get_class , get_class_methods group of functions. but not sure how? Can somebody help me with some idea for how to continue from here. I uses typical mvc pattern of products like dotproject. Declare DB in the index.php

Re: Pagination Class

Posted: Thu Oct 01, 2009 2:52 am
by Darhazer
I do not want to write count SQL statements for every method. I would like to use the same model queries, use their rowset count, to get all the result, rather than running two queries (one normal sql, one count sql).
Bad idea... if you have 1000 clients you will fetch them all, just to display 10 of them and show that there are 100 pages... this would be performance killer.

You can however use SQL_CALC_FOUND_ROWS in your select, so you won't run the COUNT(*) query, but instead of this just call the FOUND_ROWS() function, so your count method will be the same for all classes.

Re: Pagination Class

Posted: Thu Oct 01, 2009 3:04 am
by VladSun
Darhazer wrote:You can however use SQL_CALC_FOUND_ROWS in your select, so you won't run the COUNT(*) query, but instead of this just call the FOUND_ROWS() function
http://www.mysqlperformanceblog.com/200 ... ound_rows/

Re: Pagination Class

Posted: Thu Oct 01, 2009 3:28 am
by josh
@Vlad, thats right but whats to say mysql won't rewrite the directive :twisted: just playing devil's advocate, there's performance but there's also forwards compatibility ( not that count will stop working, but may not be the fastest way in future versions ). It's also "unscientific", like if you had 1,000s of queries you wanted to count() on how would the benchmarks change?

Re: Pagination Class

Posted: Thu Oct 01, 2009 3:31 am
by dude81
Yes it looked like a bad idea to me. I chose to write Count queries for each method that would need pagination and sorting. Thanks for your inputs.

Re: Pagination Class

Posted: Thu Oct 01, 2009 5:09 am
by Jenk
If using mysql, just use "LIMIT 1 10" and increment/decrease as appropriate for pages. If you're worried about COUNT(*) then don't use *, just count the primary key column - a count which is constantly in cache on the DB anyway.

Re: Pagination Class

Posted: Thu Oct 01, 2009 5:26 am
by josh
josh wrote:@Vlad, thats right but whats to say mysql won't rewrite the directive :twisted: just playing devil's advocate, there's performance but there's also forwards compatibility ( not that count will stop working, but may not be the fastest way in future versions ). It's also "unscientific", like if you had 1,000s of queries you wanted to count() on how would the benchmarks change?
Someone PMd me and said they did not understand what I was trying to say,

I'm just saying in the link Vlad sent, they show count() is slower the sql_calc_rows,

but in those examples there was a minimal # of querying. If you compared using 1,000 sql_calc_rows queries vs running 1,000 selects + 1,000 count() selects; than the results may be different. And I was also just pointing out if you used sql_calc_rows, it might be slower today, but the mysql team may refactor their engine tomorrow making it the better option to choose. I was just playing devil's advocate, and pointing out that not only is sql_calc_rows "conceptually cleaner", it is the more "abstract" of the 2 options, meaning if you used sql_calc_rows you are more likely to be able to take advantage of performance optimizations in future releases of the dbms

Re: Pagination Class

Posted: Thu Oct 01, 2009 6:58 am
by VladSun
josh wrote:I'm just saying in the link Vlad sent, they show count() is slower the sql_calc_rows...
I thought it was faster ...
josh wrote:And I was also just pointing out if you used sql_calc_rows, it might be slower today, but the mysql team may refactor their engine tomorrow making it the better option to choose. I was just playing devil's advocate, and pointing out that not only is sql_calc_rows "conceptually cleaner", it is the more "abstract" of the 2 options, meaning if you used sql_calc_rows you are more likely to be able to take advantage of performance optimizations in future releases of the dbms
I think it's conceptually wrong (if used with the current state of DB engines, and from DB point of view). It's used exclusively with LIMIT clause. But ...
http://dev.mysql.com/doc/refman/5.0/en/ ... ation.html
As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you are using SQL_CALC_FOUND_ROWS.

Re: Pagination Class

Posted: Thu Oct 01, 2009 7:56 am
by josh
Sorry thats what i meant to say, freudian slip, yes right now the way the sql_calc_rows works is rather hackish, my real argument is just that this thing should be abstracted so the underlying strategies can change, either abstract the code creating the duplicate count( * ) query, or use the flag, that way you aren't hard-wiring the stuff, because the current solutions are hackish is my point.

For instance if you had a query object make methods for getting at the count separately from the actual rows, and hide the fact that internally it is actually doing:

Code: Select all

 
$count_query = clone $query;
$count_query->addSomeCriteria();
$count_query->execute()
 
Then you can play around with different strategies as there is no 1 best solution for anything, it changes over time as mysql & your project both evolve.. is all i am trying to convey

Re: Pagination Class

Posted: Thu Oct 01, 2009 8:21 am
by superdezign
I've tried making a pagination class twice and, both times, I found it easier to give more control to the user than to the class. The class ended up just returning the LIMIT clause, the number of pages, and the current page. Then, extensions of the class would format that into HTML lists. The list styles would be different based on the child class.

The user would have to send the total number of results as well as the current page being read. This allowed the total results to either be:
  • counted from the database every time,
  • saved into a session variable,
  • saved into a helper database table, or
  • anything else the user could think of to enhance readability or performance.
The current page being read could be sent in the form of:
  • a GET variable,
  • a POST variable,
  • a "SEO-friendly" (subjectively) URL, or
  • whatever other crazy idea the developers get.
I like flexibility in classes, and the pagination classes that I wrote were limited. They performed two operations:
  • building the pagination links, and
  • building a portion of the query (the LIMIT clause for MySQL).
But that's just my .02¢.

Re: Pagination Class

Posted: Thu Oct 01, 2009 8:47 am
by VladSun
josh wrote:For instance if you had a query object make methods for getting at the count separately from the actual rows, and hide the fact that internally it is actually doing:

Code: Select all

 
$count_query = clone $query;
$count_query->addSomeCriteria();
$count_query->execute()
 
Usually the data you select in the original query has little to do with the SELECT COUNT(...) query. Especially, when there are aggregate functions, CASE/IF clauses, LEFT JOINs (!!!), etc. So, I think that excluding this "redundant" operations in the SELECT COUNT(...) query is a must. It really depends on the structure of the query, but in general these two queries are different by structure. That's why I think such "clone-the-query" abstraction is almost useless.
I think, what you are trying to do is similar to:

[sql]SELECT count(__result.*) AS total_count FROM ([DATA_FETCH_QUERY_HERE]) AS __result[/sql]
which I think may work good in some cases but in general it will not.

Re: Pagination Class

Posted: Thu Oct 01, 2009 8:55 am
by Darhazer
VladSun wrote:
Darhazer wrote:You can however use SQL_CALC_FOUND_ROWS in your select, so you won't run the COUNT(*) query, but instead of this just call the FOUND_ROWS() function
http://www.mysqlperformanceblog.com/200 ... ound_rows/
Try this test on different engines. MyISAM is extremely fast for count(*), InnoDB is not.

P.S. I just suggested an option, I'm not using it anyway. But I bet it's much faster than running query without limit and then checking the number of the results.

Re: Pagination Class

Posted: Thu Oct 01, 2009 11:17 am
by VladSun
Darhazer wrote:Try this test on different engines. MyISAM is extremely fast for count(*), InnoDB is not.
Probably it's true. InnoDB is not so read-optimized as MyISAM is.
Darhazer wrote:But I bet it's much faster than running query without limit and then checking the number of the results.
I do agree :) Checking num_rows is the worst one can do for such purposes :)

Re: Pagination Class

Posted: Fri Oct 02, 2009 5:33 am
by josh
VladSun wrote:Usually the data you select in the original query has little to do with the SELECT COUNT(...) query. Especially, when there are aggregate functions, CASE/IF clauses, LEFT JOINs (!!!), etc.
Would "abstracting" the count via using SQL_CALC_FOUND_ROWS overcome such complicated queries? If so it would also save time on constructing 2x as many queries.... ( the humans time not the computers )

Re: Pagination Class

Posted: Fri Oct 02, 2009 7:04 am
by VladSun
josh wrote:
VladSun wrote:Usually the data you select in the original query has little to do with the SELECT COUNT(...) query. Especially, when there are aggregate functions, CASE/IF clauses, LEFT JOINs (!!!), etc.
Would "abstracting" the count via using SQL_CALC_FOUND_ROWS overcome such complicated queries? If so it would also save time on constructing 2x as many queries.... ( the humans time not the computers )
First, I think software developers must write high quality software (I mean "speed"), not "just-meet-the-deadlines-and-let-it-work-somehow" one (which people tend to do it via too much of abstraction and generalization). So, I can't agree with you ;)

So ... in both cases we have two separate queries (so at transport layer query time is almost the same).
Pagination - well, this leads to the major problem related to SQL_CALC_FOUND_ROWS - caching results and reuse them.
Take a look:
http://www.phpdevblog.net/2009/06/mysql ... query.html

(I haven't tried benchmarks from the link above, but I do believe they are true)