Tuesday, June 2, 2009

PHP function for inserting or updating row into database table


Mostly any web development will require to insert or update form data into a database.

The number of fields and names of form fields will vary in each and every form in a website.

And, sometimes we need to insert the data and sometimes we need to update the data based on whether the key field is already available or not.

So, we had to write many different queries to insert/update data in many forms.

Ultimately, it will take lot of time and effort.

Below function written in php will help to save time, as we need not explicitly write the form names. Only thing is you have to give the form field names exactly same as database table field names.



The below function will insert the named $fields of the given $post array into table $table_name if $id is null.

It will update the existing row if $id is not null.

The $id of the row is always returned.

function insert_or_update_row( $table_name, $id, $fields, $post )
{
if ( $id === null )
{
// insert

$sql = "INSERT INTO $table_name (".join(", ",$fields).")
VALUES ('".join("','", map( $fields, sql_field, $post ) )."')";

}
else
{
$sql = "UPDATE $table_name SET ".
join( ", ", map( $fields, update_field, $post ) )."
WHERE id = $id";

}
if ( mysql_query( $sql ) )
{
if ($id === null) return mysql_insert_id();
return $id;
}
else
query_error( $sql );
}

function map( $orig, $fn )
{
for ( $i = 2; $i < func_num_args(); $i++ )
$arg_list[] = func_get_arg($i);

$new = array();
if ( $orig )
{
foreach( $orig as $x )
$new[] = $fn($x,$arg_list);
}
return $new;
}

Note that below functions used in map() are built-in php function.

func_num_args — Returns the number of arguments passed to the function.
func_get_arg — Return an item from the argument list.

Find below the sample usage of this function.
insert_or_update_row( "employee_table", $_POST[id],
array( 'id', 'emp_name', 'address', 'title', 'phone', 'enabled' ),
$_POST )) )


More Articles...

No comments:

Search This Blog