Questions about MySQL Transations and how to use them in php
Moderator: General Moderators
Questions about MySQL Transations and how to use them in php
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.
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
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.
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
Works fine here.
Go to PHP.net and search for mysql_insert_id
Go to PHP.net and search for mysql_insert_id
Re: Questions about MySQL Transations and how to use them in php
php.net
The function is called mysql_insert_id(). Might be what you are after.
cheers
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
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
I don't think so.etherkye wrote:Even with that, wouldn't i need to go through the document twice?
Post your code, please.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Questions about MySQL Transations and how to use them in php
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>
Last edited by Weirdan on Fri Aug 07, 2009 6:53 am, edited 1 time in total.
Reason: added [code] tags
Reason: added [code] tags
Re: Questions about MySQL Transations and how to use them in php
That's somehow different (or at least was not clear) from the one you have in the OP:etherkye wrote:And neither can it perform any unless it knows there are no errors in the whole document.
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
Re: Questions about MySQL Transations and how to use them in php
Will a transaction stop other users on the site performing functions normalily (i.e. altocommit).
As i've enver actuly used transactions before.
As i've enver actuly used transactions before.
Re: Questions about MySQL Transations and how to use them in php
Take a look at this
http://www.databasejournal.com/features ... -MySQL.htm
http://www.databasejournal.com/features ... -MySQL.htm
There are 10 types of people in this world, those who understand binary and those who don't
Re: Questions about MySQL Transations and how to use them in php
Modified the code to work with the transactions. Hopefully it'll work XD.
Tested: Didnt rollback and where was defently an error.
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>
Last edited by etherkye on Fri Aug 07, 2009 6:33 am, edited 1 time in total.
Re: Questions about MySQL Transations and how to use them in php
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.
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
Re: Questions about MySQL Transations and how to use them in php
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
Thanks.
You do use InnoDB tables, don't you?
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