MySQL 函数
在线手册：中文 英文
PHP手册

mysql_query

(PHP 4, PHP 5)

mysql_query — 发送一条 MySQL 查询

说明

resource mysql_query ( string $query [, resource $link_identifier ] )

mysql_query() 向与指定的连接标识符关联的服务器中的当前活动数据库发送一条查询。如果没有指定 link_identifier，则使用上一个打开的连接。如果没有打开的连接，本函数会尝试无参数调用 mysql_connect() 函数来建立一个连接并使用之。查询结果会被缓存。

Note:

查询字符串不应以分号结束。

mysql_query() 仅对 SELECT，SHOW，EXPLAIN 或 DESCRIBE 语句返回一个资源标识符，如果查询执行不正确则返回 FALSE。对于其它类型的 SQL 语句，mysql_query() 在执行成功时返回 TRUE，出错时返回 FALSE。非 FALSE 的返回值意味着查询是合法的并能够被服务器执行。这并不说明任何有关影响到的或返回的行数。 很有可能一条查询执行成功了但并未影响到或并未返回任何行。

以下查询语法上有错，因此 mysql_query() 失败并返回 FALSE：

Example #1 mysql_query() 例子

<php
$result = mysql_query("SELECT * WHERE 1=1")
    or die("Invalid query: " . mysql_error());
?>

以下查询当 my_col 并不是表 my_tbl 中的列时语义上有错，因此 mysql_query() 失败并返回 FALSE：

Example #2 mysql_query()

<?php
$result = mysql_query("SELECT my_col FROM my_tbl")
    or die("Invalid query: " . mysql_error());
?>

如果没有权限访问查询语句中引用的表时，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()。

MySQL 函数
在线手册：中文 英文
PHP手册
PHP手册 - N: 发送一条 MySQL 查询

用户评论:

mwwaygoo at hotmail dot com (03-Apr-2012 10:16)

I much prefer to use the same syntax for single INSERT, REPLACE and UPDATE queries as it is easier to read and keeps my code shorter (no seperate building of insert and update values)

INSERT INTO table SET x='1', y=3
UPDATE table SET x='2' WHERE y=3

So if your using a function to build your query, you will only ever need to code the "field=value, field2=value2" part for any query.

HS at Duijst dot com (01-Apr-2012 10:00)

@ ghighiaw at yahoo dot com

A simple solution might be: write the query as a format string, and when it's time to query, replace the specifier bij the variable value using sprintf().

<?php
$query = "SELECT emp_no FROM employees WHERE dpt_no = '%s'";

(....)

$var = 'dpt_it';

$qstring = sprintf($query, $var);
// $qstring will now be equivalent to:
// SELECT emp_no FROM employees WHERE dpt_no = 'dpt_it';
mysql_query($qstring);
?>

ghighiaw at yahoo dot com (28-Mar-2012 11:11)

In dynamic queries using a variable in the query before defining it will result in the query run with wrong results:

consider:

$query = "SELECT emp_no FROM employees WHERE dpt_no = '$var'";

$var= 'dpt_it';

this will be equivalent to:

SELECT emp_no FROM employees WHERE dpt_no = '';

I think its because PHP engine is an interpreter not a compiler, which can't foresee variable definitions.

This is acceptable but frustrating for newcomers from complied programming languages.

Even if you try to be smart and define the variable before the query but pass it a value from a variable defied after the query declaration this will not work either.

seingh at patronum dot tk (18-Aug-2011 07:08)

usefully function to use insend of mysql_query:
<?php
$mysql_last_query = ""; //this var contains the last mysql query
function query($sql, $args) { #sintax: query("query", "arg1", "arg2", "argn"); EXAMPLE: query("select * from %s", $_GET["table"]);
    global $mysql_last_query;
    $args = func_get_args();
    if(($num = func_num_args()) > 1)
        $sql = call_user_func_array("sprintf", $args);
    $mysql_last_query = $sql;
    return mysql_query($sql);
}
function select($table, $what = "*", $where = "", $limit = ""){ #this function is a "shortcut" for mysql select EXAMPLE: select("blah"); //SELECT * FROM blah and so on
    return mysql_query("SELECT ".$what." FROM ".$table.($where == "" ? "":(" WHERE ".$where)).($limit == "" ? "":(" LIMIT ".$limit)));
}
?>

elephant (19-Jan-2011 04:49)

you can speed up your query by using multiple insert simultaneously using this syntax:

"INSERT INTO table (id,box) VALUES (1,'box1'),(2,'box2'),(3,'box3')"

ddlshack [at] gmail.dot.com (20-Dec-2010 05:12)

Use this to neatly insert data into a mysql table:

<?php
function mysql_insert($table, $inserts) {
    $values = array_map('mysql_real_escape_string', array_values($inserts));
    $keys = array_keys($inserts);
       
    return mysql_query('INSERT INTO `'.$table.'` (`'.implode('`,`', $keys).'`) VALUES (\''.implode('\',\'', $values).'\')');
}
?>

For example:

<?php

mysql_insert('cars', array(
    'make' => 'Aston Martin',
    'model' => 'DB9',
    'year' => '2009',
));
?>

Anonymous (03-Feb-2010 10:49)

This is a small function I wrote for handling NULL values in MySQL. (See examples below)

<?php
function parseNull($data)
{
        // Be sure your data is escaped before you use this function
    if (chop($data) != "")
        if (strtolower(chop($data)) == "null")
            return "NULL";
        else
            return "'" . $data . "'";
    else
        return "NULL";
}
?>

