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=latin1Now, you want to create a form to allow a user to enter the data like so...
Code: Select all
<form name="e;form1"e; id="e;form1"e; method="e;post"e; action="e;"e;>
<input name="e;name"e; type="e;text"e; id="e;name"e; /><br />
<input name="e;addrees"e; type="e;text"e; id="e;name"e; /><br />
<input name="e;telephone"e; type="e;text"e; id="e;name"e; /><br />
<input name="e;favourite_colour"e; type="e;text"e; id="e;name"e; />
</form>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("e;table_name"e;);
function get_db_columns($table_name) {
$query = "e;DESCRIBE "e;.$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, "e;test_table"e;, $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':"e;'$value'"e;);
}
}
$sql = $action.' INTO `' . $table_name . '` SET ' . $sql;
return $sql;
}Code: Select all
$sql = insert_form_data($_POST, "personal_details", "INSERT");
mysql_query($sql) or die(mysql_error());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());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