Skip to main content
ElizabethGailLittle
ElizabethGailLittle
Inspiring
October 20, 2020
Answered

Database INSERT using PDO

  • October 20, 2020
  • 2 replies
  • 486 views

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

    This topic has been closed for replies.
    Correct answer osgood_

    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 (`).

    2 replies

    ElizabethGailLittle
    ElizabethGailLittleAuthor
    Inspiring
    October 21, 2020

    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.

    O
    osgood_
    Legend
    October 21, 2020

    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
    ElizabethGailLittleAuthor
    Inspiring
    October 21, 2020
    Yes - thanks!
    O
    osgood_
    Legend
    October 20, 2020

    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
    ElizabethGailLittleAuthor
    Inspiring
    October 20, 2020

    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

    O
    osgood_Correct answer
    Legend
    October 20, 2020

    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 (`).

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices