I'm having a strange issue with my php code and was hoping you guys could help. First some background: I have a single database (mysql) and 2 sql queries (which are generated and executed by the php) which access 2 tables ("products" and "apparel") in the database. Both tables have primary composite keys of "Item_Num" and "Color". apparel.Item_Num however has, is a foreign key dependent on products.Item_Num.
Basically this is a user interface to enter new products into an online shopping cart. After entering a product, the php should tell the user if was successful or not...but this is where my problem is. My original code (back when the primary was solely Item_num in each table aka before I added color and made them composite keys), worked just fine and the code looked like this:
Code:
if ( mysql_query($sql1) && mysql_query($sql2) )
echo "<p><h1>" . $_POST["Name"] . " added to database successfully!</h1></p>";
else
{
//There was a problem with one of the 2 queries. (Query #2 will not be inserted without #1 due to foreign key constraints)
// Therefore only need to attempt to delete #1, deletion will be cascaded.
echo "<p><h1>There was a problem inserting " . $_POST["Name"] . " to the database.</h1></p>";
mysql_query("DELETE FROM `socceret_socceretcDB`.`products` WHERE `products`.`Item_Num` = '" . $_POST["Item_Num"] . "' AND `products`.`Color` = '" . $_POST["Item_Num"] . ";");
}
After adding the composite keys, the above code wouldn't work and would always jump to the else condition. So I made some slight changes to the if statement:
Code:
if ( mysql_query($sql1) )
{
echo "<p><h1>sql 1 ok</h1></p>";
sleep(1); //I don't think this makes a difference, just testing a theory
if ( mysql_query($sql2) )
{
echo "<p><h1>" . $_POST["Name"] . " added to database successfully!</h1></p>";
}
}
else
{
//There was a problem with one of the 2 queries. (Query #2 will not be inserted without #1 due to foreign key constraints)
// Therefore only need to attempt to delete #1, deletion will be cascaded.
echo "<p><h1>There was a problem inserting " . $_POST["Name"] . " to the database.</h1></p>";
mysql_query("DELETE FROM `socceret_socceretcDB`.`products` WHERE `products`.`Item_Num` = '" . $_POST["Item_Num"] . "' AND `products`.`Color` = '" . $_POST["Item_Num"] . ";");
}
Now here's the strange part...both mysql_query($sql1) and mysql_query($sql2) seem to execute correctly and the product is correctly added to the database (even if I test the generated sql query manually). If both Color AND Item_Num are unique I get the "added to database successfully!" message from the second if statement. However if the the Item_Num exist already and a new Color is added (say Item_Num "abc" Color "white" is already in the db, and "abc", "black" is added), then the "added to database successfully!" is NOT displayed despite the fact that the product is entered into both tables (meaning both queries executed without error).
What do you guys think? For some reason the second query ( mysql_query($sql2) ) seems to be returning false despite the fact that it is executing correctly without error. Honestly I believe the original code should still work. Any help would be appreciated.
dBforums
>
problem executing sql query
02-24-11, 13:55
Linear Mode