When you pass it a value, it checks if it is a blank string or the string 'null'. If so, it returns NULL. If not, it wraps your data in single quotes.
This allows you to more easily default a field to null when you insert or update data into your database. To use it, set up a mysql insert or update query for a text field without the single quotes, like this:

<?php
// Escape your data before using it with parseNull()
$my_data = mysql_escape_string($my_data);

mysql_query("INSERT INTO `my_table` VALUES (1," . parseNull($my_data). ")");
?>

When $my_data contains the string "null" or a blank string, the value NULL is inserted. When it is not, the value is wrapped in single quotes and this string is inserted.

Warning: Escape the data before passing it to parseNull! parseNull does not escape the data. If you escape after using the parseNull function, the single quotes that parseNull generates will be escaped.

Examples:
When passed the ALREADY ESCAPED string "mydata", parseNull returns:
'mydata'
The resulting mysql query, using the code above, is:

INSERT INTO `my_table` VALUES (1, 'mydata')

When passed the ALREADY ESCAPED string "null", parseNull returns:
NULL
The resulting query is:
INSERT INTO `my_table` VALUES (1, NULL)

When passed the ALREADY ESCAPED blank string "", parseNull returns:
NULL
The resulting query is:
INSERT INTO `my_table` VALUES (1, NULL)

szczepan.krolgmail.c0m (21-Oct-2009 12:49)

<?php
/*
 * If `ve made a fast way to
 * get a sql statement out of a array
 * Just give an array to the function with
 *  your Keys (sql) and the
 * values in the other array
 *
 * array('keys'=>array('key','key2','key3'),
           'values'=>array(
            array('test','test2','test3'),
            array('best','best2','best3'),
            array('nest','nest2','nest3'),
             )
           );
 *
 * returns:
 * insert:  "(`key`,`key2`,`key3`)VALUES ('test','test2','test3'), ('best','best2','best3'), ('nest','nest2','nest3')"
 * Update:  "SET `key`='test', `key2`='test2', `key3`='test3'"
 *
 *
 */

$params=array('keys'=>array('key','key2','key3'),
           'values'=>array(
            array('test','test2','test3'),
            array('best','best2','best3'),
            array('nest','nest2','nest3'),
             )
           );

echo array2Sql($params,'insert');

function array2Sql($params=array(),$mod='insert')
{

#make it uniform (for insert, if only an pure array given)
if(!is_array($params['values']['0'])){
     $new['values']['0']=$params['values'];
     $params['values']= $new['values'];
}

switch($mod){

     case'insert':
      $vals=array();
      $keys='(`'.join('`,`',$params['keys']).'`)';

      foreach($params['values'] as $k=>$v){

             $vals[]='(\''.join('\', \'',$v).'\')';

      }
           $vals=implode(',', $vals);

      return $sql=$keys.'VALUES '.$vals;

     case'update':
      $sets=array();
      $i=0;
      foreach($params['values']['0'] as $k=>$v){
          $sets[]='`'.$params['keys'][$i].'`=\''.$v.'\'';
           $i++;
      }
       return $sql='SET '.implode(', ',$sets);

     }
     return false;

}
?>

Richie (at) RichieBartlett.com (28-Sep-2009 12:43)

For those of you whom spent hours bashing your brains against the keyboard wondering why your non-English characters are output as question marks... Try the following:

<?php

$db = mysql_connect('YOUR_DB_ADDRESS','YOUR_DB_USER','YOUR_DB_PASS') or die("Database error");
mysql_select_db('YOUR_DB', $db);

//SOLUTION::  add this comment before your 1st query -- force multiLanuage support
$result = mysql_query("set names 'utf8'");

$query = "select * from YOUR_DB_TABLE";
$result = mysql_query($query);

//-THE_REST_IS_UP_TO_YOU-

?>

Simply run the query "set names 'utf8' " against the MySQL DB and your output should appear correct.

Anonymous (25-Jun-2009 04:18)

When processing a RENAME TABLE query, PHP apparently always returns false, no matter if the query was successfully processed or not.

Stratsimir Kolchevski (22-Jun-2009 01:51)

The documentation claims that "multiple queries are not supported".

However, multiple queries seem to be supported. You just have to pass flag 65536 as mysql_connect's 5 parameter (client_flags). This value is defined in /usr/include/mysql/mysql_com.h:
#define CLIENT_MULTI_STATEMENTS (1UL << 16) /* Enable/disable multi-stmt support */

Executed with multiple queries at once, the mysql_query function will return a result only for the first query. The other queries will be executed as well, but you won't have a result for them.

Andy McReed (01-Mar-2009 08:28)

Even though executing multiple queries from one string isn't possible with the mysql_query method there is a way to do it which i found on this guys site (http://www.dev-explorer.com/articles/multiple-mysql-queries). Basically you just explode your SQL string by the semicolon (;) separating the queries and then loop through the resulting array executing each one individually. Saves you from having lines and lines of function calls in your code.

ialsoagree (31-Jan-2009 04:24)

When you run a select statement and receive a response, the data types of your response will be a string regardless of the data type of the column.

<?php
// Query to select an int column
$query = 'SELECT user_id FROM users WHERE user_id = 1';
$result = mysql_query($query);
$array = mysql_fetch_assoc($result);

// Echoes: string
echo gettype($array['user_id']);
?>

girishpadia at gmail dot com (19-Jan-2009 07:01)

Hello all,

I have extended mysql_query function to have more flexibility. It is a php file say "run_query.php". Include this file in your php application. To use this follow this steps.

