Database INSERT using PDO

[ElizabethGailLittle]

I've been fighting with an error for days now & can't figure it out.

Here is my code.  The first 2 lines attempt to see if the data itself was a problem.

if (isset($_POST['insert']))
{

echo "POST'['content'] ".$_POST['content']."<br>";
echo "POST['swapcontent']".$_POST['swapcontent']."<br>";
$insertSQL = "INSERT INTO Sidebar_content
(s_key, page, s_date, seq, s_text, content,
swapcontent, thumb, s_width, Show) VALUES (:s_key, :page, :s_date, :seq, :s_text, :content, :swapcontent, :thumb, :s_width, :Show)";
try {
$stmt = $sainttim->prepare($insertSQL);

// bind parameters, execute statement
$stmt->bindParam(':s_key', $zero, PDO::PARAM_STR);
$stmt->bindParam(':page', $_POST['page'], PDO::PARAM_STR);
$stmt->bindParam(':s_date', $_POST['s_date'], PDO::PARAM_STR);
$stmt->bindParam(':seq', $_POST['seq'], PDO::PARAM_INT);
$stmt->bindParam(':s_text', $_POST['s_text'], PDO::PARAM_STR);
$stmt->bindParam(':content', $_POST['content'], PDO::PARAM_STR);
$stmt->bindParam(':swapcontent', $_POST['swapcontent'], PDO::PARAM_STR);
$stmt->bindParam(':thumb', $_POST['thumb'],PDO::PARAM_STR);
$stmt->bindParam(':s_width', $_POST['s_width'],PDO::PARAM_INT);
$stmt->bindParam(':Show', $_POST['Show'], PDO::PARAM_STR);
} catch (Exception $e) {
echo 'Caught exception: 1 ', $e->getMessage(), "\n";
}
// execute & get # affected rows
try {
$stmt->execute();
} catch (Exception $e) {
echo 'Caught exception: 2 ', $e->getMessage(), "\n";
}
$OK = $stmt->rowCount();
// redirect if successful or display error
if ($OK)
{
header('Location: http://sainttims.com/DisplaySidebarContent.php');
exit;
} else
{
$error = $stmt->errorInfo();
echo "Error handler<br>";
if (isset($error[2])) die($error[2]);
}
} // if page submitted

RESULT:

POST'['content'] images/photos/DOK2.jpg
POST['swapcontent']
Caught exception: 2 SQLSTATE[42000]: Syntax error or access violation: 1064 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 'Show) VALUES ('0', 'Home', '2020-10-18', '0', 'DOK Meeting', 'images/photos/DOK2' at line 3 Error handler
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 'Show) VALUES ('0', 'Home', '2020-10-18', '0', 'DOK Meeting', 'images/photos/DOK2' at line 3

Any help will be appreciated!

Thanks,

Gail Little

[osgood_]

I don't use PDO BUT have you tried using 'bindValue' instead of 'bindParam'?

So instead of this workflow:

$stmt->bindParam(':page', $_POST['page'], PDO::PARAM_STR);

This workflow:

$stmt->bindValue(':page', $_POST['page'], PDO::PARAM_STR);

[ElizabethGailLittle]

I just did.  Result was identical.  I have another page which inserts data into a different table using exactly the same code but different variables.  When I change data input above, the output near 'Show) etc is aways exactly 80 characters.  Does this offer any clue?  For example, if I enter ...DOK.jpg instead of DOK2.jpg, the error message has DOK. instead of DOK2.

Thanks for the response!

Gail

[osgood_ · Accepted solution]

I've just tested 'Show' in my sql admin and it says its a reserved word. A long shot, but try renaming all instances of 'Show', to some other name.

That maybe is the problem:

https://doc.ispirer.com/sqlways/Output/SQLWays-1-205.html

There are reserved words in MySQL which cannot be used as identifiers (table or column names etc.) without being quoted with backticks (`).

[ElizabethGailLittle]

I FINALLY discovered that "Show" is a reserved word for SQL.  Changed the table structure to rename the element, and problem solved!. G_R_R_R_R!

Gail

[ElizabethGailLittle]

Thanks!  I had tried to look up reserved words & had trouble finding a list.  Finally did yesterday.  How do you test in SQL admin?  I'm going to be careful to give ALL table variables names like s_show from now on, and have changed several during this process.

[osgood_]

I'm using phpMyAdmin. If I designate a 'reserved' word to a table column it give me a warning. It still let's you use the word but warns you, so in that case, personally I would choose an alternative name for the column.

I get your point in using a naming convention like 's_show' and if that works for you then that's great, I find it a bit messy when working directly with the code. However if you apply it to ALL your database columns I think it should be alright rather than your original naming convention where some columns where prefaced with 's_' and others werent, which makes the code slightly harder to read.

[ElizabethGailLittle]

Yes - thanks!