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?

Answer 1

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;
}

Answer 2

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

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

hint: use pdo

just trying to make your life easier.

read this