This is a quick way for adding data to a table. It is the same way than PEAR::DB, so if you are working on a server without PEAR, it enables you to keep up with your habits.
<?php
function insertIntoDB($table, $toAdd)
{
$fields = implode(array_keys($toAdd), ',');
$values = implode(array_values($toAdd), ',');
$q = 'INSERT INTO `'.$table.'` ('.$fields.') VALUES ('.$values.')';
$res = mysql_query($q)OR die(mysql_error());
return true;
}
//-- Example of usage
$tToAdd = array('id'=>3, 'name'=>'Yo', 'salary' => 5000);
insertIntoDB('myTable', $tToAdd)
?>
mysql_query
说明
resource mysql_query ( string query [, resource link_identifier] )mysql_query() 向与指定的连接标识符关联的服务器中的当前活动数据库发送一条查询。如果没有指定 link_identifier，则使用上一个打开的连接。如果没有打开的连接，本函数会尝试无参数调用 mysql_connect() 函数来建立一个连接并使用之。查询结果会被缓存。
注: 查询字符串不应以分号结束。
mysql_query() 仅对 SELECT，SHOW，EXPLAIN 或 DESCRIBE 语句返回一个资源标识符，如果查询执行不正确则返回 FALSE。对于其它类型的 SQL 语句，mysql_query() 在执行成功时返回 TRUE，出错时返回 FALSE。非 FALSE 的返回值意味着查询是合法的并能够被服务器执行。这并不说明任何有关影响到的或返回的行数。 很有可能一条查询执行成功了但并未影响到或并未返回任何行。
以下查询语法上有错，因此 mysql_query() 失败并返回 FALSE：
以下查询当 my_col 并不是表 my_tbl 中的列时语义上有错，因此 mysql_query() 失败并返回 FALSE：
如果没有权限访问查询语句中引用的表时，mysql_query() 也会返回 FALSE。
假定查询成功，可以调用 mysql_num_rows() 来查看对应于 SELECT 语句返回了多少行，或者调用 mysql_affected_rows() 来查看对应于 DELETE，INSERT，REPLACE 或 UPDATE 语句影响到了多少行。
仅对 SELECT，SHOW，DESCRIBE 或 EXPLAIN 语句 mysql_query() 才会返回一个新的结果标识符，可以将其传递给 mysql_fetch_array() 和其它处理结果表的函数。处理完结果集后可以通过调用 mysql_free_result() 来释放与之关联的资源，尽管脚本执行完毕后会自动释放内存。
参见 mysql_num_rows()，mysql_affected_rows()，mysql_unbuffered_query()，mysql_free_result()，mysql_fetch_array()，mysql_fetch_row()，mysql_fetch_assoc()，mysql_result()，mysql_select_db() 和 mysql_connect()。
add a note
User Contributed Notes
cedric ___at___ sadai ___dot___ net
05-Jun-2006 11:26
05-Jun-2006 11:26
matt
21-Mar-2006 07:45
21-Mar-2006 07:45
Just realised I posted the wrong functions. Oops!
Here you go....
<?php
function compile_insert_string($data)
{
$field_names = "";
$field_values = "";
foreach ($data as $k => $v)
{
$v = preg_replace( "/'/", "\\'", $v );
//$v = preg_replace( "/#/", "\\#", $v );
$field_names .= "$k,";
$field_values .= "'$v',";
}
$field_names = preg_replace( "/,$/" , "" , $field_names );
$field_values = preg_replace( "/,$/" , "" , $field_values );
return array('FIELD_NAMES' => $field_names,
'FIELD_VALUES' => $field_values,
);
}
//------------------------------------------
// insert_query
//
// Wrapper for an insert query
//------------------------------------------
function insert_query($data, $table)
{
if (!is_array($data) or count($data) < 1)
{
$this->fatal_error("Insert data missing");
}
$insert = $this->compile_insert_string($data);
$query = "INSERT INTO {$table} (".$insert['FIELD_NAMES'].") VALUES (".$insert['FIELD_VALUES'].")";
return $this->query($query);
}
?>
yankee at gmeil dot com
10-Mar-2006 06:07
10-Mar-2006 06:07
Another shorter possibility to print options of an ENUM as <select>-tag:
<?php
$result=mysql_query('SHOW COLUMNS FROM <your table> WHERE field=\'<you column>\'');
while ($row=mysql_fetch_row($result))
{
foreach(explode("','",substr($row[1],6,-2)) as $v)
{
print("<option>$v</option>");
}
}
?>
gconnor at sgi dot com
02-Mar-2006 09:19
02-Mar-2006 09:19
I happily grabbed and used the "mysql_prepare()" function given in the first note above. All is well.
I made a slight tweak so that I could feed in NULL values without getting an empty string (or 0) instead.
// mysql_query() wrapper. takes two arguments. first
// is the query with '?' placeholders in it. second argument
// is an array containing the values to substitute in place
// of the placeholders (in order, of course).
// Pass NULL constant in array to get unquoted word NULL
function mysql_prepare ($query, $phs = array()) {
foreach ($phs as $ph) {
if ( isset($ph) ) {
$ph = "'" . mysql_real_escape_string($ph) . "'";
} else {
$ph = "NULL" ;
}
$query = substr_replace(
$query, $ph, strpos($query, '?'), 1
);
}
return mysql_query($query);
}
Sample function call:
// Check to see if all variables are defined
if ( isset($f_hostid,$f_eventid,$f_owner,$f_comment) ) {
// For numeric values, blank means NULL
if ( $f_eventid=="" ) { $f_eventid = NULL ; }
$result = mysql_prepare(
'UPDATE Hosts SET event_id=?, owner=?, comment=? WHERE id=?',
array( $f_eventid,$f_owner,$f_comment, $f_hostid )
);
if (!$result) {
$message = 'Error while updating: ' . mysql_error() . "<br />\n";
die($message);
}
echo "Update successful. <br />\n" ;
} else {
echo "Missing value. Update failed... check form logic. <br />\n" ;
}
23-Feb-2006 03:11
If, like me, you come from perl, you may not like having to use sprintf to 'simulate' placeholders that the DBI package from perl provides. I have created the following wrapper function for mysql_query() that allows you to use '?' characters to substitute values in your DB queries. Note that this is not how DBI in perl handles placeholders, but it's pretty similar.
<?php
// mysql_query() wrapper. takes two arguments. first
// is the query with '?' placeholders in it. second argument
// is an array containing the values to substitute in place
// of the placeholders (in order, of course).
function mysql_prepare ($query, $phs = array()) {
foreach ($phs as $ph) {
$ph = "'" . mysql_real_escape_string($ph) . "'";
$query = substr_replace(
$query, $ph, strpos($query, '?'), 1
);
}
return mysql_query($query);
}
// sample usage
list($user, $passwd) = array('myuser', 'mypass');
$sth = mysql_prepare(
'select userid from users where userid=? and passwd=?',
array($user, sha1($passwd))
);
$row = mysql_fetch_row($sth);
// successfull username & password authentication
if ($row !== false) {
echo "logging in as '{$row[0]}'!\n";
}
// oops, wrong userid or passwd
else {
echo "Invalid username and password combination.\n";
}
?>
brjann at no dot gmail dot spam dot com
23-Feb-2006 06:07
23-Feb-2006 06:07
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')"
?>
aidan at mcquay dot org
07-Feb-2006 04:03
07-Feb-2006 04:03
Here's a slight revision of --celtics parse sql file function. Just fixed a typo: $sql_line != $sql
<?
function parse_mysql_dump($url, $ignoreerrors = false) {
$file_content = file($url);
//print_r($file_content);
$query = "";
foreach($file_content as $sql_line) {
$tsl = trim($sql_line);
if (($sql_line != "") && (substr($tsl, 0, 2) != "--") && (substr($tsl, 0, 1) != "#")) {
$query .= $sql_line;
if(preg_match("/;\s*$/", $sql_line)) {
$result = mysql_query($query);
if (!$result && !$ignoreerrors) die(mysql_error());
$query = "";
}
}
}
}
?>
Harmor
18-Dec-2005 09:32
18-Dec-2005 09:32
Modification of hipsurgery submission. Here's a utility function that will return an array of a table. Don't forget to connect to the DB before calling this function.
<?php
//Returns an array[columnName] = value of a $table_name
function table_to_array($table_name)
{
$columns = array();
$result_all= mysql_query("SELECT * FROM $table_name");
$result_columns = mysql_query("SHOW COLUMNS FROM $table_name");
//get the columns from the table into $colums[]
while ($columnRow = mysql_fetch_array($result_columns, MYSQL_ASSOC))
{
$columns[] = $columnRow
}
while ($data = mysql_fetch_assoc($result_all, MYSQL_ASSOC))
{
foreach ($columns as $column_name)
{
$array[$column_name] = $data[$column_name];
}
}
return $array;
}
?>
hipsurgery at gmail dot com
06-Nov-2005 02:30
06-Nov-2005 02:30
This function will take the contents of any MySQL table, given only the table name, and return it as an index / associative multi-dimensional array in the form of:
$array[$row_number][$column_name] = $value;
I've found this very useful when you don't want to parse the table's contents in to HTML tags during a mysql_fetch_array() iteration.
<?php
function db_to_array($table_name) {
$cols = array();
$x=0;
$this_row=0;
mysql_connect(HOST,USERNAME,PASSWORD);
@mysql_select_db(DATABASE) or die( "Unable to select database");
$result_all=mysql_query("SELECT * FROM $table_name");
$result_cols = mysql_query("SHOW COLUMNS FROM $table_name");
mysql_close();
$numfields = mysql_num_fields($result_all);
for($i=0;$i<mysql_num_rows($result_cols);$i++)
{
$cols[] = mysql_result($result_cols, $i);
}
while ($data = mysql_fetch_assoc($result_all))
{
if ($x<$numfields)
{
$x++;
}
else
{
$x = 0;
$this_row++;
}
foreach ($cols as $col_name)
{
$array[$this_row][$col_name] = $data[$col_name];
}
mysql_data_seek($result_all, $this_row);
}
return $array;
}
// Test the function...
$test_array = db_to_array("shows");
foreach ($test_array as $outer_key => $single_array)
{
foreach ($single_array as $inner_key => $value)
{
echo "\$test_array[$outer_key][$inner_key] = $value<br />";
}
}
?>
I'm just a hobbyist, so feel free to comment on my code or (worse!) tell me that there's some native PHP function that already does this!
congaz at yahoo dot dk
26-Oct-2005 05:46
26-Oct-2005 05:46
Here's a little trick to help you keep track of MySQL table/column names, and the values you want to insert/select.
I always use constants instead of variables for my table/column names. The script that define the constants can then dynamically be set to initialize different table/column names if needed. However, here comes the trick.
Say you have an Insert statement like this:
<?=
// define(TABLE_DOCUMENT, 'table_name');
// define(COL_DOC_PUBLIC, 'column_name');
// etc....
$sql = "INSERT INTO ".TABLE_DOCUMENT."
(".COL_DOC_PUBLIC.", ".COL_DOC_UPLOAD_TSTAMP.", ".COL_DOC_CREATOR_NAME.") VALUES (".$doc_public.", ".$doc_upload_tstamp.", ".$doc_name.")";
?>
Now, with long insert statements, I find it easy to loose track of which values goes with which column names. If I somehow could use the constants as variable names, keeping track of all that sql mishmash would be quite a lot easier.
As it turns out, constants names can be used as variable variables (not quite the correct definition when we're actually dealing with constants, but what the heck).
So,the sql above could instead be like this:
<?=
${COL_DOC_PUBLIC} = $doc_public;
${COL_DOC_UPLOAD_TSTAMP} = $doc_upload_tstamp;
${COL_DOC_CREATOR_NAME} = $doc_name;
$sql = "INSERT INTO ".TABLE_DOCUMENT."
(".COL_DOC_PUBLIC.", ".COL_DOC_UPLOAD_TSTAMP.", ".COL_DOC_CREATOR_NAME.") VALUES (".${COL_DOC_PUBLIC}.", ".${COL_DOC_UPLOAD_TSTAMP}.", ".${COL_DOC_CREATOR_NAME}.")";
?>
This little trick made things alot easier for me - especially when dealing with extended querys, where you might have to use the same values in severel insert/select statements. Another thing is, that you can wait to use addslashes()/my_sql_real_escape_string until you create the "variable constants" - thus the task of remebering which values have been prepared to be used in an sql-statement has become fool-proof.
Hope somebody finds this usefull...
php at arcannon dot com
02-Oct-2005 06:30
02-Oct-2005 06:30
I believe there is a typo in celtic at raven-blue dot com version with:
if (($sql != "") && (substr($tsl, 0, 2) != "--") && (substr($tsl, 0, 1) != "#")) {
I think you really ment:
if (($tsl != "") && (substr($tsl, 0, 2) != "--") && (substr($tsl, 0, 1) != "#")) {
I changed the $sql to $tsl
celtic at raven-blue dot com
10-Sep-2005 10:03
10-Sep-2005 10:03
Here's a revision of ix at nivelzero -and- thomas at pixur's code. This SQL dump parser fixes the check for comments that was present in the old (ie. a '--' located anywhere in the string would make it ignore that line!), and adds the check for the # comment. That had me thinking.
<?php
function parse_mysql_dump($url, $ignoreerrors = false) {
$file_content = file($url);
$query = "";
foreach($file_content as $sql_line) {
$tsl = trim($sql_line);
if (($sql != "") && (substr($tsl, 0, 2) != "--") && (substr($tsl, 0, 1) != "#")) {
$query .= $sql_line;
if(preg_match("/;\s*$/", $sql_line)) {
$result = mysql_query($query);
if (!$result && !$ignoreerrors) die(mysql_error());
$query = "";
}
}
}
}
?>
thomas -at - pixtur -dot- de
16-Aug-2005 05:14
16-Aug-2005 05:14
a comment on nivelzero's excellent sql-dump-script.
It was excactly what I was looking for. Sadly in my case the sql-dump had dos-style linebreaks. Changing the regex to ...
if(preg_match("/;\s*$/", $sql_line)){
... will make the function working on both platforms. Thanks for the script.
ix at nivelzero dot ro
14-Aug-2005 06:07
14-Aug-2005 06:07
here's a script for parsing a *.sql file (tested only on dumps created with phpMyAdmin) which is short and simple (why do people say "here's a short and simple script" and it has a 100 lines?). the script skips comments and allows ; to be present within the querys
<?php
function parse_mysql_dump($url){
$file_content = file($url);
$query = "";
foreach($file_content as $sql_line){
if(trim($sql_line) != "" && strpos($sql_line, "--") === false){
$query .= $sql_line;
if(preg_match("/;[\040]*\$/", $sql_line)){
$result = mysql_query($query)or die(mysql_error());
$query = "";
}
}
}
}
?>
kagekonjou at gmail dot com
01-Jun-2005 01:06
01-Jun-2005 01:06
Due to the easy ways SQL can be injected into websites and thus virtually ruin a website that is dependant on databasing, I created a 'prepare' function similar to the way sprintf works.
<?php
/* Wildcard Rules
* SCALAR (?) => 'original string quoted'
* OPAQUE (&) => 'string from file quoted'
* MISC (~) => original string (left 'as-is')
*/
function prepare() {
/* MySQL Prepare Function
* By: Kage (Alex)
* KageKonjou@GMail.com
*/
$data = func_get_args();
$query = $data[0];
$tokens = split("[\&\?\~]", $query);
$preparedquery = $tokens[0];
$count = strlen($tokens[0]);
for ($i=1; $i<count($tokens); $i++) {
$char = substr($query, $count, 1);
$count += (strlen($tokens[$i])+1);
if ($char == "&") {
$fp = @fopen($data[$i], 'r');
$pdata = "";
if ($fp) {
while (($buf = fread($fp, 4096)) != false) {
$pdata .= $buf;
}
fclose($fp);
}
} else {
$pdata = &$data[$i];
}
$preparedquery .= ($char != "~" ? mysql_escape_string($pdata) : $pdata);
$preparedquery .= $tokens[$i];
}
return $preparedquery;
}
?>
This function has been stress-tested, and does work. Example use:
$prep_query = prepare("SELECT ?,? FROM ? WHERE '?' LIKE '&'", "lastname", "address", "addressbook", "lastname", "B%");
$prep_query now has the value of "SELECT lastname,address FROM addressbook WHERE 'lastname' LIKE 'B%'"
In essence, as explained in the Wildcard Rules, ? is a quoted string, & is quoted from file, and ~ is raw ('AS-IS'). This function is to be used to make-safe SQL that is touched by a web interface. A main example would be forum pages (Ie. thread.php?fid=12&tid=12345). SQL could be injected at this point, such as " thread.php?fid=12&tid=12345'; DELETE FROM prefix_posts WHERE `id` LIKE '1%' ", which would essentially destroy that forum unless routine backups are made. This function, if used properly, can prevent any type of injections, turning the above injection into " thread.php?fid=12&tid=12345\'; DELETE FROM prefix_posts WHERE \`id\` LIKE \'1%\' ", making it look for thread-id of everything from 12345 to 1%', making it a safe, though broken SQL.
Comments and suggestions are welcome, and use of this function is free and under the Honor System (hoping you give credit where credit is due), since I'm too lazy to tack on a GNU.
wjyong at sh163 dot net
01-May-2005 07:21
01-May-2005 07:21
The following query is not valid as expected:
<?php
$username = 'dicteworld';
$username{4} = '';
$sql = "SELECT * FROM `user` WHERE `User` = '$username'";
print($sql); // Result: SELECT * FROM `user` WHERE `User` = 'dictworld'
$res = mysql_query($query);
$row = mysql_fetch_array($res);
print_r($row);// Result: still return Array(), supposed that the user 'dictworld' exists.
?>
Pay more attention that null string '' is equivalent to '\0',therefore SQL statement above is equivalent to SELECT * FROM `user` WHERE `User` = 'dict\0world',though printing string is right.
jon at websandbox dot net
26-Jan-2005 09:25
26-Jan-2005 09:25
I think it's important to note (for newbies, like me especially) that an empty result is not the same as an error:
<?php
/* 'bar' is an empty table in the db */
$rs = mysql_query("SELECT `foo` FROM `bar`")
if($rs) {
echo mysql_num_rows($rs); //outputs: 0
}
andregodin at gmail dot com
19-Nov-2004 05:03
19-Nov-2004 05:03
Another "dumping" function but with the optional possibility to choose wich field_name to be dumped. "Have Fun and please email me if you do optimization of this code"
<?php
function mysql_format($strTemp){
//Format sql sentence for insert
$bad_chars= array("\\", "'", "\"");
$good_chars = array("\\\\", "''", "\"\"");
return str_replace($bad_chars, $good_chars, $strTemp);
}
function mysql_dump_table(){
/*******************\
* MYSQL_DUMP_TABLE *********************************\
* Paramtres : *
* 1- Table Name *
* 2- Field(s) (in string format) Name to be dumped *
* If empty, all field will be dumped *
\******************************************************/
if (!(func_num_args())) die ("<b>mysql_dump_table</b>: Need At Least A Table Name");
$arg_list = func_get_args();
$arrReturn = "";
$strTblName = mysql_format("`{$arg_list[0]}`");
$strFields = "*";
if (func_num_args() > 1){
$strFields = "";
for ($noArg=1; $noArg<func_num_args(); $noArg++){
if ($strFields) $strFields .= ", ";
$strFields .= "`$arg_list[$noArg]`";
}
}
$result = mysql_query("SELECT $strFields FROM $strTblName") or die ("Incapable d'excuter la requte");
$nbRecord = 0;
while ($row = mysql_fetch_assoc($result)){
$strFieldsNames = "";
$strFieldsValues = "";
foreach ($row as $field_name => $field_value){
if ($strFieldsNames) $strFieldsNames .= ", ";
$strFieldsNames .= "`$field_name`";
if($strFieldsValues) $strFieldsValues .= ", ";
$strFieldsValues .= "'" . mysql_format($field_value) . "'";
}
$arrReturn[$nbRecord++] = "INSERT INTO $strTblName($strFieldsNames) values($strFieldsValues);";
}
mysql_free_result($result);
return $arrReturn;
}
require_once("config_connexion_db_test.php");
/****************
* AUTRE EXEMPLE **********************************************
* Vous pouvez dcider d'afficher quelques champs seulements *
* en spcifiant les champs dsir *
**************************************************************/
$db = mysql_connect(DBTEST_HOST, DBTEST_USER, DBTEST_PASSWORD) or die("Impossible de se connecter : ");
mysql_select_db(DBTEST_NAME, $db);
$result = mysql_dump_table("tbl_produits", "code_produit", "description");
foreach ($result as $sentence){
//Afficher chaque lment du tableau
print "$sentence<br>";
}
mysql_close($db);
//Retourne
/********************************************************
INSERT INTO `tbl_produits`(`code_produit`,`description`) VALUES('produit1', 'don\'t have description');
INSERT INTO `tbl_produits`(`code_produit`,`description`) VALUES('produit2', 'without \"description\" too');
INSERT INTO `tbl_produits`(`code_produit`,`description`) VALUES('produit3', '1\\3 more than before');
...
...
*********************************************************/
?>
PHP is a very nice language!
Andr Godin :)
me at harveyball dot com
11-Sep-2004 04:13
11-Sep-2004 04:13
Just thought id post this as i couldnt find a nice and simple way of dumping data from a mysql database and all the functions i found were way overly complicated so i wrote this one and thought id post it for others to use.
//$link is the link to the database file
//$db_name is the name of the database you want to dump
//$current_time is just a reference of time()
//returns $thesql which is a string of all the insert into statements
function dumpData()
{
global $link,$db_name,$current_time;
$thesql="";
$thesql.="#SQL DATA FOR $mdb_name \n";
$thesql.="#BACK UP DATE ". date("d/m/Y G:i.s",$current_time)." \n";
$result = mysql_list_tables($mdb_name);
while ($row = mysql_fetch_row($result))
{
$getdata=mysql_query("SELECT * FROM $row[0]");
while ($row1=mysql_fetch_array($getdata))
{
$thesql.="INSERT INTO `$row[0]` VALUES (";
$getcols = mysql_list_fields($mdb_name,$row[0],$link);
for($c=0;$c<mysql_num_fields($getcols);$c++)
{
if (strstr(mysql_field_type($getdata,$c),'blob')) $row1[$c]=bin2hex($row1[$c]);
//Binary null fix if ever needed
if ($row1[$c]=="0x") $row1[$c]="0x1";
//delimit the apostrophies for mysql compatability
$row1[$c]=str_replace("'","''",$row1[$c]);
if (strstr(mysql_field_type($getdata,$c),'blob'))
$thesql.="0x$row1[$c]";
else
$thesql.="'$row1[$c]'";
if ($c<mysql_num_fields($getcols)-1) $thesql.=",";
}
$thesql.=");;\n";
}
}
return $thesql;
}
Please note the sql statements are terminated with ;; not a ; this is so when you want to do a multiple query you can tokenise the sql string with a ;; which allows your data to contain a ;
If you want to run the multiple query then use this simple function which i wrote due to not being able to find a decent way of doing it
//$q is the query string ($thesql returned string)
//$link is the link to the database connection
//returns true or false depending on whether a single query is executed allows you to check to see if any queries were ran
function multiple_query($q,$link)
{
$tok = strtok($q, ";;\n");
while ($tok)
{
$results=mysql_query("$tok",$link);
$tok = strtok(";;\n");
}
return $results;
}
mark @t ilisys dot c0m au
14-Jul-2004 12:05
14-Jul-2004 12:05
The default mysql max_allowed_packet setting is in fact 16MB. The gotcha you can fall into is your text field type.
eg:
TEXT Variable-length string up to 65,535 characters long.
MEDIUMTEXT Variable-length string up to 16,777,215 characters long.
LONGTEXT Variable-length string up to 4,294,967,295 characters long.
(corresponding sizes for blob, mediumblob, longblob)
samm at os2 dot ru
19-May-2004 05:15
19-May-2004 05:15
Small correction to the klync post (18-Jan-2004 09:05)
instead
foreach ( explode(";", "$sql_query") as $sql_line) {
its better to use
foreach ( explode(";\n", "$sql_query") as $sql_line) {
to avoid errors on strings lines like:
INSERT INTO `cms_text` VALUES ('test; test');
Predrag Supurovic
01-Dec-2003 08:52
01-Dec-2003 08:52
If you need to execute sevaral SQL commands in a row (usually called batcg SQL) using PHP you canot use mysql_query() since it can execute single command only.
Here is simple but effective function that can run batch SQL commands. Take cere, if string contains semicolon (;) anywhere except as command delimiter (within string expression for example) function will not work.
function mysql_exec_batch ($p_query, $p_transaction_safe = true) {
if ($p_transaction_safe) {
$p_query = 'START TRANSACTION;' . $p_query . '; COMMIT;';
};
$query_split = preg_split ("/[;]+/", $p_query);
foreach ($query_split as $command_line) {
$command_line = trim($command_line);
if ($command_line != '') {
$query_result = mysql_query($command_line);
if ($query_result == 0) {
break;
};
};
};
return $query_result;
}
chris at hotmail dot com
01-May-2003 09:28
01-May-2003 09:28
Windows programmers, keep in mind that although table names in Windows queries are not case sensitive, many *NIX versions of Mysql require the correct table name case (perhaps others as well). So you're better off using the right case from the beginning, in case you ever decide to go with a *NIX server.
davidc at edeca dot net
20-Apr-2003 03:30
20-Apr-2003 03:30
Regarding the idea for returning all possible values of an enum field, the mySQL manual says that "SHOW COLUMNS FROM table LIKE column" should be used to do this.
The function below (presumes db connection) will return an array of the possible values of an enum.
function GetEnumValues($Table,$Column)
{
$dbSQL = "SHOW COLUMNS FROM ".$Table." LIKE '".$Column."'";
$dbQuery = mysql_query($dbSQL);
$dbRow = mysql_fetch_assoc($dbQuery);
$EnumValues = $dbRow["Type"];
$EnumValues = substr($EnumValues, 6, strlen($EnumValues)-8);
$EnumValues = str_replace("','",",",$EnumValues);
return explode(",",$EnumValues);
}
Cavaets:
1) If the LIKE matches more than one column you get the enum from the first, so be careful with the $Column argument
2) You can't have ',' as part of one of the enums (I guess mySQL would escape this, but I haven't tried)
3) If the field isn't an enum you'll get garbage back!
This is just a quick example to show how to do it, some tidying up needs to be done (ie checking if the field is actually an enum) before it is perfect.
09-Apr-2003 03:43
Until this function prohibits them, watch out for SQL comments (--) in your input.
allen a brooker gb net
28-Mar-2003 09:35
28-Mar-2003 09:35
One way to reduce the dangers of queries like the dlete command above that dletes the whole DB is to use limits wherever possible.
EG. If you have a routine that is only deisnged to delete 1 record, add 'LIMIT 1' to the end of the command. This way you'll only lose one record if someone does something stupid.
You should also check all input, especially if it is sent using GET. ie. make sure that $_GET['id'] is not NULL or == "", is a number that is positive, not 0 (generally, I know this doesn't apply to some table types, but it applies to the default) and is within the valid range for that field.
Just don't trust ANY data that is sent to your script.
HTH
Allen
antony%40terian%2Eco%2Euk
27-Mar-2003 11:50
27-Mar-2003 11:50
A useful URL for queries to search for duplicate records is: http://www.onlamp.com/pub/a/onlamp/excerpt/mysqlckbk/index1.html?page=last
php at mereck dot net
20-Jan-2003 12:59
20-Jan-2003 12:59
phpinfo at t-online dot de,
good point about the security hole potential in multple query handling. I just wanted to say that in some cases its a good idea to use multiple queries for performance issues. For example, according to http://www.mysql.com/doc/en/Insert_speed.html :
"If you are inserting many rows from the same client at the same time, use multiple value lists INSERT statements. This is much faster (many times in some cases) than using separate INSERT statements."
claude_minette at hotmail dot com
18-Nov-2002 08:15
18-Nov-2002 08:15
I nedded to have a select box containing the fields of an ENUM in a MySQL table.
Use this if you need it too. it may be useful. Be sure to be connected and use $table to choose the table you want to describe.
$table="Ma_table"
$describe=mysql_query("describe $table");
while ($ligne=mysql_fetch_array($describe)){
extract($ligne);
if (substr($Type,0,4)=='enum'){
echo $Type;
$liste=substr($Type,5,strlen($Type));
$liste=substr($liste,0,(strlen($liste)-2));
$enums=explode(',',$liste);
if (sizeof($enums)>0){
echo "<select name='enum'>\n";
for ($i=0; $i<sizeof($enums);$i++){
$elem=strtr($enums[$i],"'"," ");
echo "<option value='".$elem."'>".$elem."</option>\n";
}
echo "</select>";
}
}
}
Hope it will help...
Some improvements can bee done, if you do, please mail me the improvements you've done...
Min's
acloutier at agricom dot ca
17-Nov-2001 07:03
17-Nov-2001 07:03
You can put multiple query with PHP && MySQL:
//initialise MySQL users variables
mysql_query("select @a:=0,@b:=0");
//select values of variables
mysql_query("select @a:=sum(rate), @b:=count(*) from ratetable where rated_user_id='$id'");
//finally, select for result
$rate_id_select=mysql_query("select @c:=round(@a/@b) as effective_rate");
//..and result $rate_user=mysql_result($rate_id_select,0,"effective_rate");
...
echo $rate_user;
Alain Cloutier
webmaster@saguenay.cc
nikhil-php at nols dot com
02-Jan-2000 04:38
02-Jan-2000 04:38
When trying to INSERT or UPDATE and trying to put a large amount of text or data (blob) into a mysql table you might run into problems.
In mysql.err you might see:
Packet too large (73904)
To fix you just have to start up mysql with the option -O max_allowed_packet=maxsize
You would just replace maxsize with the max size you want to insert, the default is 65536