PHP Mysqli fetch_assoc

Question

The MySQLi feature of PHP is great.

Prepared Statements are great for injecting parameters in a query.

However, if you use Statements, it seems you dont have access to the fetch_assoc feature anymore. You have to manually bind all your results. This mean my SQL query has to list all the fields, then I have to list all the variables in advance when getting the results (using bind_result). This can become very annoying and time-wasting.

Is there an alternative?

Thanks

Answer 1

Yes, it's called PDO and is generally preferred to MySQLi. It offers statements with the regular fetch options, and doesn't force you to explicitly bind all parameters. A typical query might look like this:

$stmt = $db->prepare("SELECT * FROM foo WHERE a = ?");
$stmt->execute(array($a));

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

PDO is very flexible, so it can be used in other ways as well. There's a lot of great examples in the manual. Take a look!

Answer 2

MySQLi does provide methods for dealing with this, have a look at result_metadata function.

I've written a class to allow hassle-free parameterised MySQLi queries - a download and basic usage info is available here: http://www.robpoyntz.com/blog/?p=191