CODEWITHSUNDEEP
phpmysql
Christopher Cheok
Christopher Cheok

Reputation: 61

Mysql +1 to variable

how should i update my attempt's using variable ? it wont work

mycode

$db_attempts = 'MAX_ATTEMPTS';

//here
$attemtps_pdo = 'UPDATE `attempts` SET `MAX_ATTEMPTS`= ? +1 WHERE `IP` = ?';
$results = $ALIST->update($attemtps_pdo,$db_attempts,$user_ip);

public function update($sql,$values1,$values2){
        try{
        $results = $this->connection->prepare($sql);
        $results->bindValue(1, $values1);
        $results->bindValue(2, $values2);
        $results->execute();

        return $results;
}

how do i make my MAX_ATTEMPTS +1 to variable , if i do it with my code , the update only update once , once is == 1 it wont update anymore why?

but if i using

$attemtps_pdo = 'UPDATE `attempts` SET `MAX_ATTEMPTS`= `MAX_ATTEMPTS` +1 WHERE `IP` = ?';

it work perfectly.

Upvotes: 0

Views: 66

Answers (3)

Thibault Henry
Thibault Henry

Reputation: 841

Just try the incrementation :

UPDATE attempts SET MAX_ATTEMPTS++ WHERE IP = ?

Upvotes: 0

Conrad Lotz
Conrad Lotz

Reputation: 8838

Alternative solutions:

Assuming it will always update by 1 every time the sql gets executed and assuming $value1 is the value currently in the database for MAX_ATTEMPTS. What I would suggest is to it when you bind the parameters:

$results->bindValue(2, (int)$values1 + 1);

Your sql will be:

$attemtps_pdo = 'UPDATE `attempts` SET `MAX_ATTEMPTS`= ? WHERE `IP` = ?';

OR

Add a database query to find the latest value of MAX_ATTEMPTS and pass it as $value1 which realizes the assumption made in the previous solution.

Upvotes: 0

EJTH
EJTH

Reputation: 2219

Because you are not referencing the MAX_ATTEMPTS column when you BIND the variable $db_attempts into your SQL query. Binding prevents stuff like this, because it could potentially lead to SQL injection.

In other words, your second example IS the correct way of doing this. If you want this to be dynamic (eg. if $db_attempts can change), then you have to build the query using string concatenation.

Upvotes: 1

Related Questions