Database INSERT using PDO

Participant ,
Oct 20, 2020 Oct 20, 2020

Copy link to clipboard

Copied

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

Views

168

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

LEGEND , Oct 20, 2020 Oct 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 (`).

Likes

Translate

Translate
LEGEND ,
Oct 20, 2020 Oct 20, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 20, 2020 Oct 20, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 20, 2020 Oct 20, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 20, 2020 Oct 20, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 21, 2020 Oct 21, 2020

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 21, 2020 Oct 21, 2020

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 21, 2020 Oct 21, 2020

Copy link to clipboard

Copied

LATEST
Yes - thanks!

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines