[RESOLVED] Insert Into Multiple Tables Using 1 Form

[drax007]

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 🙂

[Weedpacket]

  $Result1 = mysql_query($insertSQL, $insertSQL_b, $connect)

This would need to be two separate statements.

[drax007]

Thanks for the reply. I see what you mean and have modified to use seperate queries.

Now 1 of the table updates but the other doesnt and i get the following syntax error:

"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"

Heres the modified code:

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

I think im really close now but just cant work out this error...

Please help 🙂

[johanafm]

"dec" is a reserved word in MySQL, and since the SQL language is case insensitive (but generally not variables, table and fields names etc), that includes "Dec" as well. Change your code to

..., Nov, `Dec`) VALUES

Those characters are not single quotes, they're acute accents, generally referred to as backticks.

[drax007]

Hi there,

Thank you so much for the reply, ive been stuck on this for ages now.

I didnt know Dec was a reserved word. I have changed accordingly. Is there anywhere else i need to modify?

As i get error Unknown column '11' in 'field list'...

The '11' represents the vegeID from the prev query an only the first table is updated.

What does this mean?

Thank You 🙂

[johanafm]

Looks fine to me. Perhaps time to post your updated code again, and please do use php tags instead of code tags for php code.
Also, could you echo $insertSQL_b to show what the query really looks like.

[drax007]

Hi there,

I worked it out! I had to remove the `` from the $vegeid variable in the 2nd SELECT query!

Thank You for your help! I would never have known about Dec being reserved and would have been scrating my head for ages.

Thanks again!

[lfelipecoutinho]

Hello,
I am new in the forum and not a developer as well. I am trying to build a prototype for a web based system mostly using dreamweaver to show to my IT team what i want. And I am stucked in the error below. I know the problem with mixing mysql with mysqli but I am tryin to buil somethin really simple and it is easeir with DW support. Can someone please help me? Thanks in advance

Error: 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 'clientes (cli_Nome, cli_CNPJ, cli_Telefone, cli_endereço, cli_responsavel, cli_' at line 1

Code:

<?php require_once('Connections/conexao.php'); ?>
<?php

$hostname_conexao = "localhost";
$database_conexao = "urbanforo";
$username_conexao = "root";
$password_conexao = "paizao10";
$conexao = mysqli_connect($hostname_conexao, $username_conexao, $password_conexao,$database_conexao);

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysqli_real_escape_string(dbconnect(),$theValue) : mysqli_escape_string(dbconnect(),$theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;

case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$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 cadastro clientes (cli_Nome, cli_CNPJ, cli_Telefone, cli_endereço, cli_responsavel, cli_e-mail, cli_senha) VALUES (%s, %s, %s, %s, %s, %s, %s)",
GetSQLValueString($POST['cli_Nome'], "text"),
GetSQLValueString($POST['cli_CNPJ'], "int"),
GetSQLValueString($POST['cli_Telefone'], "int"),
GetSQLValueString($POST['cli_endereo'], "text"),
GetSQLValueString($POST['cli_responsavel'], "text"),
GetSQLValueString($POST['cli_email'], "text"),
GetSQLValueString($_POST['cli_senha'], "text"));

mysqli_select_db($conexao,$database_conexao);
$Result1 = mysqli_query($conexao,$insertSQL) or die(mysqli_error($conexao));

$insertGoTo = "sucesso.php";
if (isset($SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>Cadastro Clientes</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
<table align="center">
<tr valign="baseline">
<td nowrap="nowrap" align="right">Nome:</td>
<td><input type="text" name="cli_Nome" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">CNPJ:</td>
<td><input type="text" name="cli_CNPJ" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Telefone:</td>
<td><input type="text" name="cli_Telefone" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Endereço:</td>
<td><input type="text" name="cli_endereo" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Responsavel:</td>
<td><input type="text" name="cli_responsavel" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">E-mail:</td>
<td><input type="text" name="cli_email" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Senha:</td>
<td><input type="text" name="cli_senha" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">&nbsp;</td>
<td><input type="submit" value="Insert record" /></td>
</tr>
</table>
<input type="hidden" name="MM_insert" value="form1" />
</form>
<p>&nbsp;</p>
</body>
</html>