Simplify your query easily

It’s always boring and time consuming writing long INSERT/UPDATE query. Generally when you need to insert a form data with a large number of fields into a single table it does not make any good sense to write the full query manually. For example “INSERT into tbl set name = ‘$_POST[name]‘ , email = ‘$_POST[email]‘ , ….. may be 30 fields! So how can you minimize your effort?

I have written a very simple PHP function for this:

<?php
function genquery($table, $data, $mode = 'insert into', $condition = '', $raw = '') {
	$res = mysql_query("select * from $table limit 1");

	$field_arr = array();
	for($i=0; $i < mysql_num_fields($res); $i++) {
		$field_arr[] = mysql_field_name($res, $i);
	}
	mysql_free_result($res);

	$qstr = $mode." ".$table." set ";
	$arr = array();
	foreach($data as $k => $v) {
		if(!in_array($k, $field_arr)) continue;
		$arr[] = $k." = '".$v."'";
	}
	$qstr .= implode(', ', $arr);

	$qstr .= ' ' . $raw;

	if($mode == 'update' && $condition != ''){
		$qstr .= ' where ' . $condition;
	}
	return $qstr;
}
?>

You must follow a simple way for using this function. The name of the input fields in the form should be same as the fields in the table of your database. I assume your form will be submitted in POST format. In fact a form with many fields is always submitted in post method.

Here is how to use this function:

<?php
include("qfn.php");
if(isset($_POST['submit'])){  // if the form is submitted

$q = genquery("tableName", $_POST, "INSERT INTO", '', '');
mysql_query($q);
}
?>

For updating use:

$q = genquery("tableName", $_POST, "UPDATE", " id = '$_SESSION[id]' ", '');
// the 4th parameter is the condition for update. Use your own condition.

You can use more than one condition like:

$q = genquery("tableName", $_POST, "UPDATE", " id = '$_SESSION[id]' AND uid = '$_SESSION[uid]' ", '');

The 5th parameter is for raw values like

"entry_date = CURDATE(), status = '1' "

You can use the 5th parameter both for INSERT and UPDATE query. But the 4th parameter is only for UPDATE query.

Hope this will help you a lot and save your time & energy.

Download the php file in zip format.

Related posts:

1. Using Dreamweaver Spry validation with jQuery ajax form plugin
2. HAVING clause in CakePHP find query
3. How to get currently logged on windows username in PHP and Javascript