Generate INSERT/REPLACE query automatically from form

Small, short code snippets that other people may find useful. Do you have a good regex that you would like to share? Share it! Even better, the code can be commented on, and improved.

Moderator: General Moderators

Post Reply
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Generate INSERT/REPLACE query automatically from form

Post by JayBird »

Hi guys,

I created this function to help me save time creating queries and inserting data into databases, especially on the type of systems where users can add and edit data, processing this data can be a pain in the ass.

Ill guide you through how to use the functions.

Firstly, lets take an example

Create the follwoing table

Code: Select all

Table             Create Table                                                                                                                                                                                                                                                                                             
----------------  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
personal_details  CREATE TABLE `personal_details` (                                                                                                                                                                                                                                                                        
                    `id` int(2) NOT NULL auto_increment,                                                                                                                                                                                                                                                                   
                    `name` varchar(255) default NULL,                                                                                                                                                                                                                                                                      
                    `address` varchar(255) default NULL,                                                                                                                                                                                                                                                                   
                    `telephone` varchar(255) default NULL,                                                                                                                                                                                                                                                                 
                    `favourite_colour` varchar(255) default NULL,                                                                                                                                                                                                                                                          
                    PRIMARY KEY  (`id`)                                                                                                                                                                                                                                                                                    
                  ) ENGINE=InnoDB DEFAULT CHARSET=latin1
This is a very simplified table, just for demonstration purposes.

Now, you want to create a form to allow a user to enter the data like so...

Code: Select all

<form name=&quote;form1&quote; id=&quote;form1&quote; method=&quote;post&quote; action=&quote;&quote;>
  <input name=&quote;name&quote; type=&quote;text&quote; id=&quote;name&quote; /><br />
  <input name=&quote;addrees&quote; type=&quote;text&quote; id=&quote;name&quote; /><br />
  <input name=&quote;telephone&quote; type=&quote;text&quote; id=&quote;name&quote; /><br />
  <input name=&quote;favourite_colour&quote; type=&quote;text&quote; id=&quote;name&quote; />
</form>
The trick with the form is to name your fields exactly the same as the field names in the database.

Now, here are the 2 functions i created.

Code: Select all

// GET TABLE COLUMNS FUNCTION
// This funtion takes 1 arguement and returns an array containing ALL the column names in the chosen table
// 		$table_name		= the name of the table we want the columns returned from
//	
// EXAMPLE USAGE:
//		$filter_fields = get_db_columns(&quote;table_name&quote;);

function get_db_columns($table_name) {
	$query = &quote;DESCRIBE &quote;.$table_name;
	$links = mysql_query($query) or die(mysql_error());
	while($line = mysql_fetch_array($links, MYSQL_ASSOC)) {
			$filter_fieldsї] = $lineї'Field'];
	}
	return $filter_fields;
}


// INSERT (or REPLACE) DATA FUNCTION
// This function takes 3 arguments and returns a vaild SQL query ready to execute - INSERT or REPLACE
// 		$form_data 		= all data posted from the form
// 		$table_name 	= name of the table we want to insert the data into
// 		$action			= INSERT or REPLACE
//
// EXAMPLE USAGE:
//		insert_form_data($_POST, &quote;test_table&quote;, $action);
//
// NOTES:
// No checking of correct data type is done within this function, this should be done
// before passing the data to this function. This feature may be added at a later date.
//
// VERSION HISTORY:
// 0.1 15/02/2005 - First version released
//
// CREDITS
// Mark Beech for the ingenious idea )
// Feyd for support and brainstorming and general know-all

function insert_form_data($form_data, $table_name, $action) {

	$filter = get_db_columns($table_name);

	$sql = '';

	foreach ($form_data as $key => $value) {

		if(in_array($key, $filter)) {

			$sql .= (!empty($sql)?', `':'`') . $key .'` = ' . (!is_numeric($value) && empty($value)?'NULL':&quote;'$value'&quote;);

		}

	}

	$sql = $action.' INTO `' . $table_name . '` SET ' . $sql;

	return $sql;

}
Now, when your form is submitted, all you need to do on the processing page is the following 2 lines to put the data into the database

Code: Select all

$sql = insert_form_data($_POST, "personal_details", "INSERT");
	
mysql_query($sql) or die(mysql_error());
Simple eh!?


