Check correct UPDATE in MySql with PHP-PDO

Question

Check correct UPDATE in MySql with PHP-PDO

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

When launching a INSERT MySql with PDO I have the same result whether the update is done or not, as long as no errors occur, ie if for example the update does not meet the conditions of WHERE will not occur.

Example:

$sql = "UPDATE score SET bonus=1000 WHERE player='Anubis'";

$query = $con->prepare( $sql );

$res = ( $query )
    ? $query->execute()
    : NULL ;

var_dump( $res );

The above, whether or not the player named Anubis is found, throws the same result.

There is a direct way to check that the INSERT was fine on the $ res variable, or the only way is to run the update and then launch one with rowCount () :

$sql = "UPDATE score SET bonus=1000 WHERE player='Anubis'";

$query = $con->prepare( $sql );
$query->execute();

$res = $query->rowCount();

var_dump( $res );

I understand that doing it this way, two calls are being made to the BD instead of just one, the first for the update and the second to check the number of affected rows.

mysql php pdo

asked by Orici 30.03.2017 в 00:48

source

2 answers

error when reading a variable in PHP Notice: Undefined property: stdClass :: $ Name IDENTITY_INSERT error in SQL

score 1 · Answer 1

The above, whether or not the player named Anubis is found, throws the same result.

This is because PDOStatement::execute % true returns if the sentence is correctly written and false if you have any errors, syntax, for example.

If you want to get the number of rows affected by the judgment (either INSERT , UPDATE O DELETE ) it is possible to use exec() , which returns an integer with the number of rows affected. If there are no rows, then it returns 0.

$update = "UPDATE score SET bonus = 1000 where player = "Anubis";

$filas = $db->exec($update);
// $filas = int(1) -- si existe el jugador Anubis
// $filas = int(0) -- si no existe el jugador Anubis

The disadvantage of the exec() method is that it can not be used with PDO::prepare() .

On the other hand, analyzing records MySQL could see that PDO::rowCount does not make a new query to the database.

Example using exec() :

SET timestamp=1490834071;
update tabla set nombre = 'Cuchufleto' where id = 2;
User@Host: root[db] @ localhost [::1]
Thread_id: 13  Schema: angulara  QC_hit: No
Query_time: 0.000003  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
Rows_affected: 0

Example using execute() and then rowCount() :

SET timestamp=1490834148;
update tabla set nombre = 'Cuchufletito' where id = 2;
User@Host: root[db] @ localhost [::1]
Thread_id: 14  Schema: angulara  QC_hit: No
Query_time: 0.000004  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
Rows_affected: 0
SET timestamp=1490834148;

score 0 · Answer 2

Validation will depend on whether you have set the attribute in your connection: PDO::ERRMODE_EXCEPTION or not.

A. If you have established this attribute when connecting,

If you have done something similar to this when you connect:

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

It means that if something goes wrong an exception will be raised. In that case the validation will be done within a block try/catch :

try 
{
  $query->execute(...);
  echo 'Consulta exitosa';

} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}

B. If the error mode is not activated

The execute() method returns TRUE in case of success or FALSE in case of error.

Therefore, to validate:

if ($query->execute(...)) {
   echo 'Consulta exitosa';

} else {
   echo 'Un error ha ocurrido';

}