note 62227 added to function.mysql-query
| From: | brjann at no dot gmail dot spam dot com | Date: | Wed, 22 Feb 2006 22:07:12 +0000 |
| Subject: | note 62227 added to function.mysql-query | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-104248@lists.php.net to get a copy of this message | ||
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')"
?>
----
Server IP: 129.16.214.50
Probable Submitter: 130.238.5.5
----
Manual Page -- http://www.php.net/manual/en/function.mysql-query.php
Edit -- http://master.php.net/manage/user-notes.php?action=edit+62227
Delete: added to the manual -- http://master.php.net/manage/user-notes.php?action=delete+62227&report=yes&reason=added+to+the+manual
Delete: bad code -- http://master.php.net/manage/user-notes.php?action=delete+62227&report=yes&reason=bad+code
Delete: spam -- http://master.php.net/manage/user-notes.php?action=delete+62227&report=yes&reason=spam
Delete: useless -- http://master.php.net/manage/user-notes.php?action=delete+62227&report=yes&reason=useless
Delete: non-english -- http://master.php.net/manage/user-notes.php?action=delete+62227&report=yes&reason=non-english
Delete: already in docs -- http://master.php.net/manage/user-notes.php?action=delete+62227&report=yes&reason=already+in+docs
Delete: other reasons -- http://master.php.net/manage/user-notes.php?action=delete+62227&report=yes
Reject -- http://master.php.net/manage/user-notes.php?action=reject+62227&report=yes
Search -- http://master.php.net/manage/user-notes.php