[SOLVED] mysql transactions in php

[gevans]

I started chewing on my hand about 30 minutes ago when I was getting annoyed at this, it's getting worse....

 

Basically I'm forcing an error in my sql to test the ROLLBACK. When I test the actual function it says it's rolling back successfully but it's not, snippet of code...

 

<?php
###lost of omitted code that is fine
        $commit = FALSE;
        $DB->transaction('START');
        $guid = guid();
        $userLevel = (int)($position === 'LAN') ? 1 : 2;
        $query = "INSERT INTO users(password,fnm,snm,email,user_level,join_date,guid,active)
            VALUES('$pass','$fnme','$snme','$email',$userLevel,NOW(),'$guid',0)";
        $q1 = $DB->query($query);
        $id = mysql_insert_id();
        if($userLevel === 2) {
            $query = "INSERT INTO gmc(user_id, gmc, authorised)
                VALUES($id,$gmc,1000)";
            $q2 = $DB->query($query);
            if($q1 && $q2) {
                $commit = $DB->transaction('COMMIT');
            } else {
                $DB->transaction('ROLLBACK')? die('rollback'):die('nope ');
            }
        } elseif($q1) {
            $commit = $DB->transaction('COMMIT');
        } else {
            $DB->transaction('ROLLBACK');
        }
        if(!$commit) {
            $ERR->setError(6);
            $ERR->checkError();
        } else {
        
        }
###more omitted code that's fine
?>

 

Due to the fact that I'm forcing an error by inserting 1000 into a BIT(1) datatype this code 'dies' about half way down letting me know if the rollback happened or not. The following is a snippet of my db class that deals with transactions;

 

<?php
    public function transaction($task){
        switch($task) {
            case "START":
                return mysql_query("START TRANSACTION")? TRUE : FALSE;
                break;
            case "ROLLBACK":
                return mysql_query("ROLLBACK")? TRUE : FALSE;
                break;
            case "COMMIT":
                return mysql_query("COMMIT")? TRUE : FALSE;
                break;
            default:
                return FALSE;
                break;
        }
    }
?>

 

If anyone can see what I'm messing up here I'd be very happy!! I'm going to keep working on it

[gevans]

AN UPDATE,

 

Another hour and I've sorted it....

Simple but rarely documented clause of using transaction in mysql, the storage engine must be InnoDB. Funnily enough my main rig's version of mysql uses this as standard, but my laptop, running xampp lite has mysql with a default storage engine of MyISAM. This is where the database originated before I brought it back to my main pc.

 

Not going to forget this to soon....

[gevans]

And... here's where I found taht info out...

 

 

CLICKY