PDO Value Increment PHP, Mysql

Question 1

Hello guys im working on a project for handling spots on a event. I'm trying to update the values of the event spots after someone registers to it. There is no problem with someone registering or reading the values from database but i cant update the spots. Hope you can help..

Here is the not working part of the code :

$free_spots_new = $free_spots - 1 ; // i haven't written the code up there, $free_spots is the value of the free_spots value in the database. And this is the process after someone registers to this event ...
$full_spots_new = $full_spots + 1 ; // same in here

try{
$update_event_query = "UPDATE `events` SET `free_spots` = :free_spots, `full_spots`= :full_spots WHERE `event_id`=:event_id";
$update_event_query_do = $db->prepare($update_event_query);
$update_event_query_do -> bindParam(':free_spots', $free_spots_new, PDO::PARAM_INT);
$update_event_query_do -> bindParam(':full_spots', $full_spots_new, PDO::PARAM_INT);
$update_event_query_do ->execute() or die(print_r($update_event_query_do->errorInfo(), true));
}

catch(PDOException $e) {
$log->logError($e." - ".basename(__FILE__));
}

Also is it possible to update the values within the UPDATE line without defining a new variable like $free_spots_new exc.

Question 2

Beside the missing '

You can execute the alterations directly in the query:

UPDATE `events`
SET    `free_spots` = `free_spots`-1,
       `full_spots` = `full_spots`+1
WHERE  `event_id`   = :event_id

Your also need to bind the :event_id placeholder $update_event_query_do->bindParam(':event_id', $event_id, PDO::PARAM_INT);

Question 3

The following works for me:

<?php

$pdo = new PDO("mysql:host=localhost;dbname=test", "root", "pass");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$stmt = $pdo->prepare("INSERT INTO `null_test` (`can_be_null`) VALUES (:null)");
$stmt->bindValue(":null", null, PDO::PARAM_NULL);

$stmt->execute();

Pass in PHP's null, with type of PDO::PARAM_NULL. Also, make sure your prepare emulation is set to false. That might help.

Question 4

Your first question:

Specifically, can I include the $result = $stmt3->fetchAll(); before the commit(), and then execute the conditional query?

I see no reason why it should not work. A transaction behaves basically the same as operations without transactions - except that changes are only drafts. Any changes you make in the previous statements will be applied to a "working copy" valid for this single session only. For you it will appear completely transparent. However any changes will be rolled back if you do not commit them.

Also worth noting (emphasis mine):

In layman's terms, any work carried out in a transaction, even if it is carried out in stages, is guaranteed to be applied to the database safely, and without interference from other connections, when it is committed.

This can cause racing conditions.

Your second question:

Also, I'm not entirely sure on this, but do I require the $db->rollBack(); within the try block, if the code is exited (return false) before the commit()?

From the documentation it says:

When the script ends or when a connection is about to be closed, if you have an outstanding transaction, PDO will automatically roll it back.

Therefore you do not necessarily require to roll back manually as it will be done by the driver itself.

However note the following from the same source as well:

Warning PDO only checks for transaction capabilities on driver level. If certain runtime conditions mean that transactions are unavailable, PDO::beginTransaction() will still return TRUE without error if the database server accepts the request to start a transaction.

So be sure to check the compatibility beforehand!

A few notes

Do NOT begin a transaction in another transaction. This will commit the first transaction implicitely. See this comment.

Another note from the documentation:

Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.

Question 5

I FIXED THE ERROR. The solution for anyone out there is simple, the PHP explode function was used to split the contents of a textarea into separate lines but it doesn't work if you use explode() with PHP_EOL. PHP EOL tells you the server's newline character from what I understand. I used the preg_split instead to perform the splitting and it works on both my localhost that runs on Windows and my server that runs on Linux. Thank you everyone for your help!!!

$conn = new PDO("mysql:host=$host;dbname=$db;charset=$charset", $user, $pass);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                if(isset($_POST["busnumber"], $_POST["busroute"])){
                    $stops = preg_split("/\r\n|\r|\n/", $_POST['busroute']);
                    $sql = 'SELECT * FROM stops WHERE stop_name LIKE :stop';
                    $statement = $conn->prepare($sql);
                    foreach($stops as $stop){
                        $statement->bindValue(':stop', $stop);
                        $statement->execute();
                        while($result = $statement->fetch()){
                            echo $result['stop_id'].' '.$result['stop_name'].'</br>';
                        }
                    }
                }

Question 6

See my notes inline with the code:

    try{  
         $req = $prepared_insertQry_toUniqueRefTable -> execute(array(something));

         // this never executes because an Exception halts it here
         /*if($req == 0){
               $prepared_insertQry_toDuplicateRefTable->execute(array(something));
         }*/
     }
    catch(PDOException $e){
       // this catch grabs the exception and executes the code within instead
       // that code might log an error, echo the error message, or perform
       // alternative logic. In your case you want to execute alterntative logic
       // ie. your query
       $prepared_insertQry_toDuplicateRefTable->execute(array(something));

   }

PDO Value Increment PHP, Mysql

Answers

Your first question:

Your second question:

A few notes

php,mysql,pdo,set