1) Create a table in mysql with the following structure.

CREATE TABLE `errorlog` (
`query` varchar(5000) default NULL,
`error` varchar(5000) default NULL
)

2) Create a php file and paste following code. Save the file (say file name is run_query.php).

<?php
function mysql_query_($query)
{
    include "pass1.php";
    $curdate = date("d-m-Y H:i:s");
    if(mysql_query($query) == true)
    {
        if(substr(strtoupper($query),0,6) == 'INSERT' || substr(strtoupper($query),0,5) == 'UPDATE' || substr(strtoupper($query),0,5) == 'DELETE')
        {
            $fp=fopen("trans.sql","a");
            if($fp==null)
            {

                die("File cannot be opened. Try again !!!");
            }
            $printline = "/* $curdate : */ $query ;";
            fprintf($fp,"\r\n%s",$printline);
            fclose($fp);
            return true;
        }
        else
        {
            return mysql_query($query);
        }
    }
    else
    {
        $error = mysql_error();
        $error = addslashes($error);
        $query = addslashes($query);
        mysql_query("insert into errorlog values('$query','$error')");
        return false;
    }
}
?>

3) Include this file in your any php application.
4) Use mysql_query_ function instead of mysql_query function.

Advantages.

1) You can log query and error in your database table (errorlog).

2) A sql file created/appendended whenever any Data manipulation query is fired. So you can have a complete transaction log in query format.

3) This sql file will help you in "point in time recovery" in case of the database is crashed.

Your views ,comments and updation in this function are welcome.

girishpadia@gmail.com

Anonymous (19-Oct-2008 10:22)

Relying in external functions doesn't help you in learning programming, that is calling external functions without having an idea of their use.

<?PHP
function mysql_queryf($string)
{
    $args = func_get_args();
    array_shift($args);
    $len = strlen($string);
    $sql_query = "";
    $args_i = 0;
    for($i = 0; $i < $len; $i++)
    {
        if($string[$i] == "%")
        {
            $char = $string[$i + 1];
            $i++;
            switch($char)
            {
                case "%":
                    $sql_query .= $char;
                    break;
                case "u":
                    $sql_query .= "'" . intval($args[$args_i]) . "'";
                    break;
                case "s":
                    $sql_query .= "'" . mysql_real_escape_string($args[$args_i]) . "'";
                    break;
                case "x":
                    $sql_query .= "'" . dechex($args[$args_i]) . "'";
                    break;
            }
            if($char != "x")
            {
                $args_i++;
            }
        }
        else
        {
            $sql_query .= $string[$i];
        }
    }
    return mysql_query($sql_query);
}
?>

lanbotdevman (22-Sep-2008 04:01)

I modified a bit script originally posted by
ix at nivelzero dot ro
14-Aug-2005 01:07

This allows using large .sql files without getting 'memory size exhausted' error.

<?php
function parse_mysql_dump($url) {
   
    $handle = @fopen($url, "r");
    $query = "";
    while(!feof($handle)) {
        $sql_line = fgets($handle);
        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 = "";
            }
        }
    }
}
?>

Mr. Tim (18-Sep-2008 07:42)

It should be noted that mysql_query can generate an E_WARNING (not documented).  The warning that I hit was when the db user did not have permission to execute a UDF.

Expected behavior would be like an Invalid SQL statement, where there is no E_WARNING generated by mysql_query.

Warning: mysql_query() [function.mysql-query]: Unable to save result set in filename.php

The mysql_errno is 1370 and the mysql_error is:

execute command denied to user 'username'@'%' for routine 'database_name.MyUDF'

i dot prasoon at gmail dot com (07-Aug-2008 04:07)

The function formToDB($table, $exceptions = '', $sql_type = 'insert', $sql_condition = NULL) is not working properly. Problem with exceptional fields is there.

        I modified this function and now it is working properly. Here is the changed function -

function formToDB($table, $exceptions = '', $sql_type = 'insert', $sql_condition = NULL) {

  // define some vars
  $fields = '';
  $values = '';
 
  // format input fields into sql
  foreach ($_POST as $field => $value) {
   
    if (!strstr($exceptions,$field)) {
   
      $value = mysql_real_escape_string($value);
      if ($sql_type == 'insert') {
       
        $fields .= "$field, ";
        $values .= "'$value', ";
      }
      else {
        $fields .= "$field = '$value', ";
      }
    }
  }
 
  // remove trailing ", " from $fields and $values
  $fields = preg_replace('/, $/', '', $fields);
  $values = preg_replace('/, $/', '', $values);
 
  // create sql statement
  if ($sql_type == 'insert') {
    $sql = "INSERT INTO $table ($fields) VALUES ($values)";
  }
  elseif ($sql_type == 'update') {
    if (!isset($sql_condition)) {
      echo 'ERROR: You must enter a sql condition!';
      exit;
    }
    $sql = "UPDATE $table SET $fields WHERE $sql_condition";
  }
  else {
    echo 'ERROR: Invalid input for argument $sql_type: must be "insert" or "update"';
    exit;
  }
 
  // execute sql
  if (mysql_query($sql)) {
    return true;
  }
  else {
    //echo mysql_error();
    return false;
  }

}

steves at splicer dot com (07-Aug-2008 12:20)

Clarification to masteracc0 at aol dot com's earlier note:

<?php
// from masteracc0
$result1 = mysql_query($query1);

$result2 = mysql_query($query2);

//$result1 IS NOT EQUAL TO $result2 but will not provide an error

