如何在fatfree框架中循环mysql结果集？

Question

I am new to the php fat-free framework, and I am trying figure out how to loop through my mysql query results, or better yet, get it as an associative array (for learning purposes only).

What I did so far is

while(!$users->dry()){
   array_push($user_assoc,$users->cast());
   $users->next();
}

This works, but I was wondering if there is a better way of doing this? Also how do I setup a error handler? I mean how do I check if the query had any errors (i.e. fat-free equivalent of mysql_error())?

Answer 1

DB querying

There are 3 variants to loop through db results:

Without mapper:

Execute a SQL query and fetch the result set as an array of associative arrays:

$users = $db->exec('SELECT * FROM users');
foreach($users as $user)
  echo $user['name'];//associative array

With mapper->load:

Fetch mapper rows one by one (your method):

$user=new \DB\SQL\Mapper($db,'users');
$user->load('');
while(!$user->dry()) {
  echo $user->name;//db mapper
  $user->next();
}

With mapper->find:

Fetch the result set as an array of mappers:

$mapper=new \DB\SQL\Mapper($db,'users');
$users=$mapper->find('');
foreach($users as $user)
  echo $user->name;//db mapper

DB error handling

\DB\SQL is a subclass of PDO so it can throw catchable PDO exceptions. Since these are disabled by default, you need to enable them first. This can be done in 2 different ways:

• at instantiation time, for all transactions:

  $db = new \DB\SQL($dsn, $user, $pwd, array( \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION ));

• later on in the code, on a per-transaction basis:

  $db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

Once PDO exceptions are enabled, just catch them as other exceptions:

try {
  $db->exec('INSERT INTO mytable(id) VALUES(?)','duplicate_id');
} catch(\PDOException $e) {
  $err=$e->errorInfo;
  //$err[0] contains the error code (23000)
  //$err[2] contains the driver specific error message (PRIMARY KEY must be unique)
}

This also works with DB mappers, since they rely on the same DB\SQL class:

$db=new \DB\SQL($dsn,$user,$pwd,array(\PDO::ATTR_ERRMODE=>\PDO::ERRMODE_EXCEPTION));
$mytable=new \DB\SQL\Mapper($db,'mytable');
try {
  $mytable->id='duplicate_id';
  $mytable->save();//this will throw an exception
} catch(\PDOException $e) {
  $err=$e->errorInfo;
  echo $err[2];//PRIMARY KEY must be unique
}