1

I have been trying to get a simple insert to work and i cant see why it isnt working. The table has 1 row and im able to get the data using a select but when i insert nothing happens. There are no php errors thrown but there is a pdo error thrown but i still cant see where the problem is. Here is the code

$Src = "asd";
$Title = "imgasd";
$Table = "asd";
$TableRow = 123;
$External = 0;
$Type = "image";
$Thumb = "asd";
$Summery = "asd";

$stmt = $db->prepare("INSERT INTO Media (Title, Summery, Src, Table, TableRow, External, Type, Thumb) VALUES (:Title, :Summery, :Src, :Table, :TableRow, :External, :Type, :Thumb)");
$stmt->execute(array(':Title'  => $Title, ':Summery'  => $Summery, ':Src'  => $Src, ':Table'  => $Table, ':TableRow'  => $TableRow, ':External'  => $External, ':Type'  => $Type, ':Thumb'  => $Thumb)) or die(print_r($stmt->errorInfo(), true));

The code above throws no php errors but returns the following in the browser. I dont know if ive made an incredibly stupid mistake or what but i cant get this to work

Array ( [0] => 42000 [1] => 1064 [2] => 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 'Table, TableRow, External, Type, Thumb) VALUES ('imgasd', 'asd', 'asd', 'asd', '' at line 1 )
Dan Hastings
  • 3,241
  • 7
  • 34
  • 71

1 Answers1

4

Table is a reserved word in MySQL. Best practices would tell you not to use these as column names, however you can if you wrap them in back ticks. From the manual:

An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it. (Exception: A reserved word that follows a period in a qualified name must be an identifier, so it need not be quoted.)

This should work:

$stmt = $db->prepare("INSERT INTO Media (`Title`, `Summery`, `Src`, `Table`, `TableRow`, `External`, `Type`, `Thumb`) VALUES (:Title, :Summery, :Src, :Table, :TableRow, :External, :Type, :Thumb)");
scrowler
  • 24,273
  • 9
  • 60
  • 92
  • Side note: "Summery" is spelled incorrectly, but it's consistent everywhere so it won't cause you any technical problems – scrowler Jun 26 '14 at 23:51
  • That fixed it! i had a feeling it might have been src that was a reserved word but i never checked table. Ive spent a good 2 hours at this so thanks for helping out! – Dan Hastings Jun 26 '14 at 23:57
  • No worries - I suppose putting back ticks around table and column names would be good practice at all times.. – scrowler Jun 27 '14 at 00:03