//but printing these queries to the screen will provide the same result
echo($result1);
echo($result2);
?>

$result1 and $result2 are different not because of the null string terminator but because they are not the results of the queries. They are resource handles to the results of (functionally identical, though this is irrelevant) queries run at different times.

So there will be no error as long as the query is valid. The actual result of the query will be the same regardless of the null string:

<?php
    // substitute your own simple query
    $testquery = "SELECT name FROM days WHERE uid=1";
    $withnull = $testquery . "\0";
   
    $result1 = mysql_query($testquery);   
    $result2 = mysql_query($withnull);
    $result3 = mysql_query($testquery);
   
    echo($result1);
    echo("<br />" . $result2);
    echo("<br />" . $result3);
   
    echo ("<br /> \$result1 and \$result2 equal?");
    echo $result1 == $result2 ? "yes!" : "no";

    echo ("<br /> \$result1 and \$result3 equal?");
    echo $result1 == $result3 ? "yes!" : "no";
   
    $testresult1 = mysql_fetch_assoc($result1);
    $testresult2 = mysql_fetch_assoc($result2);
    $testresult3 = mysql_fetch_assoc($result3);

    echo ("<br /> \$testresult1 and \$testresult2 equal? ");
    echo $testresult1 == $testresult2 ? "yes!" : "no";
   
    echo ("<br /> \$testresult1 and \$testresult3 equal? ");
    echo $testresult1 == $testresult3 ? "yes!" : "no";
          
?>

You can see in none of these cases does the null character change the equality. Results 1 and 3 come from the same query and the comparisons are the same as the comparison of results 1 and 2. The resource handles are different, the results are the same.

This means you need not fear the null character. Just understand that a new resource handle is created every time you call mysql_query().

pascalxusPLEASENOSPAM at yahoo dot com (21-Jul-2008 08:16)

Here is an example of using the mysql_query in the context of connecting to a database.

<?php
  function connect( $dbName )
  {
    do {
      $databaseResponse = mysql_connect(
      "example.com", "username", "password" );
      sleep(1);
    } while( $databaseResponse === false );

    @ $selectResult = mysql_select_db( $dbName ) or dieFunc();
  }

  function executeQuery( $query, $db )
  {
      if( $db != "" ) connect( $db );
      else connect( "pascal_crm" );

      $result= mysql_query( $query );
      $err   = mysql_error();
      if( $err != "" ) echo "error=$err  ";
      mysql_close();
      return $result;
  }

?>

fernandoleal at loytek dot com (16-Jun-2008 11:28)

Dunno if is it a bug but when you are working with replications servers and work with multiple databases queries if you don't select the database it will only insert,update,delete into the master and bypass the slave, I think it its because it doesn't insert the sql on the binary log so the work around its to just call mysql_select_db
MYSQL : 5.0.51a-log
PHP: 5.2.6
Example:
<?php
#Inserts only to master
$link=mysql_connect('host','user','pass');
$sql ="INSERT INTO mysql.host (host) VALUES ('localhost');"
var_dump(mysql_query($sql,$link));

#The Working Way Master - Slave
$link2=mysql_connect('host','user','pass');
$select_db = mysql_select_db('mysql', $link2);
var_dump(mysql_query($sql,$link2));  
?>

uramihsayibok, gmail, com (13-Apr-2008 09:46)

Similar to the queryf() posted by sk89q, I've found this small function quite handy.

Just please, please, PLEASE remember to escape your strings!
<?php

function mysql_queryf($query) {
    if (func_num_args()>1) {
        $args=func_get_args();
        $query=call_user_func_array("sprintf",$args);
    }
    return mysql_query($query);
}

// Allows for things like
$resultset=mysql_queryf("SELECT * FROM `table` WHERE `id`=%u LIMIT 1", $_GET["id"]);
// $_GET["id"] will be converted to an integer: 0 if it's invalid

?>
With a little modification it can handle a $connection resource too.

rogier (10-Apr-2008 02:55)

For all you programmers out there getting the 'Command out of synch' errors when executing a stored procedure call:

There are known bugs related to this issue, and the best workaround for avoiding this error seems to be switching to mysqli.

Still, I needed mysql to also handle these calls correctly.
The error is normally related to wrong function call sequences, though the bug report at  http://bugs.php.net/bug.php?id=39727 shows otherwise.

For me, after commenting out hundreds of lines and several introspection calls to parse the procedure information (using information_schema and 'SHOW' extensions), I still got the same error.
The first result is returned, because I initiated my connection using the MYSQL_MULTI_RESULTS value of 131072 (forget this and you will never get any output, but an error message stating mysql cannot return results in this context)

After testing with this code (sproc2 simply calls 'SELECT * FROM sometable'), I found the error must be in the mysql library/extension. Somehow, mysql does not handle multiple resultsets correctly, or is at least missing some functionality related to handling multiple results.

<?php
    //...
    $rs = mysql_query('CALL sproc2(500)');
    while (($row=mysql_fetch_assoc($rs))!==false) {
        print_r($row);
    }
    mysql_free_result($rs);

    $rs = mysql_query('CALL sproc2(500)');
    print mysql_error(); //the notorious 'command out of synch' message :(
    while (($row=mysql_fetch_assoc($rs))!==false) {
        print_r($row);
    }
    mysql_free_result($rs);
?>

After spending hours debugging my code (the full library is already over the MB), the only solution seemed to be to CLOSE the connection after the first call, and reopening it before the second.

