Var = while loop?--Need help with best method

[RopeADope]

Hi all.  I'm working on a script to handle processing of several forms.  The issue I've run into is I cannot figure out how to execute a loop and get all of those results into a variable(query).  I can't quite seem to wrap my head around the idea.  Here's what I have thus far...

$new_post_array = array();

foreach ($_POST as $param)
{
    $new_post_array[] = $param;
};

print_r($new_post_array);

 

The following is what I think i may need to do...

$new_post_array = array();

foreach ($_POST as $param)
{
    $new_post_array[] = $param;
};

$i=1;
$sql="INSERT INTO $new_post_array[0]("; //$_new_post_array[0] holds the table name;

while($i<count($new_post_array)){
                $sql=$sql . $new_post_array[$i]; //concat the first part of the sql with the field names
}
$sql=$sql . ")values(";

while($i<count($new_post_array)){
                $sql=$sql . $new_post_array[$i]; //concat the second part of the sql with the values
}
$sql=$sql . ")"; //close the sql

 

I realize this is a huge mess...but I can't figure out an easier way to do it as of yet.  Any help would be MUCH appreciated.

 

(Side note: I just realized that the first portion of the sql statement where I give the field names is incorrect in that it will put the values instead of the field names I need.  That's simple enough to fix so please ignore that error.  My primary goal is to get the $sql statement constructed as efficiently as possible.)

[Psycho]

First, I would recommend referencing your POST variables by name. If you ever need another field int he POST data that should not be part of your query you will have a difficult time implementing it with what you have. You can give all the fields used for your query the same name as an array to begin with:

Table: <input type="text" name="table" />
Value 1: <input type="text" name="values[]" />
Value 2: <input type="text" name="values[]" />
etc...

 

Then you can reference all the fields needed for your query using a foreach on $_POST['values']

 

Anyway, no matter how you get your values in an array, creating a single query is fairly straitforward.

$sql_values = array();
foreach($_POST['values'] as $value)
{
    $sql_values[] = "('" . mysql_real_escape_string($value) . "')";
}

 

//Create complete insert query

$table = mysql_real_escape_string($_POST['table']);

$query = "INSERT INTO {$table} VALUES " . implode(', ', $sql_values);

[RopeADope]

Hmmm...I'm still fairly novice to PHP...I think that's obvious, haha.  I just have a few questions on your code to clarify my understanding.

 

What does name="values[]" do?  (I've only ever seen the basic <input type="text" name="fname"> etc...)

Table: <input type="text" name="table" />
Value 1: <input type="text" name="values[]" />
Value 2: <input type="text" name="values[]" />
etc...

 

 

What's the purpose of the { } around $table in this statement?

$query = "INSERT INTO {$table} VALUES " . implode(', ', $sql_values);

[Psycho]

What does name="values[]" do?  (I've only ever seen the basic <input type="text" name="fname"> etc...)

Table: <input type="text" name="table" />
Value 1: <input type="text" name="values[]" />
Value 2: <input type="text" name="values[]" />
etc...

By adding the [] to the variable names, the values are passed to the PHP code as an array. As I was trying to say, using a loop over ALL post fields is just a bad idea. This way, have one field for the table name and then use multiple fields (with the same name) as the values.

 

What's the purpose of the { } around $table in this statement?

$query = "INSERT INTO {$table} VALUES " . implode(', ', $sql_values);

 

Variables in double quoted stings are interpreted by the PHP parser. The {} helps to avoid problems in the interpretation if there are other characters next to the variable that could cause problems. Especially useful when referencing arrays within a string. I just make it a habit.

 

http://www.php.net/manual/en/language.types.string.php#language.types.string.parsing

 

[RopeADope]

Ok, I have a better understanding now.  I've just got one last question for clarification then I'll give it a go.  The following is one of the forms...

<form name="timesheets" method="post" action="process_data.php">
<input type="hidden" name="timesheets" value="timesheets" />
<table style="background-color:#ccc;border-radius:10px;padding:5px;">
<tr>
	<th>Timesheets</th>
</tr>
<tr>
	<td>Date</td><td>Activity</td><td>Hours</td><td>Description</td>
</tr>
<tr>
	<td><input type="text" name="date" /></td>
	<td>
		<select name="activity">
			<option>Maintenance</option>
			<option>Break/Fix</option>
			<option>Admin</option>
			<option>Service Request</option>
			<option>Training</option>
		</select>
	</td>
	<td><input type="text" name="hours" /></td>
	<td><input type="text" name="description" /></td>
</tr>
<tr>
	<td colspan="4"><input type="submit" value="Submit" style="width:100%;" /></td>
</tr>
</table>
</form>

 

So what I should do is change the form to look like the following?

<form method="post" action="process_data.php">
<table name="table" style="background-color:#ccc;border-radius:10px;padding:5px;">
<tr>
	<th>Timesheets</th>
</tr>
<tr>
	<td>Date</td><td>Activity</td><td>Hours</td><td>Description</td>
</tr>
<tr>
	<td><input type="text" name="values[]" /></td>
	<td>
		<select name="values[]">
			<option>Maintenance</option>
			<option>Break/Fix</option>
			<option>Admin</option>
			<option>Service Request</option>
			<option>Training</option>
		</select>
	</td>
	<td><input type="text" name="values[]" /></td>
	<td><input type="text" name="values[]" /></td>
</tr>
<tr>
	<td colspan="4"><input type="submit" value="Submit" style="width:100%;" /></td>
</tr>
</table>
</form>

[RopeADope]

Bump

[Psycho]

You don't want to give all the inputs the name "values[]" based upon the form you posted. The original PHP code you posted appeared to suggest you were submitting multiple values of the same logical entity "type". (e.g. submitting a list of book titles). However, the form you show has mutiple unique data types. You should keep the fields with their descriptive names and change your PHP processing code to actually use those name. The whole point of allowing you to put a name for values is so that you can reference them in a logical coherant way. That is why you shouldn't just do a foreach() loop on the POST data.

 

So, change your PHP code to something like this:

$date = (isset($_POST['date'])) ? trim($_POST['date']) : '';
$activity = (isset($_POST['activity'])) ? trim($_POST['date']) : '';
$hours = (isset($_POST['hours'])) ? trim($_POST['date']) : '';
$descr = (isset($_POST['ddescription'])) ? trim($_POST['date']) : '';

//Perform any needed validation or data conversions (e.g. mysql_real_escape_string())

//If validation passes import into db
$query = "INSERT INTO tableName ('date', 'activity', 'hours', 'description')
             VALUES ('$date', '$activity', '$hours', '$descr')";
mysql_query($query);

[RopeADope]

Apologies.  I don't think I've explained myself well enough.  I don't want to explicitly have to write the SQL statement because the processing script will handle upwards of 40 forms, all with unique fields.  What I'm trying to do is have the following happen on a form submission...

 

Form->Processing script

--Pass a hidden variable to represent the name of the form.  This variable is identical to the name of a mysql table, that way I can just put $table_name into an SQL statement and have it work for every form.

--Pass the fields in the form to the processing script which will in turn put them into an array so I can use $form_values in an SQL statement and have it work for every form.

 

I'm looking to do something like this...

$table_name=$_POST['table'];
foreach($_POST as $value)
{
    $sql_values[] = "('" . mysql_real_escape_string($value) . "')";
}
$query = "INSERT INTO {$table} VALUES " . implode(', ', $sql_values);

This is pretty much what you initially posted.  Would this work for what I'm trying to do?

[Psycho]

Well, although I like having elegant code that can work as you change the parameters, you will lose the ability to perform the appropriate validation logic based upon the field types: String, int, date, etc. There are ways you could implement coe that requires little setup but still allows you to validate efficiently. For instance you could set up a list for each form. The list would include each field name and type and you could use that list to populate each form as well as validate and perform the inserts.

 

But, taking the problem as you have just stated it, this is the approach you could take. First, name your fields as an array - BUT - use the database field name as the index.

 

Example Form:

<input type="hidden" name="table" value="someTable" />
Activity: <input type="text" name="fields['activity']" />
Hours: <input type="text" name="fields['hours']" />
Description: <input type="text" name="fields['descr']" />

 

Then in the processing code, do something similar to this:

//Retrieve table name
$table_name = mysql_real_escape_string(trim($_POST['table']));
//Parse all "fields" for db insert
$fields_ary = array();
$values_ary = array();
foreach($_POST['fields'] as $field => $value)
{
    $fields_ary[] = "`" . mysql_real_escape_string(trim($field)) . "`";
    $values_ary[] = "'" . mysql_real_escape_string(trim($value)) . "'";
}

//Create the query
$fields_str = implode(',', $fields_ary);
$values_str = implode(',', $values_ary);
$query = "INSERT INTO `{$table_name}` ({$fields_str}) VALUES ({$values_str})";