PHP & MySQL Dynamic Form & Array Problem

How to integrate the dynamic php array with multiple MySQL Insert Queries

[Sherif]

Hi Guys,

I know this might have been asked a lot of time, but i kept searching for a solution but couldn't find a clear one.


I am developing a dynamic form based on the number of inputs (Records) the user wants.

then i generate a form based on the number the user enters to populate the form using the following code

<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">

<table>
<th>Row Number</th>
<th>Year</th>
<th>Data</th>
<?php
$i=0;
while($i<$fieldnumbers){

$i_actual=$i+1;
echo "<tr>
<td>$i_actual:</td>
<td><input type='text' name='date[]'/></td>
<td><input type='text' name='value[]'/></td>";
$i++;

}


?>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<input type="hidden" name="MM_insert" value="form1" />
<input type="hidden" name="companyidfinal" value="<?php echo $companyID; ?>" />
<input type="hidden" name="typefinal" value="<?php echo $type; ?>" />

<td><input type="submit" value="Insert New Data" /></td>

</table>

</form>

the $i is the number of records that the user specifies, and it is used for the increment to generate the while loop, and $iactual is the user friendly number since the array starts at 0.


The Action of the form takes me to the following function.

$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 datadetail (companyid, `date`, type, `value`) VALUES (%s, %s, %s, %s)",
GetSQLValueString($_POST['companyidfinal'], "text"),
GetSQLValueString($_POST['date'], "date"),
GetSQLValueString($_POST['typefinal'], "text"),
GetSQLValueString($_POST['value'], "text"));

mysql_select_db($database_data, $connection);
$Result1 = mysql_query($insertSQL, $connection) or die(mysql_error());

$insertGoTo = "../manage.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
?>

I know that this previous code is used to insert a single row of data... but since the number of fields are not necessarily the same each time, i know that i should use the "FOREACH" command or something similar, but i don't know how to tackle this problem since i have more than one value that are different and some values that are constant for each INSERT Command and that can be seen in the form structure having some fields in the loop, and some hidden fields that contain the constant data.



Your help would be much appreciated.


Thanks,
Sherif

[Sherif]

I tried using the following code, but i am getting an error.

for($i = 0, $c = count($dates); $i < $c; $i++) {


$indate=$dates[$i];
$incomanyid=$companyidfinal;
$intype=$typefinal;
$invalue=$value[$i];
$incategory=$category;
$indetail=$detail[$i];

echo $indate . $incomanyid . $intype . $invalue . $incategory . $indetail;

$insertSQL = sprintf("INSERT INTO datadetail (`category`, companyid, `date`, type, `value`, `detail`) VALUES (%s, %s %s, %s, %s, %s)",
   GetSQLValueString($incategory, "text"),
 GetSQLValueString($incompanyid, "text"),
   GetSQLValueString($indate, "date"),
   GetSQLValueString($intype, "text"),
   GetSQLValueString($invalue, "text"),
 GetSQLValueString($indetail, "text"));

 mysql_select_db($database_EngineerOpediaGIS, $EngineerOpediaGIS);
 $Result1 = mysql_query($insertSQL, $EngineerOpediaGIS) or die(mysql_error());

}

I Get the following 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 ''2006-01-00', 'Price', '2400', 'D37 Smooth')' at line 1

I Tested the echo, and i seem to get the correct data, but the problem is when it is taken into the MySQL INSERT INTO Statement, and that is when i get the error.

Any Suggestions on this post and the one above?

Your help will be much appreciated.

Sincerely,
Sherif

[coopster]

You are missing a comma in your VALUES list:

VALUES (%s, %s, %s, %s, %s, %s)",

[Sherif]

Thanks coopster, everything is now working...

but are there any better recommendations as to reduce the amount of code, and to further imrpove the performance?

To be honest, although this code is working, i have a feeling that it will be loading on the server when we increase the amount of data in the array, and i feel that we could have a better integration between the form fields themselves since if someone left something blank, then the values will be shifted by one, and then they won't be associated with each other.

For example we have a date, and value... so if someone enters a date and leaves the corresponding value empty, then the array of the value will be lagging by 1 which will completely spoil everything between the 2 fields..

So does anyone have any other or better method of connection between the fields so that we can avoid any future problems that could arise due to that issue?

I know i could use form validation, but other than that, i also want my code to be structurally sound, and as much as possible to be professional.



Thanks for your cooperation, and patience.
Sherif

[coopster]

You can insert multiple rows with a single insert [dev.mysql.com] statement by using the VALUES list syntax. Basically you loop and build a VALUES list and at the end of your loop execute the query. If you have any UNIQUE key constraints this is obviously going to be a potential issue but for the most part it is an effective method of multi-row insertion.