So if you ever make a uniform database accessing interface and implement stored procedures/prepared statements (or classes for it), this could be a solution if you really wish to enable stored procedures.

Still, be aware that this is really a serious flaw in your design (and IMHO, the mysql extension)

Also see the documentation for mysqli on mysqli_query, which seems to be working fine.

kriskra at gmail dot com (23-Feb-2008 07:06)

Here is a small neat function to print out the mysql result as a html table:

<?php
function echo_result($result) {
  ?><table><tr><?
  if(! $result) { ?><th>result not valid</th><? }
  else {
    $i = 0;
    while ($i < mysql_num_fields($result)) {
      $meta = mysql_fetch_field($result, $i);
      ?><th style="white-space:nowrap"><?=$meta->name?></th><?
      $i++;
    }
    ?></tr><?
   
    if(mysql_num_rows($result) == 0) {
      ?><tr><td colspan="<?=mysql_num_fields($result)?>">
      <strong><center>no result</center></strong>
      </td></tr><?
    } else
      while($row=mysql_fetch_assoc($result)) {
        ?><tr style="white-space:nowrap"><?
        foreach($row as $key=>$value) { ?><td><?=$value?></td><? }
        ?></tr><?
      }
  }
  ?></table><?
}
?>

sk89q (17-Feb-2008 09:56)

sprintf+mysql_query which auto-escapes.

Usage:
<?php
$db->queryf("SELECT `m`.`name` FROM `test`.`members` WHERE `name`=%s OR `id`=%d OR `sex` IN (%a)", "Evil 'injection'", 'NaN', array('male', 'female', 'both', 'other', "Alien quote'man"));
?>

To be put into a class, and don't forget to change the $this->query() and $this->escape() functions accordingly.

<?php
function queryf($sql)
{   
    $args = func_get_args();
    @array_shift($args);
   
    $this->queryf_args = $args;
    $this->queryf_i = 0;
   
    $query = preg_replace_callback("#%(.)#", array($this, 'queryf_format'), $sql);
   
    return $this->query($query);
}

function queryf_format($m)
{   
    $args = $this->queryf_args;
    $i = &$this->queryf_i;
   
    switch($m[1])
    {
        case '%':
            return "%";
        case 'a':
            $s = array();
            $arr = $args[$i++];
            foreach($arr as $x)
            {
                $s[] = "'".$this->escape($x)."'";
            }
            return implode(",", $s);;=
        case 's':
            return "'".$this->escape($args[$i++])."'";
        case 'd':
            return "".intval($args[$i++])."";
        default:
            trigger_error("Bad type specifier: {$m[1]}", E_USER_ERROR);
    }
}
?>

masteracc0 at aol dot com (17-Nov-2007 06:00)

Keep in mind when dealing with PHP & MySQL that sending a null-terminated string to a MySQL query can be misleading if you use echo($sql) in PHP because the null terminator may not be visible.

For example (this assumes connection is already made),
$string1 = "mystring\0";
$string2 = "mystring";

$query1 = "SELECT * FROM table WHERE mystring='".$string1."'"
$query2 = "SELECT * FROM table WHERE mystring='".$string2."'"
 
$result1 = mysql_query($query1);

$result2 = mysql_query($query2);

//$result1 IS NOT EQUAL TO $result2 but will not provide an error

//but printing these queries to the screen will provide the same result
echo($result1);
echo($result2);

Not knowing this could lead to some mind-numbing troubleshooting when dealing with any strings with a null terminator.  So now you know! :)

patrick at ciphertek dot com (21-Sep-2007 06:28)

If you spend a lot of time writing pages that take input from a form and insert it into a database, this function will save you time!

Please Note: You have to name your form fields the same as their corresponding table column is named in mysql for this to work.

<?php

// $table - name of the mysql table you are querying
// $exceptions - fields that will not be inserted into table
//               i.e. 'submit, action, '; (note trailing comma and space!)
// $sql_type - has to be 'insert' or 'update'
// $sql_condition - have to define this if $sql_type = 'update'
//                  i.e. "userID = '".$_POST['userID']."'"
function formToDB($table, $exceptions = '', $sql_type = 'insert', $sql_condition = NULL) {

  // define some vars
  $fields = '';
  $values = '';
 
  // format input fields into sql
  foreach ($_POST as $field => $value) {
    if (!preg_match("/$field, /", $exceptions)) {
      $value = mysql_real_escape_string($value);
      if ($sql_type == 'insert') {
        $fields .= "$field, ";
        $values .= "'$value', ";
      }
      else {
        $fields .= "$field = '$value', ";
      }
    }
  }
 
  // remove trailing ", " from $fields and $values
  $fields = preg_replace('/, $/', '', $fields);
  $values = preg_replace('/, $/', '', $values);
 
  // create sql statement
  if ($sql_type == 'insert') {
    $sql = "INSERT INTO $table ($fields) VALUES ($values)";
  }
  elseif ($sql_type == 'update') {
    if (!isset($sql_condition)) {
      echo 'ERROR: You must enter a sql condition!';
      exit;
    }
    $sql = "UPDATE $table SET $fields WHERE $sql_condition";
  }
  else {
    echo 'ERROR: Invalid input for argument $sql_type: must be "insert" or "update"';
    exit;
  }
 
  // execute sql
  if (mysql_query($sql)) {
    return true;
  }
  else {
    //echo mysql_error();
    return false;
  }

} // end of function formToDB()

// Example for inserting new row
formToDB('users', 'submit, ');

// Example for updating existing row
formToDB('users', 'submit, userID, ', 'update', "userID = '".$_POST['userID']."'");

