INSERT into DB table with preparation of PDO and bindParam

Question

In simple terms, someone can explain what I'm doing wrong here - I'm just trying to insert into db using prepare and bindParam, this inserts 0 and Null into all fields.

$sql = $db->prepare("INSERT INTO db_fruit VALUES (id=? ,type=? ,colour=?)"); $sql->bindParam(1, $newId); $sql->bindParam(2, $type); $sql->bindParam(3, $colour); $sql->execute() 

btw: this method worked for me for UPDATE, etc., but not in this case for INSERT

Answer 1

Your syntax is incorrect, try the following:

 $sql = $db->prepare("INSERT INTO db_fruit (id, type, colour) VALUES (? ,? ,?)"); $sql->bindParam(1, $newId); $sql->bindParam(2, $name); $sql->bindParam(3, $colour); $sql->execute(); 

Answer 2

Expanding on the response of the AO, the following rules also apply:

 $sql = $db->prepare("INSERT INTO db_fruit (id, type, colour) VALUES (? ,? ,?)"); $sql->execute(array($newId, $name, $color)); 

and

 $sql = $db->prepare("INSERT INTO db_fruit (id, type, colour) VALUES (:id, :name, :color)"); $sql->execute(array('id' => $newId, 'name' => $name, 'color' => $color)); 

It might be a personal preference, but I find this syntax a lot cleaner.

Answer 3

 $sql = $db->prepare("INSERT INTO db_fruit (`id`, `type`, `colour`) VALUES (:id, :name, :colour)"); $sql->bindParam(':id', $newId, PDO::PARAM_INT); $sql->bindParam(':type', $type, PDO::PARAM_INT); $sql->bindParam(':colour', $colour, PDO::PARAM_STR); $sql->execute();