auto insert all form (or other array) vars to table class

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
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

auto insert all form (or other array) vars to table class

Post by Burrito »

I was bored today so I wrote up a class that will automatically insert all of your form fields (from a post) to your table (assuming the fields have the same names). You could use any kind of associative array in place of $_POST as well. You can also run free queries using the same instance.

**Notes: I used strtotime() to determine if the field is a date field. This causes problems for dates older than 1970 or something (on windows...not sure about *nix). You could probably write some fancy regex to determine the date....but I like quick and I love dirty so this is what you get. This also assumes that you have a key field on your db called "id".

Code: Select all

<?php
////////////////////////////////////////////////////////////////////////////////////////////////////////
//										Query class by Burrito											
//											11-10-2005													
//																										
//					FOR $arr USAGE, ARRAY KEY NAMES MUST MATCH TABLE FIELD NAMES EXACTLY				
//																										
//									$myQuery =& new Query();											
//										$arr usage example:												
//	$myQuery->queryRunner($arr(array),$type(string (insert||update)),$table(string));					
//	$myQuery->queryRunner($_POST,"insert","someTable"); // sets to insert all values from $_POST[] Array
//							$myQuery->executeQuery(); // executes above ^^								
//							echo $myQuery->numorid; // displays id of newly inserted record				
//																										
//										free usage example:												
//					$myQuery->queryRunner($query(string),$type(string (free)))							
//				$myQuery->queryRunner("select * from someTable","free"); // sets free query to execute	
//							$myQuery->executeFreeQuery(); // executes above^^							
//					echo $myQuery->numorid; // displays number of rows returned or new id				
////////////////////////////////////////////////////////////////////////////////////////////////////////
class Query
{
	var $qry;
	var $qrytype;
	var $result;
	var $numorid;
	var $tablename;
	function Query()
	{
	}
	// queryRunner method to determine what type of query is being done (free or $arr) and what type...burrito
	function queryRunner($qry,$qrytype='insert',$tablename=FALSE)
	{
		$this->qry = $qry;
		$this->qrytype = $qrytype;
		if($tablename)
			$this->tablename = $tablename;
	}
	//executeQuery method used to execute $arr queries...burrito
	function executeQuery()
	{
		switch(strtolower($this->qrytype))
		{
			case "insert":
			$mType = TRUE;
			$query = "insert into ".$this->tablename." (";
			break;
			case "update":
			$mType = FALSE;
			$query = "update ".$this->tablename." set ";
			break;
		}
		if($mType)
		{
			foreach($this->qry as $key => $val)
			{
				if(strtolower($key) != "id")
					$query .= $key.",";
			}
			$query = substr($query,0,strlen($query) - 1);
			$query .= ") values (";
			foreach($this->qry as $key => $val)
			{
				if(strtolower($key) != "id")
				{
					if($this->dataType($val) == "string")
						$query .= "'".mysql_real_escape_string($val)."',";
					if($this->dataType($val) == "date")
						$query .= "'".date("Y-m-d",strtotime($val))."',";
				}
			}
			$query = substr($query,0,strlen($query) - 1);
			$query .= ")";
			mysql_query($query)
				or die("This Query: \"".$query."\" Died. Error returned was ".mysql_error());
			$this->numorid = mysql_insert_id();
		}
		else
		{
			foreach($this->qry as $key => $val)
			{
				if(strtolower($key) != "id")
					$query .= $key." = '".($this->dataType($val) == "string" ? mysql_real_escape_string($val) : date("Y-m-d", strtotime($val)))."',";
			}
			$query = substr($query,0,strlen($query) - 1);
			$query .= " where id = ".$this->qry['id'];
			mysql_query($query)
				or die("This Query: \"".$query."\" Died. Error returned was ".mysql_error());
			$this->numorid = $this->qry['id'];
		}
	}
	// dataType method to determine if date or string for insert...burrito
	function dataType($val)
	{
		preg_match("/[a-z]/i",$val,$matches);
			if(isset($matches[0]))
				return "string";
		if($val != "")
		{
			if(strtotime($val) > 0)
				return "date";
		}
		else
			return "string";
	}
	// executeFreeQuery method used to execute any free queries...burrito
	function executeFreeQuery()
	{
		if(strtolower($this->qrytype) != "free")
			die("You Must Enter \"free\" For Your Second Argument For Free Queries");
		else
		{
			$this->result = mysql_query($this->qry)
								or die("This Query: \"".$this->qry."\" Died.  Error returned was: ".mysql_error());
			preg_match("/(.*?)\s.*?/",$this->qry,$matches);
			$qtype = $matches[1];
			$this->numorid = ($qtype == "select" ? mysql_num_rows($this->result) : ($qtype == "insert" ? mysql_insert_id() : "Not Valid For This Query Type"));
		}
	}
}
?>
sample usage:

Code: Select all

<?
mysql_connect("host","username","password");
mysql_select_db("database");
require_once("query.class.php");
if(isset($_POST['string']))
{
	$myQuery =& new Query();
	$myQuery->queryRunner($_POST,"insert","someTable");
	$myQuery->executeQuery();
	echo $myQuery->numorid;
	
	$myQuery->queryRunner("select * from `table`","free");
	$myQuery->executeFreeQuery();
	echo "total number of rows:".$myQuery->numorid;
	while($row = mysql_fetch_assoc($myQuery->result))
		echo $row['string']."<br>";
}
?>
<html>
<head>
	<title>query class test</title>
</head>
<body>
<form method="post">
some string here:<input type="text" name="string"><br>
some date here:<input type="text" name="date"><br>
<input type="submit">
</form>
</body>
</html>
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Here's my similar snippet in function form - viewtopic.php?t=30683&highlight=

Nice work Burrito 8)
User avatar
AndrewBacca
Forum Commoner
Posts: 62
Joined: Thu Jan 30, 2003 10:03 am
Location: Isle of Wight, UK

Post by AndrewBacca »

nice, ive been tring to work out how to do something like this for a very long time :)

thanks
Post Reply