?>

ollitech at gmail dot com (25-Aug-2007 06:53)

Running an invalid delete query may not return false.

Invalid because no such record exists.

Code;

[php]
    //  execute it
                      $result=mysql_query($sql_delete_byindex);
                               
                      if(!$result||$result==FALSE){
                                   
                                                echo("<h1>Error occured while removing listing #: <i>".$rec_index."</i> </H1>");                                   
                                           
                                    }else if($result==TRUE){
                                               
                                                echo("<h1>Listing #: <i>".$rec_index."</i> Deleted!</H1>");
                                   
                                    echo "<a href=\"index.php\">Go to Start Page</a>";
                                   
                                    }
[/php]

Query;
[code]
$sql_delete_byindex = "DELETE FROM `$mysql_table` WHERE `index` = '".$rec_index."' AND `key` = '".$key."'";   
[/code]

result will be TRUE

halion at gmail dot com (09-Aug-2007 10:53)

mysql_query doesnt support multiple queries, a way round this is to use innodb and transactions

this db class/function will accept an array of arrays of querys, it will auto check every line for affected rows in db, if one is 0 it will rollback and return false, else it will commit and return true, the call to the function is simple and is easy to read etc
----------

class MySQLDB
{
   private $connection;          // The MySQL database connection

   /* Class constructor */
   function MySQLDB(){
      /* Make connection to database */
      $this->connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());
      mysql_select_db(DB_NAME, $this->connection) or die(mysql_error());
   }

   /* Transactions functions */

   function begin(){
      $null = mysql_query("START TRANSACTION", $this->connection);
      return mysql_query("BEGIN", $this->connection);
   }

   function commit(){
      return mysql_query("COMMIT", $this->connection);
   }
  
   function rollback(){
      return mysql_query("ROLLBACK", $this->connection);
   }

   function transaction($q_array){
         $retval = 1;

      $this->begin();

         foreach($q_array as $qa){
            $result = mysql_query($qa['query'], $this->connection);
            if(mysql_affected_rows() == 0){ $retval = 0; }
         }

      if($retval == 0){
         $this->rollback();
         return false;
      }else{
         $this->commit();
         return true;
      }
   }

};

/* Create database connection object */
$database = new MySQLDB;

// then from anywhere else simply put the transaction queries in an array or arrays like this:

   function function(){
      global $database;

      $q = array (
         array("query" => "UPDATE table WHERE something = 'something'"),
         array("query" => "UPDATE table WHERE something_else = 'something_else'"),
         array("query" => "DELETE FROM table WHERE something_else2 = 'something_else2'"),
      );

      $database->transaction($q);

   }

jack dot whoami at gmail dot com (02-Aug-2007 02:13)

Simulating an atomic operation for application locks using mysql.

$link = mysql_connect('localhost', 'user', 'pass');
if (!$link) {
    die('Not connected : ' . mysql_error());
}

// make foo the current db
$db_selected = mysql_select_db('foo', $link);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}

$q = "update `table` set `LOCK`='F' where `ID`='1'";
$lock = mysql_affected_rows();

If we assume
     NOT LOCKED = "" (empty string)
     LOCKED = 'F'

then if the column LOCK had a value other than F (normally should be an empty string) the update statement sets it to F and set the affected rows to 1. Which mean than we got the lock.
If affected rows return 0 then the value of that column was already F and somebody else has the lock.

The secret lies in the following statement taken from the mysql manual:
"If you set a column to the value it currently has, MySQL notices this and does not update it."

Of course all this is possible if the all application processes agree on the locking algorithm.

earlsinclair2001 at yahoo dot com (30-Apr-2007 09:14)

On my latest project, very often, I needed to select a unique row from the database. For example: a certain user with certain username, or a row where the ID (primary key) is X. I got tired of typing these queries over and over so I created a simple function that will do just that: select one row from the database where certain field is unique. I hope this can be helpful to somebody:

<?php

function selectonerow($fieldsarray, $table, $uniquefield, $uniquevalue)
{
    //The required fields can be passed as an array with the field names or as a comma separated value string
    if(is_array($fieldsarray))
    {
        $fields = implode(", ", $fieldsarray);
    }
    else
    {
        $fields = $fieldsarray;
    }
   
    //performs the query
    $result = mysql_query("SELECT $fields FROM $table WHERE $uniquefield = '$uniquevalue'") or die("Could not perform select query - " . mysql_error());
   
    $num_rows = mysql_num_rows($result);
       
    //if query result is empty, returns NULL, otherwise, returns an array containing the selected fields and their values
    if($num_rows == NULL)
    {
        return NULL;
    }
    else
    {
        $queryresult = array();
        $num_fields = mysql_num_fields($result);
        $i = 0;
        while ($i < $num_fields)
        {
            $currfield = mysql_fetch_field($result, $i);
            $queryresult[$currfield->name] = mysql_result($result, 0, $currfield->name);
            $i++;
        }
        return $queryresult;
    }
}

?>

This function assumes there is a MySQL connection already established and the database to be used already selected.

Here is an example of usage:

selectonerow(fields, table name, unique field name, unique field value)

Let's say I have a users table with the fields userid, username, firstname, lastname and email. userid is the primary key and username is a unique field. If you want to select the firstname, lastname and email from the table where the userid is 4:

<?php

$fields = array("firstname", "lastname", "email");
$userdata = selectonerow($fields, "users", "userid", 4);

?>

or

<?php

$userdata = selectonerow("firstname, lastname, email", "users", "userid", 4);

