Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2009
    Posts
    11

    Unanswered: problem executing sql query

    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.
    Last edited by SilverJester; 02-24-11 at 14:59.

  2. #2
    Join Date
    Dec 2009
    Posts
    11
    I just discovered the mysql_error() and mysql_errno() functions and put them to use. I got this error:

    1062: Duplicate entry 'abc-red' for key 'PRIMARY'

    after entering in a few other records with Item_num of "abc" and various Color values. This error was inn regards to the second query (the insert into the apparel table).

    edit:
    also remember despite the error, the record is still added to the table?!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •