CODEWITHSUNDEEP
phppdo
Achshar
Achshar

Reputation: 5243

PDO: Using fetchAll on unknown query

I have a php class that I use to run SQL statements via PDO. The class stores the FetchAll's data to that query in a public variable But the problem is that I don't know what the query will be so I end up calling FetchAll on data manipulation queries (INSERT, DELETE, UPDATE)

how can I know if a particular query is fetchable or not? I don't want to use hacks like checking if the query starts from INSERT/DELETE/UPDATE.

class mysql {
    public $call, $rows;
    public function query($a) {
        $this->call = $pdo->prepare($a['query']);
        foreach($a['params'] as $key => $param) {$this->call->bindValue($key + 1, $param);}
        $this->rows = $this->call->fetchAll(PDO::FETCH_ASSOC);
    }
}

This throws error if I run a manipulation query.

Edit: complete class

class mysql {
    public $call, $rows;

    // allows query on construct time
    function __construct($a = false) {if($a) $this->query($a);}
    public function query($a) {
        $this->call = $pdo->prepare($a['query']);

        // execute the query with or without parameters, and if it succeeds and dontLog is not set and the query has data manipulation then call the log function to log the query along with user id
        if($this->call->execute(isset($a['params']) ? $a['params'] : null) && !isset($a['dontLog']) && in_array(substr($a['query'], 0, 6), array('INSERT','UPDATE','DELETE'))) $this->log(isset($a['params']) ? json_encode($a['params']) : '');

        // if the call returns any columns then store it in rows public variable or store an empty array
        $this->rows = ($this->call->columnCount() > 0) ? $this->call->fetchAll(PDO::FETCH_ASSOC) : array();
    }
    private function log($params) {
        new mysql(array('query' => 'INSERT INTO logs (user, query, parameters) VALUES (?, ?, ?)', 'params' => array($GLOBALS['user']['id'], $this->call->queryString, $params), 'dontLog' => true));
    }
}

Upvotes: 2

Views: 2155

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157872

It's a class!

Why have only single method as tough it's just an ordinary function?
Why return FetchAll all the time? there can be scalar returned which going to be very handy. Or a single row?
Why not to have separate methods for different results?

  • fetchall for the rows
  • fetchrow for the row
  • fetchone for the scalar
  • query for the everything else

it would be extremely handy and readable

Also, you have to change this strange code

foreach($a['params'] as $key => $param) {$this->call->bindValue($key + 1, $param);}

to this one

$this->call->execute($a['params']);

as your current code plainly unworkable.

Or, to make it really handy

public function fetchAll($a)
{
    $params = func_get_args();
    $query = array_shift($args);
    $this->call = $pdo->prepare($query);
    $this->call->execute($params);
    return $this->call->fetchAll();
}

to be called like this:

$rows = $db->fetchAll("SELECT * FROM t WHERE cat=?",$catagory);

neat, eh?

one more - you have to return the result, not store it in a class variable. Your class don't need these rows but calling code does.

Upvotes: 1

sectus
sectus

Reputation: 15464

You can try to use PDOStatement::columnCount

Upvotes: 5

Related Questions