?>

This will return an array to $userdata with the keys being the field name and their respective value. This is how you would print out their first name, last name and email, for example:

<?php

echo $userdata['firstname'] $userdata['lastname'] $userdata['email'];

?>

JustinB at harvest dot org (09-Mar-2007 12:01)

If you're looking to create a dynamic dropdown list or pull the possible values of an ENUM field for other reasons, here's a handy function:

<?php
// Function to Return All Possible ENUM Values for a Field
function getEnumValues($table, $field) {
    $enum_array = array();
    $query = 'SHOW COLUMNS FROM `' . $table . '` LIKE "' . $field . '"';
    $result = mysql_query($query);
    $row = mysql_fetch_row($result);
    preg_match_all('/\'(.*?)\'/', $row[1], $enum_array);
    if(!empty($enum_array[1])) {
        // Shift array keys to match original enumerated index in MySQL (allows for use of index values instead of strings)
        foreach($enum_array[1] as $mkey => $mval) $enum_fields[$mkey+1] = $mval;
        return $enum_fields;
    }
    else return array(); // Return an empty array to avoid possible errors/warnings if array is passed to foreach() without first being checked with !empty().
}
?>

This function asumes an existing MySQL connection and that desired DB is already selected.

Since this function returns an array with the original enumerated index numbers, you can use these in any later UPDATEs or INSERTS in your script instead of having to deal with the string values.  Also, since these are integers, you can typecast them as such using (int) when building your queries--which is much easer for SQL injection filtering than a string value.

massiv at nerdshack dot com (19-Feb-2007 02:29)

Small change in mysql_dump function, to remove the ";" char at the end of the query.

<?
  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)) {
         $query = str_replace(";", "", "$query");
         $result = mysql_query($query);
         if (!$result && !$ignoreerrors) die(mysql_error());
         $query = "";
       }
     }
   }
  }
?>

... Massimo

noah at missionecommerce dot com (16-Dec-2006 02:26)

I got so tired of having to type out all the 11 letters in "mysql_query()" and even more tired of having to iterate through the result set....

So I created the perfect little all purpose wrapper function, called "q()";

<?
function q($query,$assoc=1) {
    $r = @mysql_query($query);
    if( mysql_errno() ) {
        $error = 'MYSQL ERROR #'.mysql_errno().' : <small>' . mysql_error(). '</small><br><VAR>$query</VAR>';
        echo($error); return FALSE;
    }
    if( strtolower(substr($query,0,6)) != 'select' ) return array(mysql_affected_rows(),mysql_insert_id());
    $count = @mysql_num_rows($r);
    if( !$count ) return 0;
    if( $count == 1 ) {
        if( $assoc ) $f = mysql_fetch_assoc($r);
        else $f = mysql_fetch_row($r);
        mysql_free_result($r);
        if( count($f) == 1 ) {
            list($key) = array_keys($f);   
            return $f[$key];
        } else {
            $all = array();
            $all[] = $f;
            return $all;
        }
    } else {
        $all = array();
        for( $i = 0; $i < $count; $i++ ) {
            if( $assoc ) $f = mysql_fetch_assoc($r);
            else $f = mysql_fetch_row($r);
            $all[] = $f;
        }
        mysql_free_result($r);
        return $all;
    }
}

?>

Example:

<?
$r = q('Select id,foo FROM blah');
echo $r[0]['id']; // first row, field 'id'

// for single field single row selects
// only the value is returned
$count = q('SELECT count(*) from blah');
// $count is the number
?>

Returns affected_rows and/or insert_id for anything other than select's. If you dont want field name keys then pass 0 for second parameter.

babba at nurfuerspam dot de (25-Nov-2006 11:42)

Following function creates a minimal update query by comparing two arrays with old and new values (phpmyadmin-like). An easy way to use it in your forms is to print out the old values in hidden fields with name old[$key] and name the visible form fields new[$key]. Feel free to send comments via mail.

<?php
function getUpdateString($tablename, $whereclause, $old, $new) {
    $changedvalues = "";
    foreach($old as $key => $oldvalue) {
        $newvalue = $new[$key];
        if($oldvalue != $newvalue) {
            if($changedvalues != "")
                $changedvalues .= ", ";
           
            $changedvalues .= "`".$key."`=";
            if(!is_numeric($newvalue))
                $changedvalues .= "'".$newvalue."'";
            else
                $changedvalues .= $newvalue;
        }
    }
   
    if($changedvalues == "")
        return "";
   
    return "UPDATE ".$tablename. " SET ".$changedvalues." WHERE ".$whereclause;
}
?>

axiak at mit dot edu (24-Oct-2006 01:13)

Gconner at sgi...

your function breaks when you give it a question mark!
Here's a function which correctly implements what I think you want. I'm using it in a pet project of mine.

The code:
<?php
function mysql_prepare ($query, $phs = array()) {
    $phs = array_map(create_function('$ph',
                     'return "\'".mysql_real_escape_string($ph)."\'";'), $phs);

    $curpos = 0;
    $curph  = count($phs)-1;

    for ($i=strlen($query)-1; $i>0; $i--) {

      if ($query[$i] !== '?')  continue;
      if ($curph < 0 || !isset($phs[$curph]))
    $query = substr_replace($query, 'NULL', $i, 1);
      else
    $query = substr_replace($query, $phs[$curph], $i, 1);

      $curph--;
    }
    unset($curpos, $curph, $phs);
    return $query;
}
?>

veyita_angi at hotmail dot com (04-Oct-2006 05:35)

