note 62227 deleted from function.mysql-query by philip
| From: | philip@php.net | Date: | Fri, 04 Sep 2015 19:20:09 +0000 |
| Subject: | note 62227 deleted from function.mysql-query by philip | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-203645@lists.php.net to get a copy of this message | ||
Note Submitter: brjann at no dot gmail dot spam dot com
----
Using mysql 4 w/o stored procedures can become quite tedious, especially when writing a lot of
standard sql-statements all the time.
These two functions, standardSQLInsert and standardSQLUpdate, handle most of my uncomplex cases of
updating and inserting into tables. Note the use of the quote_smart function, described at http://php.net/mysql_real_escape_string, making
all queries safe.
<?php
function standardSQLInsert($strTableName, $arrValuePairs){
$strSeparator = '';
$strCols = '';
$strValues = '';
foreach ($arrValuePairs as $strCol => $strValue) {
$strCols = $strCols.$strSeparator.$strCol;
$strValues = $strValues.$strSeparator.quote_smart($strValue);
$strSeparator = ',';
}
mysql_query("INSERT INTO $strTableName ($strCols) VALUES($strValues)");
}
function standardSQLUpdate($strTableName, $arrValuePairs, $arrConditionPairs){
$strSeparator = '';
$strSetStatements = '';
$strUpdateConditions = '';
foreach ($arrValuePairs as $strCol => $strValue){
$strSetStatements = $strSetStatements.$strSeparator.$strCol.'='.quote_smart($strValue);
$strSeparator = ',';
}
$strSeparator = '';
foreach ($arrConditionPairs as $strCol => $strValue){
$strUpdateConditions =
$strUpdateConditions.$strSeparator.$strCol.'='.quote_smart($strValue);
$strSeparator = ' AND ';
}
$strUpdateConditions = '('.$strUpdateConditions.')';
mysql_query("UPDATE $strTableName SET $strSetStatements WHERE $strUpdateConditions");
}
//example
$arrValuePairs = array('Col1' => 'Value1', 'Col2' =>
'Value2');
$arrConditionPairs = array('Col3' => 'Value3', 'Col4' =>
'Value4');
standardSQLInsert('mytable',$arrValuePairs);
//yields "INSERT INTO mytable (Col1,Col2) VALUES ('Value1','Value2')"
standardSQLUpdate('mytable',$arrValuePairs,$arrConditionPairs);
//yields "UPDATE mytable SET Col1 = 'Value1', Col2 = 'Value2' WHERE (Col3 =
'Value3' AND Col4 = 'Value4')"
?>