Transaction in PHP with MySQL?

Question

I create a PHP that execute multiples query. I wanna make a transaction, because i need to save all the query if everything works ok, if not make a rollback. Is critical save all the data. I don't know how to make the transaction. I've been following that:PHP + MySQL transactions examples but it's not working.

This is my php:

<?php 

require_once __DIR__ . '/connect_to_database.php';

$db = new DB_CONNECT();

$response = array();

$horaSeleccionada= $_POST['horaSeleccionada'];
$fechaElegida= $_POST['fechaElegida'];
$data1FK= $_POST['data1FK'];
$reservado= $_POST['reservado'];
$usuarioFK= $_POST['usuarioFK'];
$nombreAuxiliar= $_POST['nombreAuxiliar'];
$fechaDeReserva= $_POST['fechaDeReserva'];
$telefonoAux= $_POST['telefonoAux'];
$anioHoy = (int) $_POST['anioHoy']; 
$filasAfectadas = 0;
$consultasRealizadas = 0;

$fecha = date_create($fechaElegida);
$anioAuxiliar = (int) $fecha->format("Y");

while($anioHoy == $anioAuxiliar){
    $fechaAGuardar = (String) $fecha->format('Y-m-d');

    $query_search = "insert into table(hora, data1FK, usuarioFK, reservado, fecha, nombreAuxiliar, fechaDeReserva, telefonoAux) values ('$horaSeleccionada','$data1FK','$usuarioFK','$reservado','$fechaAGuardar','$nombreAuxiliar','$fechaDeReserva','$telefonoAux')";

    $result = mysql_query($query_search) or die(mysql_error());

    if(mysql_affected_rows()>0){
        $filasAfectadas++;
    }
    else{
        $response["success"] = 0;
        $response["mensaje"] = "Hubo un error";
        break;
    }

    $consultasRealizadas++;

    date_add($fecha, date_interval_create_from_date_string('7 days'));
    $anioAuxiliar = (int) $fecha->format("Y");
}

if($consultasRealizadas == $filasAfectadas && $consultasRealizadas!=0){
    $response["success"] = 1;
    $response["mensaje"] = "Todo ok";
}
else { 
    $response["success"] = 0;
    $response["mensaje"] = "Hubo un error";
}
echo json_encode($response);
?>  

My database is in the same server, with phpmyadmin. Thanks for helping me!

Answer 1

I'm going to get shat on for this answer but there is no reason for you to use transactional queries but before I can explain why, here are a few things I want to point out.

Firstly,

mysql has been deprecated since PHP 5.5. The newer and more kickass version of which, is mysqli. That said, there's no more reason to use mysql since mysqli is everything and more of mysql.

EDIT: What @Jay mentioned, this is not enough. You need to surround the variable in single quotes. Even better if you concatenate it too... Furthermore, if you know what sort of values you should be getting in a form, like username being alphanumeric, you can use preg_replace to ensure that only alphanumeric makes it to the code.

Secondly,

After implementing point #1, try escaping your post variables. Something like mysqli_real_escape_string works wonders in preventing any exceptions from popping up when you are running your insert query. In addition, the most important point of using mysqli_real_escape_string is that it prevents database injections entirely, therefore you should start using it immediately.

Thirdly,

Try not to use procedural and OOP together, it makes for a very messy code. Choose one, stick to it and if you want to change from one to another, do so with the whole page of code.

Lastly,

The reason why transactional queries like the one you linked exists, is for projects and codes that executes multiple queries, of which, you don't. In addition, if you do your string escaping well, all the queries will execute perfectly. The only time when you need transactional queries is when you are running mass inserts, updates or deletes where if one thing fu*ks up, it could potentially crash the whole system.

Your system doesn't seem to require that.