yandoo Posted July 18, 2009 Share Posted July 18, 2009 Hi there, I was hoping for a little help please. I am trying to insert data into two tables using olny 1 form. In the first table general info about a plant is suppose to be inserted and in the second table is inserted the VegeID from the previous table, username and 12 months of the year, (1 or 0s, determine if seeds can be sown on that month). Im using the mysql_insert_id command to retrieve the VegeID which is inserted in the first query and then also inserted in the second query (along with the months) Trouble is i keep getting errors and neither table is updated.? I think im quite close (hope so) but was hoping somebody could have a look at my code because i cant seem to work it out. $editFormAction = $_SERVER['PHP_SELF']; if (isset($_SERVER['QUERY_STRING'])) { $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']); } if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) { $insertSQL = sprintf("INSERT INTO vegetable (NULL, TypeID, Name, EnvID, ThinOutInDays, Moisture, Hardiness, Soil, Light, Details, HarvestInDays, Username) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", GetSQLValueString($_POST['TypeID'], "int"), GetSQLValueString($_POST['Name'], "text"), GetSQLValueString($_POST['EnvID'], "int"), GetSQLValueString($_POST['ThinOutInDays'], "int"), GetSQLValueString($_POST['Moisture'], "text"), GetSQLValueString($_POST['Hardiness'], "text"), GetSQLValueString($_POST['Soil'], "text"), GetSQLValueString($_POST['Light'], "text"), GetSQLValueString($_POST['Details'], "text"), GetSQLValueString($_POST['HarvestInDays'], "int"), GetSQLValueString($_POST['Username'], "text")); $vegeid = mysql_insert_id(); $insertSQL_b = sprintf("INSERT INTO outdoorsowtime (Username, VegeID, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec) VALUES (%s, '$vegeid', %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", GetSQLValueString($_POST['Username'], "text"), GetSQLValueString($_POST['Jan'], "int"), GetSQLValueString($_POST['Feb'], "int"), GetSQLValueString($_POST['Mar'], "int"), GetSQLValueString($_POST['Apr'], "int"), GetSQLValueString($_POST['May'], "int"), GetSQLValueString($_POST['Jun'], "int"), GetSQLValueString($_POST['Jul'], "int"), GetSQLValueString($_POST['Aug'], "int"), GetSQLValueString($_POST['Oct'], "int"), GetSQLValueString($_POST['Nov'], "int"), GetSQLValueString($_POST['Dec'], "int")); mysql_select_db($database_connect, $connect); $Result1 = mysql_query($insertSQL, $insertSQL_b, $connect) or die(mysql_error()); Thank You:) Quote Link to comment https://forums.phpfreaks.com/topic/166410-solved-insert-into-multiple-tables-using-1-form/ Share on other sites More sharing options...
Aeglos Posted July 18, 2009 Share Posted July 18, 2009 mysql_query() can't accept 2 query strings. First argument is the query, second is the optional link identifier. You are basically saying "run $insertSQL query on the $insertSQL_b database connection". You have to use two different mysql_query() function calls, one with each query string. $result1 = mysql_query($insertSQL, $connect) or die(mysql_error()); $result2 = mysql_query($insertSQL_b, $connect) or die(mysql_error()); Quote Link to comment https://forums.phpfreaks.com/topic/166410-solved-insert-into-multiple-tables-using-1-form/#findComment-877538 Share on other sites More sharing options...
yandoo Posted July 18, 2009 Author Share Posted July 18, 2009 Hi there Thank you for the reply the help is appreciated I see what you mean and have made the change to put it into 2 querys. Im still geting a few errors though and funnily they say May - Dec is undefined index (other months no errors?) and that theres a mysql syntax error. " Notice: Undefined index: May in G:\wamp\www\greenlife\add_vege.php on line 82 Notice: Undefined index: Jun in G:\wamp\www\greenlife\add_vege.php on line 83 Notice: Undefined index: Jul in G:\wamp\www\greenlife\add_vege.php on line 84 Notice: Undefined index: Aug in G:\wamp\www\greenlife\add_vege.php on line 85 Notice: Undefined index: Oct in G:\wamp\www\greenlife\add_vege.php on line 86 Notice: Undefined index: Nov in G:\wamp\www\greenlife\add_vege.php on line 87 Notice: Undefined index: Dec in G:\wamp\www\greenlife\add_vege.php on line 88 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL, TypeID, Name, EnvID, ThinOutInDays, Moisture, Hardiness, Soil, Light, Deta' at line 1" Neither table is updating either. What am i missing? Thank You Quote Link to comment https://forums.phpfreaks.com/topic/166410-solved-insert-into-multiple-tables-using-1-form/#findComment-877541 Share on other sites More sharing options...
Aeglos Posted July 18, 2009 Share Posted July 18, 2009 The "Notice: undefined index..." errors mean that those variables don't exist. It would seem that the form never set them and they were never posted, so check the form for errors. The error in your SQL syntax is due to the "NULL" you have in you column list. It servers no purpose and is messing things up. Notice that you have 11 values to insert, and 12 fields, that can't be... the quantity has to match, also SQL tries to map the first value to the NULL column which is nonexistant and the query fails. Remove the NULL item from the query. If you are concerned about an ID value in the database that comes first (as I asume), don't worry. If you don't reference columns in an insert query, SQL will fill them up with their default or simply leave them blank. Just remember to have the same number of values and (corresponding) column names in the query. ALSO. mysql_insert_id() will return nothing until you actually run the query, so you have to move it down... ... $result1 = mysql_query($insertSQL, $connection); $vegeId = mysql_insert_id(); // Here it will work, the query has already run. $insertSQL_b = ... //Your SQL query that uses $vegeId. $result2 = mysql_query($insertSQL_b, $connection); Quote Link to comment https://forums.phpfreaks.com/topic/166410-solved-insert-into-multiple-tables-using-1-form/#findComment-877543 Share on other sites More sharing options...
yandoo Posted July 18, 2009 Author Share Posted July 18, 2009 Thanks for the reply, I have made the modifications you suggested and realised i have the wrong "GetSQLValueString" for the month checkboxes and edited to: $insertSQL_b = sprintf("INSERT INTO outdoorsowtime (Username, VegeID, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec) VALUES (%s, '$vegeid', %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", GetSQLValueString($_POST['Username'], "text"), GetSQLValueString(isset($_POST['Jan']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Feb']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Mar']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Apr']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['May']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Jun']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Jul']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Aug']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Sep']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Oct']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Nov']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Dec']) ? "true" : "", "defined","1","0")); The first table now updates correlcty!! The second doesnt update yet though. And again i am getting a sql error, but the data appears to be correct (same vegeID from prev query and checkbox entered values)! "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Dec) VALUES ('Tom', '5', 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0)' at line 1" I think its very close...and hope it is something quite easy.. Any ideas? Thank You Quote Link to comment https://forums.phpfreaks.com/topic/166410-solved-insert-into-multiple-tables-using-1-form/#findComment-877557 Share on other sites More sharing options...
yandoo Posted July 18, 2009 Author Share Posted July 18, 2009 Heres how it looks now: $insertSQL = sprintf("INSERT INTO vegetable (TypeID, Name, EnvID, ThinOutInDays, Moisture, Hardiness, Soil, Light, Details, HarvestInDays, Username) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", GetSQLValueString($_POST['TypeID'], "int"), GetSQLValueString($_POST['Name'], "text"), GetSQLValueString($_POST['EnvID'], "int"), GetSQLValueString($_POST['ThinOutInDays'], "int"), GetSQLValueString($_POST['Moisture'], "text"), GetSQLValueString($_POST['Hardiness'], "text"), GetSQLValueString($_POST['Soil'], "text"), GetSQLValueString($_POST['Light'], "text"), GetSQLValueString($_POST['Details'], "text"), GetSQLValueString($_POST['HarvestInDays'], "int"), GetSQLValueString($_POST['Username'], "text")); mysql_select_db($database_connect, $connect); $result1 = mysql_query($insertSQL, $connect) or die(mysql_error()); $vegeid = mysql_insert_id(); $insertSQL_b = sprintf("INSERT INTO outdoorsowtime (Username, VegeID, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec) VALUES (%s, '$vegeid', %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", GetSQLValueString($_POST['Username'], "text"), GetSQLValueString(isset($_POST['Jan']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Feb']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Mar']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Apr']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['May']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Jun']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Jul']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Aug']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Sep']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Oct']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Nov']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['Dec']) ? "true" : "", "defined","1","0")); $result2 = mysql_query($insertSQL_b, $connect) or die(mysql_error()); Quote Link to comment https://forums.phpfreaks.com/topic/166410-solved-insert-into-multiple-tables-using-1-form/#findComment-877558 Share on other sites More sharing options...
Aeglos Posted July 18, 2009 Share Posted July 18, 2009 I can't find a mistake at a quick glance. Could you put echo $insertSQL_b before the last mysql_query() and paste the output here? So we can check for errors in the full SQL query that you are trying to run. Quote Link to comment https://forums.phpfreaks.com/topic/166410-solved-insert-into-multiple-tables-using-1-form/#findComment-877675 Share on other sites More sharing options...
yandoo Posted July 18, 2009 Author Share Posted July 18, 2009 Hey thanks for the help Heres what is outputed: INSERT INTO outdoorsowtime (Username, VegeID, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, `Dec`) VALUES ('Tom', `15`, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 1) I think the Dec is a reserved mysql word so i have simply add `` around it.... $insertSQL_b = sprintf("INSERT INTO outdoorsowtime (Username, VegeID, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, `Dec`) The error i receive now is: Unknown column '15' in 'field list' The 15 represents the current VegeID number from the first query... What ya think? Thank You Quote Link to comment https://forums.phpfreaks.com/topic/166410-solved-insert-into-multiple-tables-using-1-form/#findComment-877692 Share on other sites More sharing options...
Aeglos Posted July 18, 2009 Share Posted July 18, 2009 The single quotes around the $vegeid var are the problem. Somewhere the '15' gets converted to `15` which is column name designation. I'd try removing the single quotes around it (which works) or checking where and why they change... Maybe the sprintf function does it, don't really know. But since it's a value you generate and get yourself from the script/database, there should be no risks to removing the single quotes. May I ask why are you using sprintf to build the queries though? It's simplier to just concatenate the variables, seems like added bloat with no practical benefit to me. Quote Link to comment https://forums.phpfreaks.com/topic/166410-solved-insert-into-multiple-tables-using-1-form/#findComment-877705 Share on other sites More sharing options...
yandoo Posted July 18, 2009 Author Share Posted July 18, 2009 Eureeka!! Thats it! Thank You so much for helping me, ive really learned a lot. Im very much a beginnner at php and was trying to save time building the form and query and used dreamweaver to generate it. Then modified it myself and with help from you. Im sure there are loads of better ways to do it but i find i wouldnt know where to begin. Updating multiple tables has been something ive wanted to know how to do for a long time. Thank You so much Quote Link to comment https://forums.phpfreaks.com/topic/166410-solved-insert-into-multiple-tables-using-1-form/#findComment-877726 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.