MySQL query builder.

[kickassamd]

Was wondering if anyone has any examples of a query builder...

 

public function compileInsertQuery($table, $data, $autoClean = true)
{
	// Valid data
	if (is_array($data))
	{
		$fields = "";
		$values = "";
		foreach ($data as $key => $value)
		{
			$fields .= "{$this->_colDelimiters['left']}{$key}{$this->_colDelimiters['right']}, ";
			if ($autoClean)
			{
				$values .= "{$this->_strDelimiters['left']}{$this->cleanString($value)}{$this->_strlDelimiters['right']}, ";
			}
			else
			{
				$values .= "{$this->_strDelimiters['left']}{$value}{$this->_colDelimiters['right']}, ";
			}
		}

		// Strip last , from string
		$fields = preg_replace("/, $/", "", $fields);
		$values = preg_replace("/, $/", "", $values);

		$query = "INSERT INTO {$this->addTablePrefix($table)} ({$fields}) VALUES({$values})";
		return $query;
	}
}

 

Is mine but i was wondering if anyone knows of anyways to improve it etc.

[jvrothjr]

this a dynamic building query function? If so I have a function that builds based on field name that contain data.

 

You pass the table name then it builds the insert query based on if the [fieldname] variable is $_POST[varname].

 

if not data is passed for that field it drops that field from the insert statement.

 

[kickassamd]

Sure id love to see how others do it.

[jvrothjr]

Now the Variables methed is POST. Only thing needed passed in the table name this looks at the field names of the table to build the query string then pass the query back to the calling function.

Function InsertValues($table) {
	$qst1 = 0; // Record Count Field Name
	$chk1 = 0; // Loop Check Field Name
$qst2 = 0; // Record Count Field Value
// Query table for all records
$querystring = "Select * from ".$table;
// Begin creation of insert qeury string
$QSPI = "Insert into ".$table." (";
// Get results of field names 	
$result=mysql_query($querystring);
// Loop thru field names
while(($field = mysql_fetch_field($result)))
{
// If field Not Null the continue
	IF ($_POST[$field->name] != "") {
// Set counter		 
		$qst1++; 
// Set a variable as array and set value to field name
		$QS1[$qst1] = $field->name;
// Set temp variable holder to field value			
		$fn1 = $_POST[$field->name];
// Check value if it is on/off set values to 1/0 this is used for checkboxs			
		if ($_POST[$field->name] == "on") {$fn1 = "1";}
		if ($_POST[$field->name] == "off") {$fn1 = "0";}	
// Set Second Array value to value
		$QS2[$qst1] = $fn1;
	}
}
// loop thru first Array to set the field names	
for ($qstL1 = 0; $qstL1 <= $qst1; $qstL1++) {
	if ($QS1[$qstL1] != '') {
		if ($chk1 == 0) {
			$tmpqs1 = $QS1[$qstL1];
			$chk1 = 1;
		} else {
			$tmpqs1 = $tmpqs1.",".$QS1[$qstL1];
		}
	} 
}
// Add to query string
$QSPI = $QSPI.$tmpqs1.") values(";
// loop thru second Array to set the vales for the fields
for ($qstL2 = 0; $qstL2 <= $qst1; $qstL2++) {
	if ($QS2[$qstL2] != '') {
		$chkstr = substr($QS1[$qstL2], 0, 4);
		$chkstr1 = substr($QS1[$qstL2], strlen($QS1[$qstL2]) - 4, 4);
		if ($chk2 == 0) {
// if field name starts with date call the date function to set the format of the date correctlly
			if ($chkstr == "date") {$QS2[$qstL2] = input2date($QS2[$qstL2]);} 
			if ($chkstr1 == "Date") {$QS2[$qstL2] = input2date($QS2[$qstL2]);} 
			$tmpqs2 = "'".$QS2[$qstL2]."'";
			$chk2 = 1;
		} else {
// Function to format date
			if ($chkstr == "date") {$QS2[$qstL2] = input2date($QS2[$qstL2]);} 			 
			if ($chkstr1 == "Date") {$QS2[$qstL2] = input2date($QS2[$qstL2]);} 			 
			$tmpqs2 = $tmpqs2.",'".$QS2[$qstL2]."'";
		}
	} 
}
// Complete the definition of the query string
$QSPI = $QSPI.$tmpqs2.")";
// Pass the completed query string back to the calling function
return $QSPI;
}