executing a prepared statement with an array of insert values doesn't work

Question

I'm trying to de a SELECT in php on my database with a prepared statement, using an array of insert values. At the moment I work with stored procedures and my code looks likt this:

$user_id = $user["id_user_key"];
$is_active = 1;

$stmt = $db->prepare("CALL spGetUserProducts(?, ?)");
$stmt->bind_param("ii", $user_id, $is_active);
$stmt->execute();
$result = $stmt->get_result();

This works fine but at the moment I would like to get rid of my stored procedures and do all the SQL in php

I changed the above code to this:

$user_id = $user["id_user_key"];
$is_active = 1;

$stmt = $db->prepare(
           "SELECT
                tp.id_product AS id,
                tp.product_name AS pname,
                tp.product_code AS pcode,
                tp.product_icon AS picon
            FROM
                tbl_user_products tup
            INNER JOIN
                tbl_products tp
                ON tp.id_product = tup.id_product_fk
            WHERE
                tup.active = :isActive
                AND tup.id_user_fk = :getUser"
);
$stmt->execute(array(
    ":getUser" => $user_id,
    ":isActive" => $is_active)
);
$result = $stmt->get_result();

When I run this script I get the following error:

Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':isActive AND tup.id_user_fk = :getUser' at line 12

To be certain, I also did run my SQL script without the binding of the params, like so:

$user_id = $user["id_user_key"];
$is_active = 1;

$stmt = $db->prepare(
          "SELECT
                tp.id_product AS id,
                tp.product_name AS pname,
                tp.product_code AS pcode,
                tp.product_icon AS picon
            FROM
                tbl_user_products tup
            INNER JOIN
                tbl_products tp
                ON tp.id_product = tup.id_product_fk
            WHERE
                tup.active = 1
                AND tup.id_user_fk = 1"
);
$stmt->execute();
$result = $stmt->get_result();

And this also works. It seems like the binding of :getUser and :isActive doesn't seem to work although I followed this example from php.net:

/* Execute a prepared statement by passing an array of insert values */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->execute(array(':calories' => $calories, ':colour' => $colour));

I'm running things on XAMPP 3.2.2.

Any thoughts on this?

Answer 1

You appear to be using the mysqli_ PHP code library to connect to your database. MySQLi does not support named parameters. You didn't say exactly where you got your example from but I would guess it was from a page relating to the PDO library. They are different libraries with different (although similar-looking, if you don't check closely enough) function names and capabilities.

There's nothing stopping you from continuing to use the supported syntax however. You just need to switch the order you supply the parameters in order to match the order they appear in the SQL. (Or alternatively of course you could switch the order of the WHERE clauses in the SQL, but here I have switched the order of the parameter values.)

$stmt = $db->prepare(
           "SELECT
                tp.id_product AS id,
                tp.product_name AS pname,
                tp.product_code AS pcode,
                tp.product_icon AS picon
            FROM
                tbl_user_products tup
            INNER JOIN
                tbl_products tp
                ON tp.id_product = tup.id_product_fk
            WHERE
                tup.active = ?
                AND tup.id_user_fk = ?"
);

$stmt->bind_param("ii", $is_active, $user_id);
$stmt->execute();