Good colleagues, I am learning PHP a little more thoroughly since I am a bit newbie, I already did database queries with MySQLi and I want to learn the PDO alternative since I can connect to other databases other than MySQL. So I decided to see different examples and I found one that I did not understand well so I decided to see line by line.
<?php
function doSomething($id){
$pdo= conecta();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$motivo= "probar";
$res=0;
$e="";
try{
$pdo->beginTransaction();
$sql= "INSERT INTO registro (nombre_c, desc_c, motivo_c) (SELECT nombre_a, desc_a, :motivo FROM autos WHERE id=:id)";
$stmt= $pdo->prepare($sql);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->bindParam(':motivo', $motivo,PDO::PARAM_STR);
$res1 = $stmt->execute();
$sql="DELETE FROM autos WHERE id=:id";
$stmt= $pdo->prepare($sql);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$res2 = $stmt->execute();
$pdo->commit();
$res=$res1&&$res2;
}catch(PDOException $e){
$pdo->rollBack();
}
unset($stmt);
unset($pdo);
return array($res?"actualizado":"error", $e);
}
?>
My idea is to understand it line by line so that what I learned is better for me. And from what I see a Method is created that receives the $ id property and connects to a database and an update is created (of which I do not know if the query is well done because it seems strange to me) I am having a hard time understanding PDO queries, so I wanted to see what tips or advice you could give me.
My query is to know and decipher what exactly does the function since it confuses me a lot
Advertisement
Answer
$pdo= conecta();
I suppose this is a successfully connected pdo object.
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
This means that pdo will throw an exception once it encounters an error. (As an alternative example you can set it to PDO::ERRMODE_WARNING, and it will produce a php warning on error)
$pdo->beginTransaction();
By this you tell PDO that you begin communicating with the database, after which you might want to accept the changes by $pdo->commit(); or decline them by $pdo->rollBack();
Thus, each $pdo->beginTransaction(); must be finished with either $pdo->commit(); or $pdo->rollBack();
$sql= "INSERT INTO registro (nombre_c, desc_c, motivo_c) (SELECT nombre_a, desc_a, :motivo FROM autos WHERE id=:id)"; $stmt= $pdo->prepare($sql);
At this step the string query is passed to PDO and prepared for execution (it’s necessary to prevent injections).
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->bindParam(':motivo', $motivo,PDO::PARAM_STR);
Binding params is like telling pdo to consider the indicated flags (beginning with : ) equal to the passed variables. The third parameter is the variable type here, often it can be omitted.
$res1 = $stmt->execute();
Executing the query itself. So at this step the previously prepared query is executed:
$sql= "INSERT INTO registro (nombre_c, desc_c, motivo_c) (SELECT nombre_a, desc_a, :motivo FROM autos WHERE id=:id)";
Namely, it selects fields nombre_a, desc_a, and probar from table autos and then inserts each selected line into table registro. But the changes are not displayed in the database yet. If an error occurs in the next DELETE query, this insert operation will be rolled back. This is because you started a transaction, which means you don’t send any changes to the database without telling PDO to commit().
But after inserting we haven’t committed yet. So if DELETE query produces an error, PDO will throw an exception, as it is set to the exception error mode. This exception will be caught by try-catch, and the whole transaction will be rolled back.
Otherwise, if DELETE is successful, there will be no exception, so the previously inserted data will be committed to the database, and the changes will be displayed.