How do I prevent MySQL from updating column with an empty string using PDO

Question

I have the exact question as How do I prevent MySQL from updating column with an empty string

However, the only twist is that I am applying this to PDO instead of the conventional MySQL.

I have followed the top answer on that question regarding adding IF statements to check if the length is 0. This does not seem to work for PDO however since no update occurs into the table and the webpage outputs: Array ( [0] => HY093 [1] => [2] => ) 1. Note I am using prepared statements via the bind function.

My PHP code with the IF statement included:

    <?php
$pdo=new PDO("mysql:dbname=createyo_TestDatabase;host=localhost","createyo_james","password");

$statement=$pdo->prepare(
"UPDATE `Users` 
SET `EditIDs` = IF(LENGTH(':EditID')=0, EditIDs, ':EditID'), 
`ArticleIDs` = IF(LENGTH(':ArticleID')=0, ArticleIDs, ':ArticleID'),
`Reputation` = IF(LENGTH(':Reputation')=0, Reputation, ':Reputation'),
`VotedUpArticleIDs` = IF(LENGTH(':VotedUpArticleID')=0, VotedUpArticleIDs, ':VotedUpArticleID'),
`VotedUpEditIDs` = IF(LENGTH(':VotedUpEditID')=0, VotedUpEditIDs, ':VotedUpEditID'),
`VotedDownArticleIDs` = IF(LENGTH(':VotedDownArticleID')=0, VotedDownArticleIDs, ':VotedDownArticleID'),
`VotedDownEditIDs` = IF(LENGTH(':VotedDownEditID')=0, VotedDownEditIDs, ':VotedDownEditID'),
WHERE `UserID` = :UserID");

$statement->bindValue(':EditID', (string) trim($_GET['EditID']), PDO::PARAM_STR);
$statement->bindValue(':ArticleID', (string) trim($_GET['ArticleID']), PDO::PARAM_STR);
$statement->bindValue(':Reputation', (string) trim($_GET['Reputation']), PDO::PARAM_STR);
$statement->bindValue(':VotedUpArticleID', (string) trim($_GET['VotedUpArticleID']), PDO::PARAM_STR);
$statement->bindValue(':VotedUpEditID', (string) trim($_GET['VotedUpEditID']), PDO::PARAM_STR);
$statement->bindValue(':VotedDownArticleID', (string) trim($_GET['VotedDownArticleID']), PDO::PARAM_STR);
$statement->bindValue(':VotedDownEditID', (string) trim($_GET['VotedDownEditID']), PDO::PARAM_STR);

    $statement->bindValue(':UserID', (string) trim($_GET['UserID']), PDO::PARAM_STR);

    $statement->execute() or die(print_r($statement->errorInfo()));
$results=$statement->fetchAll(PDO::FETCH_ASSOC);
$json=json_encode($results);
print $json;
?>

Edit

I am now receiving a syntax error: 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 'WHERE UserID = '1234'' at line 9. I have not been able to understand this error.

New PHP code:

$statement=$pdo->prepare(
"UPDATE `Users` 
SET `EditIDs` = IF(LENGTH(:EditID)=0, EditIDs, :EditID1), 
`ArticleIDs` = IF(LENGTH(:ArticleID)=0, ArticleIDs, :ArticleID1),
`Reputation` = IF(LENGTH(:Reputation)=0, Reputation, :Reputation1),
`VotedUpArticleIDs` = IF(LENGTH(:VotedUpArticleID)=0, VotedUpArticleIDs, :VotedUpArticleID1),
`VotedUpEditIDs` = IF(LENGTH(:VotedUpEditID)=0, VotedUpEditIDs, :VotedUpEditID1),
`VotedDownArticleIDs` = IF(LENGTH(:VotedDownArticleID)=0, VotedDownArticleIDs, :VotedDownArticleID1),
`VotedDownEditIDs` = IF(LENGTH(:VotedDownEditID)=0, VotedDownEditIDs, :VotedDownEditID1),
WHERE `UserID` = :UserID");
$statement->bindValue(':EditID', (string) trim($_GET['EditID']), PDO::PARAM_STR);
$statement->bindValue(':ArticleID', (string) trim($_GET['ArticleID']), PDO::PARAM_STR);
    $statement->bindValue(':Reputation', (string) trim($_GET['Reputation']), PDO::PARAM_STR);
$statement->bindValue(':VotedUpArticleID', (string) trim($_GET['VotedUpArticleID']), PDO::PARAM_STR);
$statement->bindValue(':VotedUpEditID', (string) trim($_GET['VotedUpEditID']), PDO::PARAM_STR);
$statement->bindValue(':VotedDownArticleID', (string) trim($_GET['VotedDownArticleID']), PDO::PARAM_STR);
$statement->bindValue(':VotedDownEditID', (string) trim($_GET['VotedDownEditID']), PDO::PARAM_STR);

$statement->bindValue(':EditID1', (string) trim($_GET['EditID']), PDO::PARAM_STR);
$statement->bindValue(':ArticleID1', (string) trim($_GET['ArticleID']), PDO::PARAM_STR);
$statement->bindValue(':Reputation1', (string) trim($_GET['Reputation']), PDO::PARAM_STR);
$statement->bindValue(':VotedUpArticleID1', (string) trim($_GET['VotedUpArticleID']), PDO::PARAM_STR);
$statement->bindValue(':VotedUpEditID1', (string) trim($_GET['VotedUpEditID']), PDO::PARAM_STR);
$statement->bindValue(':VotedDownArticleID1', (string) trim($_GET['VotedDownArticleID']), PDO::PARAM_STR);
$statement->bindValue(':VotedDownEditID1', (string) trim($_GET['VotedDownEditID']), PDO::PARAM_STR);

possible duplicate of Error HY093 with a MySQL Insert PDO Request — cdhowie
– cdhowie, Commented Aug 22, 2014 at 23:17

Barmar · Accepted Answer · 2014-08-22 23:48:11Z

3

In the SQL, you should not put :placeholder inside quotes. That makes them into literal strings, so they won't get substituted from the bound parameters.

$statement=$pdo->prepare(
"UPDATE `Users` 
SET `EditIDs` = IF(LENGTH(:EditID)=0, EditIDs, :EditID), 
`ArticleIDs` = IF(LENGTH(:ArticleID)=0, ArticleIDs, :ArticleID),
`Reputation` = IF(LENGTH(:Reputation)=0, Reputation, :Reputation),
`VotedUpArticleIDs` = IF(LENGTH(:VotedUpArticleID)=0, VotedUpArticleIDs, :VotedUpArticleID),
`VotedUpEditIDs` = IF(LENGTH(:VotedUpEditID)=0, VotedUpEditIDs, :VotedUpEditID),
`VotedDownArticleIDs` = IF(LENGTH(:VotedDownArticleID)=0, VotedDownArticleIDs, :VotedDownArticleID),
`VotedDownEditIDs` = IF(LENGTH(:VotedDownEditID)=0, VotedDownEditIDs, :VotedDownEditID),
WHERE `UserID` = :UserID");

However, you're also not allowed to use the same :placeholder multiple times in the same query. So you'll need to give the repeated placeholders different names.

$statement=$pdo->prepare(
"UPDATE `Users` 
SET `EditIDs` = IF(LENGTH(:EditID)=0, EditIDs, :EditID1), 
`ArticleIDs` = IF(LENGTH(:ArticleID)=0, ArticleIDs, :ArticleID1),
`Reputation` = IF(LENGTH(:Reputation)=0, Reputation, :Reputation1),
`VotedUpArticleIDs` = IF(LENGTH(:VotedUpArticleID)=0, VotedUpArticleIDs, :VotedUpArticleID1),
`VotedUpEditIDs` = IF(LENGTH(:VotedUpEditID)=0, VotedUpEditIDs, :VotedUpEditID1),
`VotedDownArticleIDs` = IF(LENGTH(:VotedDownArticleID)=0, VotedDownArticleIDs, :VotedDownArticleID1),
`VotedDownEditIDs` = IF(LENGTH(:VotedDownEditID)=0, VotedDownEditIDs, :VotedDownEditID1)
WHERE `UserID` = :UserID");

Then you'll have to bind all the additional parameters:

$statement->bindValue(':EditID1', (string) trim($_GET['EditID']), PDO::PARAM_STR);
$statement->bindValue(':ArticleID1', (string) trim($_GET['ArticleID']), PDO::PARAM_STR);
$statement->bindValue(':Reputation', (string) trim($_GET['Reputation']), PDO::PARAM_STR);
$statement->bindValue(':VotedUpArticleID1', (string) trim($_GET['VotedUpArticleID']), PDO::PARAM_STR);
$statement->bindValue(':VotedUpEditID1', (string) trim($_GET['VotedUpEditID']), PDO::PARAM_STR);
$statement->bindValue(':VotedDownArticleID1', (string) trim($_GET['VotedDownArticleID']), PDO::PARAM_STR);
$statement->bindValue(':VotedDownEditID1', (string) trim($_GET['VotedDownEditID']), PDO::PARAM_STR);

edited Aug 22, 2014 at 23:48

answered Aug 22, 2014 at 23:26

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jamesgates1 Over a year ago

Thanks, this seems to be what I was looking for. However, I am now receiving a syntax error for the WHERE part of the query. I have updated the question.

Barmar Over a year ago

You have an extra comma at the end of the VotedDownEditIDs line.

jamesgates1 Over a year ago

Thanks :) I should really use a proper PHP IDE instead of the one the web hosts give me.

Axel Amthor · Accepted Answer · 2014-08-22 23:19:28Z

0

Have your own trim function like

  function nulltrim($str)
  {
      $s = trim($str);
      if ( empty($s) ) 
            return null;
      return $s;
  }

and

  $statement->bindValue(':EditID', (string) nulltrim($_GET['EditID']), PDO::PARAM_STR);

should solve it.

answered Aug 22, 2014 at 23:19

Axel Amthor

11.1k1 gold badge28 silver badges45 bronze badges

2 Comments

jamesgates1 Over a year ago

Thanks for the input. Does this mean to say the IF statement I used does not work for PDO or is this just a method you find more suitable?

Axel Amthor Over a year ago

you're updating the column with it's current value. If that's already an empty string, this will stay and not converted to null obviously.

Collectives™ on Stack Overflow

How do I prevent MySQL from updating column with an empty string using PDO

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related