Questions about MySQL Transations and how to use them in php

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

etherkye
Forum Newbie
Posts: 21
Joined: Fri Aug 07, 2009 3:40 am

Questions about MySQL Transations and how to use them in php

Post by etherkye »

What i have is a single script which processes data. One line will insert into one table, i then need to get it's alto ID number, and use it in the next query. However, it can only process the data if there are no errors at all.

So either i need to use transactions, and the documents i've found on google arn't much help, and it needs to not interfer with the other users accessing the database.

Or i need to run through all the data twice.

Any help as to how to solve this problem will be much appresiated.
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Questions about MySQL Transations and how to use them in php

Post by papa »

etherkye
Forum Newbie
Posts: 21
Joined: Fri Aug 07, 2009 3:40 am

Re: Questions about MySQL Transations and how to use them in php

Post by etherkye »

I only get an error when i try to click the link.

You tried to access the address http://us2.php.net/manual/en/function.m ... ert-id.php, which is currently unavailable. Please make sure that the Web address (URL) is correctly spelled and punctuated, then try reloading the page.
Make sure your Internet connection is active and check whether other applications that rely on the same connection are working.
marty pain
Forum Contributor
Posts: 105
Joined: Thu Jun 11, 2009 5:32 am
Location: Essex

Re: Questions about MySQL Transations and how to use them in php

Post by marty pain »

Works fine here.

Go to PHP.net and search for mysql_insert_id
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Questions about MySQL Transations and how to use them in php

Post by papa »

php.net

The function is called mysql_insert_id(). Might be what you are after.


cheers
etherkye
Forum Newbie
Posts: 21
Joined: Fri Aug 07, 2009 3:40 am

Re: Questions about MySQL Transations and how to use them in php

Post by etherkye »

Even with that, wouldn't i need to go through the document twice?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Questions about MySQL Transations and how to use them in php

Post by VladSun »

etherkye wrote:Even with that, wouldn't i need to go through the document twice?
I don't think so.
Post your code, please.
There are 10 types of people in this world, those who understand binary and those who don't
etherkye
Forum Newbie
Posts: 21
Joined: Fri Aug 07, 2009 3:40 am

Re: Questions about MySQL Transations and how to use them in php

Post by etherkye »

Code: Select all

<?php
include $_SERVER['DOCUMENT_ROOT'] . "/include.php";
 
head();
echo "<Body>
Processing Audit Trail.<br/>";
 