Just a couple of differences if you are editing data. Populate your form as you would normally with data from the database, but dont forget to include a hidden filed wioth the id of the data you are editing.

Then on the processing page you just need to do the following

Code: Select all

$sql = insert_form_data($_POST, "personal_details", "REPLACE");
	
mysql_query($sql) or die(mysql_error());
From this example, the time savings are there, but obviously the more fields you have in your tables, the more of a time saving the will produce for you.

I find it really useful, as i dont have to typ any of the queries myself, i just throw all the $_POST data into the function, and it returns a valid query.

Be aware that you will have to do your own data validation before passing the form data to the function i.e. if you are expecting a integer for a specific field, check this is what has actually been submitted.

This is the first verison of the function, and i hope to include more features when i get time.

Hope someone finds this useful.

Thanks

Mark
Last edited by JayBird on Fri Jun 03, 2005 3:31 am, edited 1 time in total.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Updated the user guide :!:
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Looks good 8)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Nice idea .. and I like the implementation so far .. but ..
The trick with the form is to name your fields exactly the same as the field names in the database.
That is a security issue that would be a big help to a hacker trying some sort of SQL injection attack.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Yes, that could be an issue. Any suggestions welcome to make it more secure?!
onion2k wrote:Nice idea .. and I like the implementation so far .. but ..
The trick with the form is to name your fields exactly the same as the field names in the database.
That is a security issue that would be a big help to a hacker trying some sort of SQL injection attack.
R0d Longfella
Forum Newbie
Posts: 20
Joined: Fri Apr 08, 2005 7:17 am

Post by R0d Longfella »

Use mysql_escape_string, that makes it impossible to insert any mysql code. Next when you display the values from the database use htmlspecialchars, which makes it impossible to insert any html code. (Like non-existing images, which src makes the user post a message on an openBB forum)
programmermatt
Forum Commoner
Posts: 65
Joined: Tue Mar 15, 2005 5:03 pm
Contact:

Post by programmermatt »

When my form has fieldnames that I am updating, I always just do something similar to this:

Code: Select all

<form name=&quote;form1&quote; id=&quote;form1&quote; method=&quote;post&quote; action=&quote;&quote;>
  <input name=&quote;formїname]&quote; type=&quote;text&quote; id=&quote;name&quote; /><br />
  <input name=&quote;formїaddrees]&quote; type=&quote;text&quote; id=&quote;name&quote; /><br />
  <input name=&quote;formїtelephone]&quote; type=&quote;text&quote; id=&quote;name&quote; /><br />
  <input name=&quote;formїfavourite_colour]&quote; type=&quote;text&quote; id=&quote;name&quote; />
</form>

Code: Select all

foreach ($_POST['form'] as $k=>$v ) {
   $sql .= (!empty($sql) ? ', ' : '') . "{$k}='{$v}'";
}
mysql_query("insert into table set ".$sql);
Hope that might help you in writing your function.


And writing this out just made me think of something, does anyone know if forms can have multi-dimensional arrays, i know that you can:

Code: Select all

<input name=&quote;formїfavourite_colour]&quote; type=&quote;text&quote; id=&quote;name&quote; />
but can you go:

Code: Select all

<input name=&quote;formїtablename]їfavourite_colour]&quote; type=&quote;text&quote; id=&quote;name&quote; />
I never tried it before.

edit: yes, yes you can.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Here is the general thought.. Offcourse it becomes much more compliated...

Code: Select all

$columns = array();
$columns[] = array('name' => 'person_id', 'caption' => 'ID');
$columns[] = array('name' => 'surname', 'caption' => 'Surname');
...

$i = 0;
foreach($columns as $column)
{
  echo "{$column['caption']} : <input type='text' name='{$i}' />";
  ++$i;
}
And when it's posted back

Code: Select all

$sql1 = "INSERT INTO $table (";
$sql2 = ") VALUES (";
$i = 0;
foreach($columns as $column)
{
  $sql1 .= "{$column['name']}, ";
  $sql2 .= "'" . mysql_real_escape_string($_POST[$i], $db) . "', ";
  ++$i;
}
$sql1 = rtrim($sql1, ", ");
$sql2 = rtrim($sql2, ", ");
$sql = $sql1 . $sql2 . ")";

echo "<b>$sql</b>";
Post Reply