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

gavin stanley
  • 1,065
  • 2
  • 12
  • 27

3 Answers3

18

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
  • 27,459
  • 7
  • 46
  • 75
  • 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
10

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,719
  • 6
  • 29
  • 49
1
$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
  • 542
  • 2
  • 7
  • 24
  • 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