this could be a nice way to print values from 2 tables with a foreign key. i have not yet tested correctly but it should work fine.

$buscar = mysql_query("SELECT k.*, e.Clasificacion FROM cat_plan_k k, cat_equipo e WHERE Tipo='$tipo' AND k.ID_Eq=a.ID_Eq");
    while ($row=mysql_fetch_array($buscar))
        {
            $nombre = "e.Clasificacion";
            $row[$nombre] = $Clasific; echo $row[$nombre].'convertido en '.$Clasific;
        }         
    mysql_free_result($buscar);

cc+php at c2se dot com (02-Sep-2006 01:39)

Here's a parameterised query function for MySQL similar to pg_query_params, I've been using something similar for a while now and while there is a slight drop in speed, it's far better than making a mistake escaping the parameters of your query and allowing an SQL injection attack on your server.

<?php   # Parameterised query implementation for MySQL (similar PostgreSQL's PHP function pg_query_params)
        # Example: mysql_query_params( "SELECT * FROM my_table WHERE col1=$1 AND col2=$2", array( 42, "It's ok" ) );

        if( !function_exists( 'mysql_query_params' ) ) {

                function mysql_query_params__callback( $at ) {
                        global $mysql_query_params__parameters;
                        return $mysql_query_params__parameters[ $at[1]-1 ];
                }

                function mysql_query_params( $query, $parameters=array(), $database=false ) {

                        // Escape parameters as required & build parameters for callback function
                        global $mysql_query_params__parameters;
                        foreach( $parameters as $k=>$v )
                                $parameters[$k] = ( is_int( $v ) ? $v : ( NULL===$v ? 'NULL' : "'".mysql_real_escape_string( $v )."'" ) );
                        $mysql_query_params__parameters = $parameters;

                        // Call using mysql_query
                        if( false===$database )
                                return mysql_query( preg_replace_callback( '/\$([0-9]+)/', 'mysql_query_params__callback', $query ) );
                        else    return mysql_query( preg_replace_callback( '/\$([0-9]+)/', 'mysql_query_params__callback', $query ), $database );

                }
        }

?>

joe (29-Aug-2006 08:45)

alteration to the script reposted by
webmaster at vs2055067 dot vserver dot de

   $fields = implode(array_keys($toAdd), ',');
   $values = "'".implode(array_values($toAdd), "','")."'";

should really be

   $fields = "`".implode(array_keys($toAdd), '`,`')."`";
   $values = "'".implode(array_values($toAdd), "','")."'";

as keys like `desc` (short for description) cause errors

spencer at barekoncept dot com (24-Aug-2006 05:15)

Here's an easy way to store the column names from a specified table in the array "cnames".

$result = mysql_query("SHOW COLUMNS FROM tablename");
$count = 0;
while ($row=mysql_fetch_row($result)){
    $cnt = 0;
    foreach ($row as $item){
        if ($cnt == 0){
            $cnames[$count] = $item;
            $cnt++;
            $count++;
        }
    }
}

Then, to display the results comma delimited:

foreach($cnames as $c){
echo $c.",";
}

I hope this helps some people as it took me a while to figure it out.

webmaster at vs2055067 dot vserver dot de (27-Jul-2006 04:03)

in the first note the function doesn't work and the other function is pretty complicated. Here is the corrected version of the first one and a function for update.

<?php
function mysql_insert($table, $toAdd){

   $fields = implode(array_keys($toAdd), ',');
    $values = "'".implode(array_values($toAdd), "','")."'"; # better

   $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)
}

function mysql_update($table, $update, $where){
    $fields = array_keys($update);
    $values = array_values($update);
     $i=0;
     $query="UPDATE ".$table." SET ";
     while($fields[$i]){
       if($i<0){$query.=", ";}
     $query.=$fields[$i]." = '".$values[$i]."'";
     $i++;
     }
     $query.=" WHERE ".$where." LIMIT 1;";
     mysql_query($query) or die(mysql_error());
     return true;
    
     //Example
     // mysql_update('myTable', $anarray, "type = 'main'")

}
?>

rob desbois (07-Jul-2006 10:38)

Note that the 'source' command used in the mysql client program is *not* a feature of the server but of the client.
This means that you cannot do
   mysql_query('source myfile.sql');
You will get a syntax error. Use LOAD DATA INFILE as an alternative.

cedric ___at___ sadai ___dot___ net (05-Jun-2006 04:26)

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)

?>

matt (21-Mar-2006 11: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 10: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 01: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 07: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 (22-Feb-2006 10: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 08: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 01: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 (05-Nov-2005 06: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 (25-Oct-2005 10: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 (01-Oct-2005 11: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 03: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 = "";
       }
     }
   }
  }
?>

ix at nivelzero dot ro (14-Aug-2005 11: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 = "";
        }
      }
    }
  }
?>

wjyong at sh163 dot net (01-May-2005 12: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 01: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
}

/* malformed query /*
$rs = mysql_query("SELECT `foo` FRO `bar`");
if($rs) {
  echo "This will never be echoed";
}
?>

andregodin at gmail dot com (18-Nov-2004 09: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 09: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 05: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)

Predrag Supurovic (01-Dec-2003 12: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 02: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 (19-Apr-2003 08: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 08:43)

Until this function prohibits them, watch out for SQL comments (--) in your input.

allen a brooker gb net (28-Mar-2003 01: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

claude_minette at hotmail dot com (18-Nov-2002 12: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 (16-Nov-2001 11: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 08: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