Page 1 of 2

Questions about MySQL Transations and how to use them in php

Posted: Fri Aug 07, 2009 4:53 am
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.

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

Posted: Fri Aug 07, 2009 5:06 am
by papa

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

Posted: Fri Aug 07, 2009 5:28 am
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.

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

Posted: Fri Aug 07, 2009 5:30 am
by marty pain
Works fine here.

Go to PHP.net and search for mysql_insert_id

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

Posted: Fri Aug 07, 2009 5:32 am
by papa
php.net

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


cheers

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

Posted: Fri Aug 07, 2009 5:45 am
by etherkye
Even with that, wouldn't i need to go through the document twice?

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

Posted: Fri Aug 07, 2009 5:53 am
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.

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

Posted: Fri Aug 07, 2009 5:57 am
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.

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

Posted: Fri Aug 07, 2009 6:07 am
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.

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

Posted: Fri Aug 07, 2009 6:09 am
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.

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

Posted: Fri Aug 07, 2009 6:12 am
by VladSun

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

Posted: Fri Aug 07, 2009 6:23 am
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.

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

Posted: Fri Aug 07, 2009 6:32 am
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.

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

Posted: Fri Aug 07, 2009 6:34 am
by etherkye
Sorry, i just pressed the Code button in the reply box. Didn't know there was a php tag.

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

Posted: Fri Aug 07, 2009 6:36 am
by VladSun
Thanks.

You do use InnoDB tables, don't you?