This is a strange problem that i can’t seem to figure out. I have some php code that processes a form but for some reason the sql code does not insert the data in the table. I can echo the sql statement and copy and paste it into mysql and it works fine. Here is the code:
$lngCartID = $_SESSION[‘CartKey’];
$strSQL = “”;
$rsTemp=mysql_query("SELECT ID_NO, (Select SellingPrice from InventoryItemPrices Where InventoryItems_ID = InventoryItems.ID_NO and date(StartDate)<=date(Now()) and Date(EndDate)>= date(now()) Limit 1) as SellingPrice From InventoryItems Where UpSell = ".checkSQL(true,3)) or die(mysql_error());
for ($x=0;$x<mysql_num_rows($rsTemp);$x++)
{
$lngID = mysql_result($rsTemp,$x, “ID_NO”);
$lngPrice = mysql_result($rsTemp,$x, “SellingPrice”);
$blnAdd = getRequest(“chkAddToCart”.$lngID,3);
if ($blnAdd)
{
$strSQL = $strSQL ."(".checkSQL($lngCartID,1).",".checkSQL($lngID,1).", 1,".checkSQL($lngPrice,1).")";
$strSQL = $strSQL.",";
}
}
$strSQL = substr_replace($strSQL,"",-1);
mysql_query("Insert Into WebOrderDetail (WebOrder_ID, InventoryItems_ID, QTY, Price) Values ".$strSQL) or die(mysql_error());
The above code uses two functions: One, for processing the information passed from the form, two, for validating the information before it’s inserted into the database. Both of those functions have been used many times and work perfectly.
I have done some debugging myself and also found some other strange behaviors. The table i am inserting the data in is a detailed table for the order table. This table has a field that links back to the order table, WebOrder_ID. I don’t have a relationship created between these two tables. If i leave that field blank or if i put a number in the field that is different the the current order, the sql statement works perfectly. Also if i create a duplicate table and name is something different than use that table in the above code, the insert works.
For fun, I tried the insert statement with a different order id then the one that’s being currently used by the person purchasing on line, than perform an update statement to change the wrong order id to the correct one, it deletes the records from the order detail table.
I have never seen any type of behavior like this before. My guess is that it has to do with locks, but i am not sure why. I am not accessing the order detail table prior to the insert statement.
If any one has any suggestions, it would be greatly appreciated.
Thanks
Todd