[SOLVED] General Question about PHP/Database weirdness...

[Sephirangel]

Question: Why do mysql queries enter the same record twice into a table when only called once?

 

I have had this problem on many of my INSERT queries even though i only call them once. I have resolved the issue my creating a self incrementing primary key and also making a unique key. However, on one of my tables making a unique key is not possible (eg linking a Sale record to stock items in the sale) so it enters the same record twice.

 

Any ideas??

 

 

PS: im new to this website so hello everyone! ^^

[jonsjava]

could we see the code in question?

[JonnoTheDev]

This is a common question and is usually caused by the page either being loaded twice or a user hitting forward, back, refresh in their browser. This is easily solved by redirecting the user back to the page after the insert query has run successfully

 

// database query
mysql_query("INSERT INTO blah,blah,blah");
// redirect user
header("Location:mypage.php");

[Sephirangel]

Basically my program takes sales information from the admin and places it in 2 tables, 'sales' and 'saleitems'. the saleitems table holds information about the items in a particular sale (as there can be an infinite amount of items in 1 sale) and contains the sale number of the sale they are in. Another function then alters the main stock quantity of each item that is in the sale.

 

Basically, i have added a unique field to the sales table (in this case, Time) and that has solved the duplicate problem for that table but it is entering the 'saleitems' record twice and altering the stock quantity twice (so if someone buys 2 of an item it takes 4 off the quantity)

 

Here are the functions and where i call them:

 

function getSalesNum($salesArray){

	$getSaleNo = "SELECT * FROM sales WHERE Time = '" . $salesArray['saleTime'] . "'";

	$q = mysql_query($getSaleNo);
	$row = mysql_fetch_assoc($q);
	$saleN = $row['Sales_Num'];
	return $saleN;

}

function insertSaleItem($salesArray, $saleN){

	for($i = 1; $i <= $salesArray['noOfItems']; $i++){

			$itemCode = $_SESSION['ItemCode'.$i];
			$saleQuant = $_SESSION['Quantity'.$i];
			$pricePerItem = $_SESSION['PricePerItem'.$i];
			$insertSaleItems = "INSERT IGNORE INTO saleitems (Sales_Num, ItemCode, SaleQuantity, PricePerItem) VALUES ('" . $saleN . "','" . $itemCode . "','" . $saleQuant . "','" . $pricePerItem ."')";

			mysql_query($insertSaleItems);
	}

}

function changeInv($salesArray){

	for($i = 1; $i <= $salesArray['noOfItems']; $i++){
		$itemCode = $salesArray['ItemCode'.$i];
		$saleQuant = $salesArray['Quantity'.$i];
		$getInvQuant = "SELECT * FROM stock WHERE ItemCode = '" . $itemCode ."'";

		$q = mysql_query($getInvQuant);
		$row = mysql_fetch_assoc($q);
		$quant = $row['Quantity'];
		echo $quant;
		$newQuant = $quant - $saleQuant;
		echo $newQuant;
		$changeInvQuant = "UPDATE stock SET Quantity = '" . $newQuant . "' WHERE ItemCode = '" . $itemCode . "'";
		mysql_query($changeInvQuant);
	}	

}

 

Because the 'sales' number is generated by the database, i have to get it from the table in order to enter it into the 'saleitems' table.

 

This is where im calling them:

 

<?php

$newSale = insertNewSale($_SESSION);
echo $newSale;

$saleN = getSalesNum($_SESSION);
insertSaleItem($_SESSION, $saleN);

changeInv($_SESSION);
?>

 

Im pretty new to PHP so be gentle!

 

Thankyou for your rapid replies! Im gonna like it here

[JonnoTheDev]

This must have something to do with the number of iterations in your loops containing the SQL. I would get it to print text to the screen within the loops rather than perform the SQL query for a test. If the sentence print out more than expected then you know the loops are incorrect

[Sephirangel]

Fraid not. The for statement is correct. If  $salesArray['noOfItems'] == 1, then the loop only executes once.

 

Any other suggestions?

[PFMaBiSmAd]

As neil.johnson wrote in Reply #2, duplicate data is usually caused by a page being requested twice and the code on that page unconditionally executing.

 

Some specific causes of this are javascript code that submits a page and then the actual form submitting a page or URL rewriting. Do you have any javascript code on the form or are you rewriting URLs?

 

A common solution is to use a session variable to indicate that a page has already been executed and then surround your code in a conditional statement that only runs your code if the page has not already been visited.

[Sephirangel]

I do not have any Javascript code on that particular page PFMaBiSmAd.

Do you mean, set a $_SESSION variable to 0 and increment it at the very start of the page? then say if  $_SESSION['pageLoad'] == 1 then carry out statement?

[Sephirangel]

I have just added a $_SESSION variable on the page before with the form which submits the data and incrementing it by 1 at the top of the page in question then added the conditional statement around the functions and, low and behold, the records have only been inserted once!

 

Thankyou for your help neil.johnson and PFMaBiSmAd, a nice welcome to the forums

 

Sephirangel