Using a variable as parameter in mysql

Question

I have written a method to get a value out of a database based on an id.

I would like to use the variable id as a parameter in mysql but I can't get it to work.

Here is my code:

function get_color_by_id($id) {
        $mysqli = new mysqli("localhost", "root", "usbw", "ipadshop", 3307);

        if($mysqli->connect_errno){
            die("Connection error: " . $mysqli->connect_error);
        }

        set @id := $id;
        $result = $mysqli->query('SELECT kleur FROM kleur WHERE id=',@id);
        if(!$result){
            die('Query error: ' . $mysqli->error);
        }

        while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
            return $row;
        }
    }

`set @id := $id;` is not valid PHP. – John Conde Jun 19 '14 at 20:49 — John Conde, Jun 19 '14 at 20:49

score 0 · Accepted Answer · edited May 23 '17 at 11:57

PHP is not SQL; and SQL code should not be expected to work in a PHP context. The procedural SQLish syntax (set @id := $id) is thus invalid in context, as is the @id expression used later.

As shown here (and updated for this question), the correct way to use parameters in mysqli is with prepare, bind_param, and execute.

$stmt = $mysqli->prepare('SELECT kleur FROM kleur WHERE id = ?');
$stmt->bind_param('i', $id);  // assuming $id represents an integer
$result = $stmt->execute();

Since bind_param uses reference calling semantics, the variables used should not be re-assigned once bound to avoid potential confusion.

Also, it can be confusing to give the same names to columns and tables; my preference is to use plural names for tables/relations, and to refine the column names more. Consider a query with different names chosen;

SELECT kleurNaam FROM kleuren WHERE id = ?
-- or in English
SELECT colorName FROM colors WHERE id = ?

Easier to read, no?

Thank you for your solution. I will it in mind to give diffrent names to columns in the future. — GleDel, Jun 19 '14 at 21:37

Using a variable as parameter in mysql

1 Answers1