if ($_FILES['file']['type'] == "text/comma-separated-values")
{
    //generate Tax desc - Tax Code and Tax Value
    $TaxCode  = array();
    $TaxValue = array();
    $result = mysql_query("SELECT * FROM `bookkeeping`.`TaxCodes`
                           WHERE `CCODE` = '" . $_SESSION['CCODE'] . "'");
    $rows = mysql_num_rows($result);
 
    for($j=0;$j<$rows;$j++)
    {
        $row = mysql_fetch_array($result);
        $TaxCode["T".$row['TCode']]  = $row['Desc'];
    }
 
    //Generate Nominal Code lists
    $Nominal  = array();
    $result = mysql_query("SELECT * FROM `bookkeeping`.`sageCodes`
                           WHERE `CCODE` = '" . $_SESSION['CCODE'] . "'");
    $rows = mysql_num_rows($result);
 
    for($j=0;$j<$rows;$j++)
    {
        $row = mysql_fetch_array($result);
        $Nominal[$row['Code']]  = $row['Desc'];
    }
 
    //Generate Bank Accounts
    $Banks  = array();
    $result = mysql_query("SELECT * FROM `bookkeeping`.`BankAccounts`
                           WHERE `CCODE` = '" . $_SESSION['CCODE'] . "'");
    $rows = mysql_num_rows($result);
 
    for($j=0;$j<$rows;$j++)
    {
        $row = mysql_fetch_array($result);
        $Banks[$row['SAGE']]  = $row['ACC_NAME'];
    }
 
    $File = fopen($_FILES["file"]["tmp_name"], 'r');
 
    $query = "";
    $error = "";
 
    $lines      = 0;
    $errors     = 0;
    $ignored    = 0;
    $processed  = 0;
    
    while (!feof($File)) {
        $Error = false;
        $line = fgetcsv($File);
        $lines++;
        
        if(in_array($line[1],array("PI","BP","CP","SI","BR","CR")))
        {
            $Date   = date("Y-m-d",ukstrtotime(($line[2])));
            $Payee  = mysql_real_escape_string($line[3]);
            $Type   = $Nominal[$line[4]];
            $Dept   = mysql_real_escape_string($line[5]);
            $Ref    = mysql_real_escape_string($line[6]);
            $Desc   = mysql_real_escape_string($line[7]);
            $Amount = number_format(mysql_real_escape_string($line[8]),2,'.','');
            $Vat    = number_format(mysql_real_escape_string($line[9]),2,'.','');
            $Tax    = $TaxCode[$line[10]];
 
            if($Payee == ""){
                $error .= "Line: " . $lines . " Invalid Payee<br/>";
                $Error = true;
            }
            if($Type == ""){
                $error .= "Line: " . $lines . " Invalid Nominal Code<br/>";
                $Error = true;
            }
            if($Amount == 0){
                $error .= "Line: " . $lines . " Amount Cannot Be Zero<br/>";
                $Error = true;
            }
            if($Tax == ""){
                $error .= "Line: " . $lines . " Invalid Tax Code<br/>";
                $Error = true;
            }
            if($Error){
                $errors++;
                continue;
            }
            
            if($line[1]=="PI")//Purchse Invoice
            {
                $supplier = mysql_num_rows(mysql_query("SELECT * FROM `bookkeeping`.`Suppliers`
                                                      WHERE `CCODE` = '" . $_SESSION['CCODE'] . "'
                                                      AND   `Ref` = '$Payee'"));
                if($supplier>0){            
                    $query .= "INSERT INTO `bookkeeping`.`PurchaseInvoices` VALUES
                                    ('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'PI','$Date','$Payee','$Ref','1');<br>";
                    $query .= "INSERT INTO `bookkeeping`.`PurchaseRecord` VALUES
                                    ('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat');<br>";
                    $processed++;
                }
                else
                {
                    $error .= "Error on line " .$lines. ": Supplier ". $Payee." has not be referenced.<br>";
                    $errors++;
                }
            }
            else if($line[1]=="BP")//Purchase Invoice WO
            {
                $Payee =  $Banks[$Payee];
                $query .= "INSERT INTO `bookkeeping`.`PurchaseInvoices` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'BP','$Date','$Desc','$Ref','1');<br>";
                $query .= "INSERT INTO `bookkeeping`.`PurchaseRecord` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat');<br>";
                $query .= "INSERT INTO `bookkeeping`.`PurchasePaid` VALUES
                                ('".$_SESSION['CCODE']."','$Dept','$pID','','$Date','$Payee','','$Total','$Desc',0);<br>";
                $processed++;
            }
            else if($line[1]=="CP")//Petty Cash
            {
                $Payee =  $Banks[$Payee];
                $query .= "INSERT INTO `bookkeeping`.`PurchaseInvoices` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'CP','$Date','$Desc','$Ref','1');<br>";
                $query .= "INSERT INTO `bookkeeping`.`PurchaseRecord` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat');<br>";
                $query .= "INSERT INTO `bookkeeping`.`PurchasePaid` VALUES
                                ('".$_SESSION['CCODE']."','$Dept','$pID','','$Date','$Payee','','$Total','$Desc',0);<br>";
                $processed++;
            }
            if($line[1]=="SI")//Sales Invoice
            {
                $customer = mysql_num_rows(mysql_query("SELECT * FROM `bookkeeping`.`Customers`
                                                      WHERE `CCODE` = '" . $_SESSION['CCODE'] . "'
                                                      AND   `Ref` = '$Payee'"));
                if($customer>0){
                    $query .= "INSERT INTO `bookkeeping`.`SalesInvoices` VALUES
                                    ('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'SI','$Date','$Payee','$Ref','1');<br>";
                    $query .= "INSERT INTO `bookkeeping`.`SalesRecord` VALUES
                                    ('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat');<br>";
                    $processed++;
                }
                else
                {
                    $error .= "Error on line " .$lines. ": Customer ". $Payee." has not be referenced.<br>";
                    $errors++;
                }
            }
            else if($line[1]=="BR")//Sales Invoice WO
            {
                $Payee =  $Banks[$Payee];
                $query .= "INSERT INTO `bookkeeping`.`SalesInvoices` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'BR','$Date','$Desc','$Ref','1');<br>";
                $query .= "INSERT INTO `bookkeeping`.`SalesRecord` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat');<br>";
                $query .= "INSERT INTO `bookkeeping`.`SalesPaid` VALUES
                                ('".$_SESSION['CCODE']."','$Dept','$pID','','$Date','$Payee','','$Total','$Desc',0);<br>";
                $processed++;
            }
            else if($line[1]=="CR")//Cash Takings
            {
                $Payee =  $Banks[$Payee];
                $query .= "INSERT INTO `bookkeeping`.`SalesInvoices` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'CR','$Date','$Desc','$Ref','1');<br>";
                $query .= "INSERT INTO `bookkeeping`.`SalesRecord` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat');<br>";
                $query .= "INSERT INTO `bookkeeping`.`SalesPaid` VALUES
                                ('".$_SESSION['CCODE']."','$Dept','$pID','','$Date','$Payee','','$Total','$Desc',0);<br>";
                $processed++;
            }
        }
        else
            $ignored++;
    }
    echo $query     . "<br>Lines: "
       . $lines     . "<br>Processed: "
       . $processed . "<br>Ignored: "
       . $ignored   . "<br>Errors: "
       . $errors    . "<br>"
       . $error;
}
else
    echo "Invalid file type. Please use";
?>
</body></html>
The $pID's are where the ID numbers needs to go. And no, it does not actuly perform any querys yet. And neither can it perform any unless it knows there are no errors in the whole document.
Last edited by Weirdan on Fri Aug 07, 2009 6:53 am, edited 1 time in total.
Reason: added [code] tags
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Questions about MySQL Transations and how to use them in php

Post by VladSun »

etherkye wrote:And neither can it perform any unless it knows there are no errors in the whole document.
That's somehow different (or at least was not clear) from the one you have in the OP:
etherkye wrote:One line will insert into one table, i then need to get it's alto ID number, and use it in the next query. However, it can only process the data if there are no errors at all.
:)

So, you want to parse and insert ALL lines of data and cancel inserting ALL of them if even a single error occurs.
In this case - yes, you need transactions.

Start the transaction.
Perform as many queries you want.
If you encounter an error in you data rollback the transaction.
If there are no errors, commit the transaction.
There are 10 types of people in this world, those who understand binary and those who don't
etherkye
Forum Newbie
Posts: 21
Joined: Fri Aug 07, 2009 3:40 am

Re: Questions about MySQL Transations and how to use them in php

Post by etherkye »

Will a transaction stop other users on the site performing functions normalily (i.e. altocommit).

As i've enver actuly used transactions before.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Questions about MySQL Transations and how to use them in php

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
etherkye
Forum Newbie
Posts: 21
Joined: Fri Aug 07, 2009 3:40 am

Re: Questions about MySQL Transations and how to use them in php

Post by etherkye »

Modified the code to work with the transactions. Hopefully it'll work XD.

Code: Select all

<?php
include $_SERVER['DOCUMENT_ROOT'] . "/include.php";
 
head();
echo "<Body>
Processing Audit Trail.<br/>";
 
if ($_FILES['file']['type'] == "text/comma-separated-values")
{
    //generate Tax desc - Tax Code and Tax Value
    $TaxCode  = array();
    $TaxValue = array();
    $result = mysql_query("SELECT * FROM `bookkeeping`.`TaxCodes`
                           WHERE `CCODE` = '" . $_SESSION['CCODE'] . "'");
    $rows = mysql_num_rows($result);
 
    for($j=0;$j<$rows;$j++)
    {
        $row = mysql_fetch_array($result);
        $TaxCode["T".$row['TCode']]  = $row['Desc'];
    }
 
    //Generate Nominal Code lists
    $Nominal  = array();
    $result = mysql_query("SELECT * FROM `bookkeeping`.`sageCodes`
                           WHERE `CCODE` = '" . $_SESSION['CCODE'] . "'");
    $rows = mysql_num_rows($result);
 
    for($j=0;$j<$rows;$j++)
    {
        $row = mysql_fetch_array($result);
        $Nominal[$row['Code']]  = $row['Desc'];
    }
 
    //Generate Bank Accounts
    $Banks  = array();
    $result = mysql_query("SELECT * FROM `bookkeeping`.`BankAccounts`
                           WHERE `CCODE` = '" . $_SESSION['CCODE'] . "'");
    $rows = mysql_num_rows($result);
 
    for($j=0;$j<$rows;$j++)
    {
        $row = mysql_fetch_array($result);
        $Banks[$row['SAGE']]  = $row['ACC_NAME'];
    }
 
    $File = fopen($_FILES["file"]["tmp_name"], 'r');
 
    $query = "";
    $error = "";
 
    $lines      = 0;
    $errors     = 0;
    $ignored    = 0;
    $processed  = 0;
 
    $query = mysql_query("BEGIN;");
    
    while (!feof($File)) {
        $Error = false;
        $line = fgetcsv($File);
        $lines++;
        
        if(in_array($line[1],array("PI","BP","CP","SI","BR","CR")))
        {
            $Date   = date("Y-m-d",ukstrtotime(($line[2])));
            $Payee  = mysql_real_escape_string($line[3]);
            $Type   = $Nominal[$line[4]];
            $Dept   = mysql_real_escape_string($line[5]);
            $Ref    = mysql_real_escape_string($line[6]);
            $Desc   = mysql_real_escape_string($line[7]);
            $Amount = number_format(mysql_real_escape_string($line[8]),2,'.','');
            $Vat    = number_format(mysql_real_escape_string($line[9]),2,'.','');
            $Tax    = $TaxCode[$line[10]];
 
            if($Payee == ""){
                $error .= "Line: " . $lines . " Invalid Payee<br/>";
                $Error = true;
            }
            if($Type == ""){
                $error .= "Line: " . $lines . " Invalid Nominal Code<br/>";
                $Error = true;
            }
            if($Amount == 0){
                $error .= "Line: " . $lines . " Amount Cannot Be Zero<br/>";
                $Error = true;
            }
            if($Tax == ""){
                $error .= "Line: " . $lines . " Invalid Tax Code<br/>";
                $Error = true;
            }
            if($Error){
                $errors++;
                continue;
            }
            
            if($line[1]=="PI")//Purchse Invoice
            {
                $supplier = mysql_num_rows(mysql_query("SELECT * FROM `bookkeeping`.`Suppliers`
                                                      WHERE `CCODE` = '" . $_SESSION['CCODE'] . "'
                                                      AND   `Ref` = '$Payee'"));
                if($supplier>0){            
                    $query = mysql_query( "INSERT INTO `bookkeeping`.`PurchaseInvoices` VALUES
                                    ('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'PI','$Date','$Payee','$Ref','1')");
                    $pID = mysql_insert_id();
                    $query = mysql_query( "INSERT INTO `bookkeeping`.`PurchaseRecord` VALUES
                                    ('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat')");
                    $processed++;
                }
                else
                {
                    $error .= "Error on line " .$lines. ": Supplier ". $Payee." has not be referenced.<br>";
                    $errors++;
                }
            }
            else if($line[1]=="BP")//Purchase Invoice WO
            {
                $Payee =  $Banks[$Payee];
                $query = mysql_query( "INSERT INTO `bookkeeping`.`PurchaseInvoices` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'BP','$Date','$Desc','$Ref','1')");
                $pID = mysql_insert_id();
                $query = mysql_query( "INSERT INTO `bookkeeping`.`PurchaseRecord` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat')");
                $query = mysql_query( "INSERT INTO `bookkeeping`.`PurchasePaid` VALUES
                                ('".$_SESSION['CCODE']."','$Dept','$pID','','$Date','$Payee','','$Total','$Desc',0)");
                $processed++;
            }
            else if($line[1]=="CP")//Petty Cash
            {
                $Payee =  $Banks[$Payee];
                $query = mysql_query( "INSERT INTO `bookkeeping`.`PurchaseInvoices` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'CP','$Date','$Desc','$Ref','1')");
                $pID = mysql_insert_id();
                $query = mysql_query( "INSERT INTO `bookkeeping`.`PurchaseRecord` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat')");
                $query = mysql_query( "INSERT INTO `bookkeeping`.`PurchasePaid` VALUES
                                ('".$_SESSION['CCODE']."','$Dept','$pID','','$Date','$Payee','','$Total','$Desc',0)");
                $processed++;
            }
            if($line[1]=="SI")//Sales Invoice
            {
                $customer = mysql_num_rows(mysql_query("SELECT * FROM `bookkeeping`.`Customers`
                                                      WHERE `CCODE` = '" . $_SESSION['CCODE'] . "'
                                                      AND   `Ref` = '$Payee'"));
                if($customer>0){
                    $query = mysql_query( "INSERT INTO `bookkeeping`.`SalesInvoices` VALUES
                                    ('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'SI','$Date','$Payee','$Ref','1')");
                    $pID = mysql_insert_id();
                    $query = mysql_query( "INSERT INTO `bookkeeping`.`SalesRecord` VALUES
                                    ('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat')");
                    $processed++;
                }
                else
                {
                    $error .= "Error on line " .$lines. ": Customer ". $Payee." has not be referenced.<br>";
                    $errors++;
                }
            }
            else if($line[1]=="BR")//Sales Invoice WO
            {
                $Payee =  $Banks[$Payee];
                $query = mysql_query( "INSERT INTO `bookkeeping`.`SalesInvoices` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'BR','$Date','$Desc','$Ref','1')");
                $pID = mysql_insert_id();
                $query = mysql_query( "INSERT INTO `bookkeeping`.`SalesRecord` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat')");
                $query = mysql_query( "INSERT INTO `bookkeeping`.`SalesPaid` VALUES
                                ('".$_SESSION['CCODE']."','$Dept','$pID','','$Date','$Payee','','$Total','$Desc',0)");
                $processed++;
            }
            else if($line[1]=="CR")//Cash Takings
            {
                $Payee =  $Banks[$Payee];
                $query = mysql_query( "INSERT INTO `bookkeeping`.`SalesInvoices` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'CR','$Date','$Desc','$Ref','1')");
                $pID = mysql_insert_id();
                $query = mysql_query( "INSERT INTO `bookkeeping`.`SalesRecord` VALUES
                                ('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat')");
                $query = mysql_query( "INSERT INTO `bookkeeping`.`SalesPaid` VALUES
                                ('".$_SESSION['CCODE']."','$Dept','$pID','','$Date','$Payee','','$Total','$Desc',0)");
                $processed++;
            }
        }
        else
            $ignored++;
    }
 
   if($errors == 0)
   {
       echo "No Errors Found. Audit Trail Has Been Processed."
           ."<br>Lines: "     . $lines
           ."<br>Processed: " . $processed
           ."<br>Ignored: "   . $ignored;
       $query = mysql_query("COMMIT;");
 
   }else{
        echo              "<br>Lines: "
           . $lines     . "<br>Processed: "
           . $processed . "<br>Ignored: "
           . $ignored   . "<br>Errors: "
           . $errors    . "<br>"
           . $error;
       $query = mysql_query("ROLLBACK;");
   }
}
else
    echo "Invalid file type. Please use";
?>
</body></html>
Tested: Didnt rollback and where was defently an error.
Last edited by etherkye on Fri Aug 07, 2009 6:33 am, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Questions about MySQL Transations and how to use them in php

Post by VladSun »

Please, edit your posts and substitute the [ code ] BB code tags with [ php ] (without spaces ofcourse) :)
I want to thelp you, but I do hate copying and pasting in my PHP editor just to get the hilight.
There are 10 types of people in this world, those who understand binary and those who don't
etherkye
Forum Newbie
Posts: 21
Joined: Fri Aug 07, 2009 3:40 am

Re: Questions about MySQL Transations and how to use them in php

Post by etherkye »

Sorry, i just pressed the Code button in the reply box. Didn't know there was a php tag.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Questions about MySQL Transations and how to use them in php

Post by VladSun »

Thanks.

You do use InnoDB tables, don't you?
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply