phpInsertUpdate

<?php
/************************************************************/
/* Created by Jason S. Mayoff  ([email protected]			*/
/* Copyright 2001											*/				
/* Contains													*/
/*		1. phpInsert() (automatically inserts data into db)	*/
/*			(Similar to <CFINSERT>)							*/
/*															*/
/*															*/
/*		2. phpUpdate() (automatically updates db record) 	*/
/*			(Similar to <CFUPDATE>)							*/
/*															*/
/************************************************************/


/* 	
	ABOUT THESE FUNCTIONS:
	I have been using ColdFusion for quite some time, and have grown accustomed to some of that 
	language's built-in functions.  Two of the best, time-saving ones in CF are called <CFUPDATE>
	and <CFINSERT>.  Essentially, they allow you to, in a single line of code, add a new record to, 
	or  update your database. 
	
	These two functions,  phpInsert() and phpUpdate() are my best approximation of the CF functions, 
	with what I believe is an improvement.  The functions only act upon form fields, whose names have
	the prefix FORM_.  This allows you to easily exclude certain fields from the functions' actions.
	
	HOW TO USE THESE FUNCTIONS:
	*The functions must be called after a form has been submitted.  

	*The form field names that you want the functions to act upon must have the prefix FORM_ 
		- All other form fields will be ignored by the functions
			ie: <INPUT TYPE="text" NAME="FORM_name" VALUE=""> (This gets processed)
			ie: <INPUT TYPE="text" NAME="email"> (This does not get processed)

	*The column names corresponding to the form fields must already exist in $tablename in 
		your MySQL database.  
			- If your column name is email, the form field must be FORM_email
			- or conversely, the form field FORM_firstName will be inserted (or updated) 
				into the field firstName, in $tablename.

	*When calling the function, you must pass them the name of the table you want updated, or added to
			ie: phpInsert(tablename)
	
	*For PHPUpdate, you must include as a hidden (or other) field, the primary key of the record
		you want updated.
			ie: <INPUT TYPE="hidden" NAME="FORM_id" VALUE="1">
		
	LIMITATIONS:
	All form fields you want acted upon MUST have the prefix FORM_, including the primary 
	key field for PHPupdate.  The column names in $tablename must already be created.  
		
	These functions have been tested on PHP4 and MySQL 3.23, running on Windows ME and Linux.  
	I have no clue whether they will work with your setup.

	If you have problems, you can try contacting me at [email protected], but there's no guarantee
	I'll respond to your email.  I make no guarantees that these will work, and I can not be held 
	responsible for any problems they cause you.  The code is right here for you to see, so go ahead
	and make any changes you'd like.  If you find a problem with them, or if you think you have a better 
	way of doing this, please let me know.  
		
*/



/*
** Function PHPinsert automatically inserts form fields into a database table.
** Takes all "FORM_" fields from the form and creates a record in the specified table.
** Function IN -> $tablename; The name of the table you want to insert the data into
*/


function phpInsert($tablename)
	{
			
	global $HTTP_POST_VARS;  # Make variable global
	$keys = "";
	$values = "";
	# List through all form variables
	foreach($HTTP_POST_VARS as $key=>$val) 
		{
			# If the prefix is FORM_ then 
			if (substr($key, 0, 5) == "FORM_")	
				{
					# Create the keys and values line 
						$keys .= substr($key, 5) . ", ";
						
						$query = mysql_query("SELECT " . substr($key, 5) . " FROM " . $tablename)
	or die ("Invalid Query: phpInsert");
						$test = mysql_fetch_field ($query);
						$isNumeric = $test->numeric;
											
						if ($isNumeric == 1)
							{
							$values .= $val . ", ";
							}
						else 
							{
						$values .= "'" . $val . "', ";
							}			
						
						
				} # end of IF prefix is FORM_

		} #end of foreach loop 

# Remove the trailing "," on the keys and values line
	$keys = substr($keys, 0, -2);
	$values = substr($values, 0, -2);

# Construct SQL statement
	$SQL = "INSERT INTO " . $tablename . " (";
	$SQL .= $keys . ")";
	$SQL .= "VALUES (";
	$SQL .= $values . ")";

	$query = mysql_query($SQL)
	or die ("Invalid Query: phpInsert (library.php)");
	
	global $insertedID;
	$insertedID = mysql_insert_id();
	
	}
	
	

/*
** Function PHPupdate automatically updates form fields in a database table.
** Takes all "FORM_" fields from the form and updates a record in the specified table.
** In order for this to work, you must include the primary key field and it's value 
** (usually in a hidden field) also with the prefix FORM_.  
** ie: if $tablename's primary key field is ID then your hidden field should be:
** 			<INPUT TYPE="hidden" NAME="FORM_ID" VALUE="whatever_value">
** Function IN -> $tablename; The name of the table you want this function to act upon
*/

function phpUpdate($tablename)
	{

	global $HTTP_POST_VARS;  # Make variable global
	# List through all form variables
	foreach($HTTP_POST_VARS as $key=>$val) 
		{
		
			# If the prefix is FORM_ then 
			if (substr($key, 0, 5) == "FORM_")	
				{
					# Do a query to find out if this is the primary key
					$query = mysql_query("SELECT " . substr($key, 5) . " FROM " . $tablename)
	or die ("Invalid Query:phpUpdate: Check for primary key ");
						$test = mysql_fetch_field ($query);
						$isPrimary_key = $test->primary_key;
						if ($isPrimary_key == 1) # Set the primary_key_value but don't add it to SQL 
							{
							$primary_key_value = $val;
							}
						else # If this is not the primary_key_field
							{
	
						# Create the keys = values line 
						# If the field is not numeric then surround with ''
						$query = mysql_query("SELECT " . substr($key, 5) . " FROM " . $tablename)
	or die ("Invalid Query: phpUpdate (isNumeric?)");
						$test = mysql_fetch_field ($query);
						$isNumeric = $test->numeric;
											
						if ($isNumeric == 1)
							{
							$line .= substr($key, 5) . " = " . $val . ", ";
							}
						else 
							{
							$line .= substr($key, 5) . " = '" . $val . "', ";							
							}			
							
							} #End of if (else) this is not the primary_key_field
						
				} # end of IF prefix is FORM_

		} #end of foreach loop 

# Remove the trailing "," on the line
	$line = substr($line, 0, -2);

# Construct SQL statement
	$SQL = "UPDATE " . $tablename . " SET ";
	$SQL .= $line;
	$SQL .= " WHERE ID = ". $primary_key_value;
	
	$query = mysql_query($SQL)
	or die ("Invalid Query: phpUpdate()");

	}



?>