Example of SELECT query with bind variables

Question

I'm trying to bind input parameters into my SELECT query and then fetch the resulting rows, but MySQLi seems to be different to other APIs I'm used to and I'm getting lost in the PHP manual.

Is the following approach correct?

$sql = 'SELECT product_id, product_name, area_id
    FROM product
    WHERE product_id = ?';
$stmt = $myMySQLi->prepare($sql);
if(!$stmt){
    throw new Exception('Prepare error');
}
if( !@$stmt->bind_param('i', $product_id) ){
    throw new Exception('Bind error');
}
if( !$stmt->execute() ){
    throw new Exception('Execute error');
}

If it's so, how do I fetch rows into associative arrays? If I'm overcomplicating it, how should I proceed?

score 3 · Answer 1 · answered Apr 29 '10 at 17:38

3

Using bind_result you can map results to variables:

$stmt->bind_result($product_id, $product_name, $area_id);
while ($stmt->fetch()) {
    echo $product_id . ": " . $product_name;
}

answered Apr 29 '10 at 17:38

racetrack

3,766
30
30

Oh my... That's so primitive... It's the first time I use MySQLi and I already hate it to the bones. Thanks. – Álvaro González Apr 30 '10 at 07:11

score 3 · Accepted Answer · edited May 23 '17 at 12:18

3

mysqli doesn't provide a way to fetch results into an array. If you want this functionality you have two options:

extend mysqli and write a fetchAll method
use pdo from now on

hint: use pdo

just trying to make your life easier.

read this

edited May 23 '17 at 12:18

Community

1
1

answered Apr 29 '10 at 17:58

Galen

29,976
9
71
89

I didn't mean all rows at a time, just row by row, like MySQLi_Result::fetch_assoc(). Thanks for the link: I couldn't find a way to obtain a MySQLi_Result from a MySQLi_STMT and it seems that...you cannot! Thank God the project is at an early stage and I can still get rid of MySQLi. – Álvaro González Apr 30 '10 at 07:10
if you can go with pdo i highly recommend it. – Galen Apr 30 '10 at 14:51
I've discarded MySQLi. I can't delay this project any longer so I'll stick to my good old custom class built on top of regular MySQL functions. On next project I'll give PDO a try-out. – Álvaro González May 04 '10 at 08:29

Example of SELECT query with bind variables

2 Answers2