Inserting POST data to a database

[norbie]

Hi guys,

 

I'm about to produce quite a large booking form that needs to insert data into a mysql database when it has been submitted. It's pretty simple stuff, but there will be around 40-50 fields to enter into the database and I want to check that the code I would normally use is the most efficient way of doing it.

 

I would normally use code like this:

 

<?

$field1 = strip_tags($_POST[field1];
$field2 = strip_tags($_POST[field2];
$field3 = strip_tags($_POST[field3];
$field4 = strip_tags($_POST[field4];
$field5 = strip_tags($_POST[field5];

mysql_query("INSERT INTO `table` (`id` ,`field1` ,`field2` ,`field3` ,`field4` ,`field5`) VALUES ('' , '" . $field1 . "', '" . $field2. "', '" . $field3 . "', '" . $field4 . "', '" . $field5 . "')");

?>

 

field1, field2 etc are all just random names I have chosen for this example. This code works but when producing for say 50 fields, all with different names it can be very tedious to program.

 

I was wondering if there is a more efficient way that perhaps grabs all POST data and puts it all in the database, rather than having to specify each field name and where it goes...

 

Thanks for your time.

[PFMaBiSmAd]

You should make a list of the field names in an array so that you can loop through the array to get the expected names so that you can both validate the correct form data and you can then dynamically build the query string, putting the correct filed names and correct pieces of data in it.

 

We would also ask, why you are putting so many columns/fields in a table, that usually indicates a bad table design.

[norbie]

Hi PFMaBiSmAd,

 

Thanks for your reply. That code snippet is just an example, the data would be stored across multiple tables. The form would be capturing a user's personal details such as name, address, contact details etc which would all go in a "customer" table, with some more data such as their job role and company type (drop down box in form used as a lookup from other tables) then stored in the "customer" table as an ID/integer so that it is normalised.

 

You mentioned putting the field names into an array, can you provide a short example of how this would work? I presume I create an array with the field names, this loops through to process the POST data, but how does this create the mysql query string?

 

Many thanks.

[kickstart]

Hi

 

Basic example, asuming the form fields have the same names as the database fields:-

 

$Fields = array("FieldName1","FieldName2","FieldName3);
$FieldValues = '';

for ($Fields as $Field)
{
$FieldValues .= (($FieldValues != '') ? ',' : '')."'".$_POST[$Field]."'";
}

$sql = 'INSERT INTO SomeTable ('.implode(',',$Fields).') VALUES ('.$FieldValues.')';

 

Of course you could make it more complex by using a multi dimensional array to store other field info (such as a marker as to how to validate it, and different names for the database field and form field).

 

All the best

 

Keith