How to use both fetch() and fetchAll() in a PDO wrapper class?

Question

How can I alter my PDO wrapper class, so that if I expect a single row result with my query it uses fetch() and if it expects multiple results it uses fetchAll().

Right now, if I have only one result I still have to loop through the result array and that seem pretty unpracticable to me.

Query in the model:

public function doccEdit() {

    $id = mysql_real_escape_string($_GET['id']);

    $this->result = $GLOBALS['db']->select("creditcards", "id = ?", $id);

    print_r($this->result);

}

In the wrapper class:

public function run($sql, $bind="") {
    $this->sql = trim($sql);
    $this->bind = $this->cleanup($bind);
    $this->error = "";

    try {
        $pdostmt = $this->prepare($this->sql);
        if($pdostmt->execute($this->bind) !== false) {
            if(preg_match("/^(" . implode("|", array("select", "describe", "pragma")) . ") /i", $this->sql))
                return $pdostmt->fetchall(PDO::FETCH_OBJ);
            elseif(preg_match("/^(" . implode("|", array("delete", "insert", "update")) . ") /i", $this->sql))
                return $pdostmt->rowCount();
        }   
    } catch (PDOException $e) {
        $this->error = $e->getMessage();    
        $this->debug();
        return false;
    }
}

Why don't you just always fetchall()? If there's only one row, you'll get one... — Ben, Feb 23 '13 at 14:09
Because I thought I could avoid the loop. With fetchall I have to use a loop even when there is only one result. — Stefan Schneider, Feb 23 '13 at 14:15
what does mysql_real_escape_string to do here? do you format `"creditcards"` properly? — Your Common Sense, Feb 23 '13 at 14:32

Your Common Sense · Answer 1 · 2013-02-23T14:43:45.297

DON'T TRY to automate everything

The less magic in your code, the easier support and less painful troubles.
Don't try to stuff all the logic into one single method. It's a class! You can create as many methods as you need.

When you need rowCount() - select it explicitly! It's not that hard.
But when you stumble upon this code after couple months, you will know what does this value mean.

When you need single row - use a method to get a single row. When you need many rows - use a method to get many rows.
It is simple and extremely unambiguous!

When you turn back to your code after 2 months, you will have absolutely no idea, what did you expected. So - always write it explicitly.

Here is an excerpt from my mysqli wrapper class to give you an idea:

public function query()
{   
    return $this->rawQuery($this->prepareQuery(func_get_args()));
}
/**
 * Helper function to get scalar value right out of query and optional arguments
 * 
 * Examples:
 * $name = $db->getOne("SELECT name FROM table WHERE id=1");
 * $name = $db->getOne("SELECT name FROM table WHERE id=?i", $id);
 *
 * @param string $query - an SQL query with placeholders
 * @param mixed  $arg,... unlimited number of arguments to match placeholders in the query
 * @return string|FALSE either first column of the first row of resultset or FALSE if none found
 */
public function getOne()
{
    $query = $this->prepareQuery(func_get_args());
    if ($res = $this->rawQuery($query))
    {
        $row = $this->fetch($res);
        if (is_array($row)) {
            return reset($row);
        }
        $this->free($res);
    }
    return FALSE;
}

/**
 * Helper function to get single row right out of query and optional arguments
 * 
 * Examples:
 * $data = $db->getRow("SELECT * FROM table WHERE id=1");
 * $data = $db->getOne("SELECT * FROM table WHERE id=?i", $id);
 *
 * @param string $query - an SQL query with placeholders
 * @param mixed  $arg,... unlimited number of arguments to match placeholders in the query
 * @return array|FALSE either associative array contains first row of resultset or FALSE if none found
 */
public function getRow()
{
    $query = $this->prepareQuery(func_get_args());
    if ($res = $this->rawQuery($query)) {
        $ret = $this->fetch($res);
        $this->free($res);
        return $ret;
    }
    return FALSE;
}

/**
 * Helper function to get single column right out of query and optional arguments
 * 
 * Examples:
 * $ids = $db->getCol("SELECT id FROM table WHERE cat=1");
 * $ids = $db->getCol("SELECT id FROM tags WHERE tagname = ?s", $tag);
 *
 * @param string $query - an SQL query with placeholders
 * @param mixed  $arg,... unlimited number of arguments to match placeholders in the query
 * @return array|FALSE either enumerated array of first fields of all rows of resultset or FALSE if none found
 */
public function getCol()
{
    $ret   = array();
    $query = $this->prepareQuery(func_get_args());
    if ( $res = $this->rawQuery($query) )
    {
        while($row = $this->fetch($res))
        {
            $ret[] = reset($row);
        }
        $this->free($res);
    }
    return $ret;
}

