Using Pdo Prepared Statement and Incrementing a Column Value

Using PDO Prepared Statement and Incrementing a column value

$sql = 'UPDATE user_alerts SET notif = notif + 2 WHERE ( user_id = :userid )';
$prepStatement = $pdo->prepare( $sql );
$prepStatement->execute(array(':userid' => $userid));

You can't bind a column name to a prepared statement.

Increment value in a MySql query using PDO

Step 1: Sanitize your input. Never concatenate a query with $_GET['something'], because I can delete your whole database like that.

Step 2: Handle errors properly.

Step 3: prepare() doesn't execute the query. It returns a Prepared Statement. http://php.net/manual/en/pdo.prepare.php You still need to execute it.

$stmt = $bdd->prepare("UPDATE `articulos` SET `telVisto` = `telVisto` + 1 WHERE `ID` =:id ");
$stmt->execute(array(':id' => $_GET['id']));

Step 4. If nothing works, can you check if you even have a row to update? Does the query

SELECT * FROM `articulos` WHERE `ID` = (insert your ID here manually)

return anything? Maybe the problem is that you don't work with the correct data?

Issues incrementing a field in MySQL/PHP with prepared statements

You can't parameterize column names with PDO. What you can do is have hard-coded values (which you basically already have) and construct the SQL string accordingly. I would check this value in the actual function too though, just to be on the safe side:

function db_OP_doVote($pdo, $postid, $votetype)
{
    if( !in_array( $votetype, array( 'v-cool', 'v-meh', 'v-shit' /*, etc. */ ), true ) )
    {
        throw new InvalidArgumentException( 'Unexpected $votetype: ' . $votetype );
        // or simply return false perhaps
    }

    $sql = '
        UPDATE content_posts
        SET `' . $votetype . '` = `' . $votetype . '` + 1
        WHERE `id` = :id
    ';

    $prepStatement = $pdo->prepare( $sql );

    $prepStatement->execute(array(':id' => $postid));

    echo "Success";
}

However, this strategy suggests your database design could use a little more attention. The way you have it now, is that for every type of vote, you have a column. This is not really efficient and/or flexible database design. What happens if you get asked to add another type of vote?

I'd suggest adding another table, to be more flexible:

CREATE TABLE `content_post_vote` (
  `content_post_id` int(11) NOT NULL,
  `vote_type` enum('cool','meh','shit') NOT NULL, # using enum() to assure valid vote types 
  `votes` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`content_post_id`,`vote_type`)
)

Then your query would be something like:

$sql = '
    INSERT INTO `content_post_vote` (`content_post_id`,`vote_type`,`votes`)
    VALUES( :id, :votetype, 1 )
    ON DUPLICATE KEY UPDATE `votes` = `votes` + 1
';

What this does is insert a vote if there is no record for a certain primary key (content_post_id,vote_type) yet, and else update the record with a vote if the record already exists.

Then to query the database for how many votes of a particular type a particular content_post has gotten, you do this:

 $sql = '
     SELECT `votes` # or perhaps more columns
     FROM `content_post_vote`
     WHERE `content_post_id` = :id AND 
           `vote_type` = :votetype
 ';

How can I increment a column in mysql using prepared statements?

 $query = "UPDATE forums_topics SET posts=posts+? WHERE id=?"

MySQL PDO Name-Value Prepared Statement Using Last Parameter Only

Thanks for all the help everybody!

I went with Michael's solution, but tested Ryan's too.

i.e.

Update to note as solved. Using...

$stmt->execute($params); // scrap the foreach nonsense...

bindValue() rather than bindParam() is also appropriate.

To wrap things up, as per Ryan's comment, I'm pushing an answer out.

Thanks again!

Using PDO Prepared Statements With MySQL Query Variable

You can't use query parameters to insert expressions to your syntax. Parameters are not just string-interpolation. If they were, there would be no benefit to using them, because you can do string-interpolation easily in PHP already.

The whole point of query parameters is that the value is combined with the query on the server, after the SQL syntax has been parsed, so it's too late for you to insert any new syntax, like an expression.

Query parameters are always treated as a single scalar value. You can't use a parameter for:

• Table identifiers
• Column identifiers
• SQL keywords
• Expressions
• Lists of values

As others have explained, in this case, you have no need to use a query parameter anyway. Using the literal expression log + 1 directly in your query is safe. There's no untrusted content (from users or other sources) being inserted into the query, so there's no risk of SQL injection.

updating row by adding +1 using pdo

Two questions, two answers.

First, as @Fred points out, remove the comma after hits = hits + 1 and the UPDATE statement will work.

Second,

in the above code I bind the value sitename, do I need to do the same for hits?

No. You use bind variables to pass data between the SQL code and the client code (in this case, your php code.) Updating the value of hits is handled entirely within the SQL and is not passed in or out.

With PDO you can also use named bind variables, which would look like this:

$query = $con->prepare( "UPDATE sitename SET hits = hits + 1
                           WHERE sitename = :url" );

and then either

$query->bindParam(':url', $url);
$query->execute();

or simply

$query->execute(array(':url' => $url));

The above code has no error handling, as that was not the issue I was attempting to address. Error handling is important, but in this case I leave it as an exercise for the reader.

---

Related Topics