Jump to content

[SOLVED] Insert Into Multiple Tables Using 1 Form

yandoo

By yandoo
in PHP Coding Help

 Share

Recommended Posts

yandoo Advanced Member

yandoo

Posted
Posted

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:)

Link to comment
Share on other sites
Aeglos Newbie

Aeglos

Posted
Posted

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());

Link to comment
Share on other sites
yandoo Advanced Member

yandoo

Posted
  • Author
Posted

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 :)

Link to comment
Share on other sites
Aeglos Newbie

Aeglos

Posted
Posted

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);

 

 

Link to comment
Share on other sites
yandoo Advanced Member

yandoo

Posted
  • Author
Posted

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 :)

Link to comment
Share on other sites
yandoo Advanced Member

yandoo

Posted
  • Author
Posted

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());

Link to comment
Share on other sites
Aeglos Newbie

Aeglos

Posted
Posted

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.

Link to comment
Share on other sites
yandoo Advanced Member

yandoo

Posted
  • Author
Posted

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 :)

Link to comment
Share on other sites
Aeglos Newbie

Aeglos

Posted
Posted

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.

Link to comment
Share on other sites
yandoo Advanced Member

yandoo

Posted
  • Author
Posted

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 :)

 

 

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.