/**
 * Helper function to get all the rows of resultset right out of query and optional arguments
 * 
 * Examples:
 * $data = $db->getAll("SELECT * FROM table");
 * $data = $db->getAll("SELECT * FROM table LIMIT ?i,?i", $start, $rows);
 *
 * @param string $query - an SQL query with placeholders
 * @param mixed  $arg,... unlimited number of arguments to match placeholders in the query
 * @return array enumerated 2d array contains the resultset. Empty if no rows found. 
 */
public function getAll()
{
    $ret   = array();
    $query = $this->prepareQuery(func_get_args());
    if ( $res = $this->rawQuery($query) )
    {
        while($row = $this->fetch($res))
        {
            $ret[] = $row;
        }
        $this->free($res);
    }
    return $ret;
}

Look - from the function name you can always tell which result to expect:

$name = $db->getOne('SELECT name FROM table WHERE id = ?i',$_GET['id']);
$data = $db->getAll("SELECT * FROM ?n WHERE mod=?s LIMIT ?i",$table,$mod,$limit);

Don't be fooled by such a pitfall like number of returned rows.
There could be honest one row in the resultset which you intend to populate with fetchAll. So, it will return single-dimensional array instead of multi-dimensional and you will have plenty of video effects on your page

score -1 · Answer 2 · answered Sep 06 '13 at 17:26

Since you didn't mark an answer as accepted. I thought I'd answer you question. I found this while looking for the answer myself. I agree with "Your Common Sense" in that they should be two separate functions. However, in direct answer to your question, this is what I had (PDO example rather than mysqli):

function select($sql,$params=NULL,$fetchType=NULL){
    try{
        $qry = $this->db->prepare($sql);
        $qry->execute($params);
        if($qry->rowCount() > 1){
            if($fetchType == 'OBJ'){//returns object
                $results = $qry->fetchAll(PDO::FETCH_OBJ);
            }elseif($fetchType == 'NUM'){//-numerical array
                $results = $qry->fetchAll(PDO::FETCH_NUM);
            }else{//default - associative array
                $results = $qry->fetchAll(PDO::FETCH_ASSOC);
            }
        }
        else{
            if($fetchType == 'OBJ'){//returns object
                $results = $qry->fetch(PDO::FETCH_OBJ);
            }elseif($fetchType == 'NUM'){//-numerical array
                $results = $qry->fetch(PDO::FETCH_NUM);
            }else{//default - associative array
                $results = $qry->fetch(PDO::FETCH_ASSOC);
            }
        }

        if($results){
            return $results;
        }else{
            return NULL;
        }
    }
    catch(PDOException $err){
        $this->logError($err);
    }
}

However I found that if I queried all rows in a table but there was only one row in the table it would return a 1-d array instead of a 2-d array. My code to handle the result wouldn't work for both types of arrays. I could handle that each time but I found it just easier to, as stated above, separate them into different functions so if I knew there would be only one answer I could call the appropriate function. This is what I have now:

function select($sql,$params=NULL,$fetchType=NULL){
    try{
        $qry = $this->db->prepare($sql);
        $qry->execute($params);

        if($fetchType == 'OBJ'){//returns object
            $results = $qry->fetch(PDO::FETCH_OBJ);
        }elseif($fetchType == 'NUM'){//-numerical array
            $results = $qry->fetch(PDO::FETCH_NUM);
        }else{//default - associative array
            $results = $qry->fetch(PDO::FETCH_ASSOC);
        }

        if($results){
            return $results;
        }else{
            return NULL;
        }
    }
    catch(PDOException $err){
        $this->logError($err);
    }
}

function selectAll($sql,$params=NULL,$fetchType=NULL){
    try{
        $qry = $this->db->prepare($sql);
        $qry->execute($params);

        if($fetchType == 'OBJ'){//returns object
            $results = $qry->fetchAll(PDO::FETCH_OBJ);
        }elseif($fetchType == 'NUM'){//-numerical array
            $results = $qry->fetchAll(PDO::FETCH_NUM);
        }else{//default - associative array
            $results = $qry->fetchAll(PDO::FETCH_ASSOC);
        }

        if($results){
            return $results;
        }else{
            return NULL;
        }
    }
    catch(PDOException $err){
        $this->logError($err);
    }
}

I just can't stand so much repeated code. You definitely need to learn how to use functions. — Your Common Sense, Sep 06 '13 at 19:40
Is there a way to save the name of a function in a variable? (swap "fetch" with "fetchAll")? — Joao, Oct 03 '13 at 14:08

How to use both fetch() and fetchAll() in a PDO wrapper class?

2 Answers2