7

In simple terms can someone explain what I am doing wrong here - I am simply trying to insert into a db with with prepare and bindParam, this is inserting 0 and Null into all the 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 has been working for me for UPDATE etc, but not in this case for INSERT

Dharman
  • 30,962
  • 25
  • 85
  • 135
gavin stanley
  • 1,082
  • 2
  • 13
  • 28

3 Answers3

20

Expanding on A.O's answer, the following are also valid:

$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));

Might just be personal preference, but I find this syntax to be much cleaner.

Sammitch
  • 30,782
  • 7
  • 50
  • 77
  • I have been led to believe (could be wrong, very green) that bindParam is secure and replaces my_real_escape_string. Is your method as secure? – gavin stanley Oct 25 '13 at 21:59
  • @gavin yes. PDO binds the parameters just the same as if you were to call `bindParam()` separately for each. – Sammitch Oct 25 '13 at 22:01
11

Your syntax is incorrect, try this: 

$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();
A.O.
  • 3,733
  • 6
  • 30
  • 49
2
$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();
Tadeusz Majkowski
  • 612
  • 2
  • 8
  • 26
  • You don't have to put the array inside `execute` because you already have the values inside `bindParam` – laviku Apr 06 '16 at 15:05