fetchAll helper function using PDO

Question

Suppose I have a function

function fetchAll(){
  $args = func_get_args();
  $query = array_shift($args);
  $query = str_replace("%s","'%s'",$query);
  foreach ($args as $key => $val) {
    $args[$key] = mysql_real_escape_string($val);
  }
  $query = vsprintf($query, $args);
  if (!$query) return FALSE;

  $res = mysql_query($query);
  if (!$res) {
    trigger_error("db: ".mysql_error()." in ".$query);
    return FALSE;
  }
  $a = array();
  while($row = mysql_fetch_assoc($res)) $a[]=$row;
  return $a;
}

and then use it like this

$a=$db->fetchAll("SELECT * FROM users WHERE status=%s LIMIT %d,%d",$status,$start,$num);

How can I rewrite it using PDO?
Every example I can find shows only how to bind parameters directly. Should I pass variable type as well as it's value? Or make this call always 4 lines - 3 binds and execute?

Wrikken · Accepted Answer · 2010-09-16T14:22:17.907

1

edit: as the Colonel indicated, apparently this (no longer?) works with LIMIT clauses.

If you're using simple queries / are not that bothered with type:

function fetchAll(){
   $args = func_get_args();
   $query = array_shift($args);//'SELECT * FROM users WHERE status=? LIMIT ?,?'
   //you'll need a reference to your PDO instance $pdo somewhere....
   $stmt = $pdo->prepare($query);
   $stmt->execute($args);
   return $stmt->fetchAll();
}

edited Sep 16 '10 at 14:22

answered Sep 16 '10 at 10:58

Wrikken

69,272
8
97
136

Well I am bothered with type, at least to distinguish ints from strings. But it seems doesn't matter with PDO. nothing bad if i pass a string for the last 2 placeholders - right? – Your Common Sense Sep 16 '10 at 11:11
Well it doesn't work, throwing an error, `near ''0','2''`. Looks like default string type doesn't work for LIMIT clause – Your Common Sense Sep 16 '10 at 12:35
Hmm, you're right. I seem to remember that about 2 years ago I could (_but haven't since forgone the type_), but at this time, `LIMIT` indeed errors on it with the 'default' string. – Wrikken Sep 16 '10 at 14:20
1

As stereofrog have just pointed out in my other question, its prepared statements emulation option gets responsible for such behavior. by adding `$dbh->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );` this error can be eliminated. – Your Common Sense Sep 16 '10 at 15:54

score 0 · Answer 2 · edited Sep 16 '10 at 13:48

0

Every example I can find shows only how to bind parameters directly. Should I pass variable type as well as it's value? Or make this call always 4 lines - 3 binds and execute?

You don't have to fire binds one line at a time; you can bind with an array like this:

# the data we want to insert  
$data = array('Cathy', '9 Dark and Twisty Road', 'Cardiff');  

$STH = $DBH->("INSERT INTO folks (name, addr, city) values (?, ?, ?)");  
$STH->execute($data);

edited Sep 16 '10 at 13:48

Dan Soap

10,114
1
40
49

answered Sep 16 '10 at 11:02

Erik

20,526
8
45
76

I was concerned for LIMIT clause. Seems it's impossible to omit type setting in this case... – Your Common Sense Sep 16 '10 at 12:36

fetchAll helper function using PDO

2 Answers2

Linked