Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : MySQL Functions : mysql_query

mysql_query

Send a MySQL query (PHP 4, PHP 5, PECL mysql:1.0)
resource mysql_query ( string query [, resource link_identifier] )

mysql_query() sends an unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

Parameters

query

A SQL query

The query string should not end with a semicolon.

link_identifier

The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If by chance no connection is found or established, an E_WARNING level warning is generated.

Return Values

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

Use mysql_num_rows() to find out how many rows were returned for a SELECT statement or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.

mysql_query() will also fail and return FALSE if the user does not have permission to access the table(s) referenced by the query.

Examples

Example 1455. Invalid Query

The following query is syntactically invalid, so mysql_query() fails and returns FALSE.

<?php
$result
= mysql_query('SELECT * WHERE 1=1');
if (!
$result) {
   die(
'Invalid query: ' . mysql_error());
}

?>


Example 1456. Valid Query

The following query is valid, so mysql_query() returns a resource.

<?php
// This could be supplied by a user, for example
$firstname = 'fred';
$lastname  = 'fox';

// Formulate Query
// This is the best way to perform a SQL query
// For more examples, see mysql_real_escape_string()
$query = sprintf("SELECT firstname, lastname, address, age FROM friends WHERE firstname='%s' AND lastname='%s'",
   
mysql_real_escape_string($firstname),
   
mysql_real_escape_string($lastname));

// Perform Query
$result = mysql_query($query);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
   
$message  = 'Invalid query: ' . mysql_error() . "\n";
   
$message .= 'Whole query: ' . $query;
   die(
$message);
}

// Use result
// Attempting to print $result won't allow access to information in the resource
// One of the mysql result functions must be used
// See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.
while ($row = mysql_fetch_assoc($result)) {
   echo
$row['firstname'];
   echo
$row['lastname'];
   echo
$row['address'];
   echo
$row['age'];
}

// Free the resources associated with the result set
// This is done automatically at the end of the script
mysql_free_result($result);
?>


Related Examples ( Source code ) » mysql_query
















Code Examples / Notes » mysql_query

acloutier

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


chris

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.

nikhil-php

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


brjann

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')"
?>


09-apr-2003 08:43

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

jacky

This is a replay to:  a at a dot com 20-Jun-2007 04:59
I call it a bug that PHP can not execute multiple queries, if your code is vulnerable to your example, then your code is broken. The language has no business hiding your bad code! That would be like blaming items when something bad happens ..... never mind, this is common practice now a days....
Not supporting multiple queries in one execution is a problem when you want to run thousands/millions of queries over a slower network connection. I'm here because I need to execute ~ 6000000 MySQL queries to populate a random customer database. Running saved queries every few thousand statements would save a lot of time....
Not supporting this is like that magic_quotes problem which causes more problems then it solves, at least magic_quotes will finally be removed from PHP6.
[quote=a at a dot com]
###The reason that multiple queries are not supported is to help prevent exploits.###
For example, the user could enter something like:
 "; DELETE * FROM users;
in the name field of a form, which would erase everything in the table when the query is executed.
By allowing mysql_query to support only single commands, this hole is closed.[/quote]


cedric ___at___ sadai ___dot___ net

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)
?>


hipsurgery

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!


veyita_angi

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);


wjyong

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.


mark @t ilisys dot c0m au

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

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');


massiv

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


jack dot whoami

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.


ollitech

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


davidc

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.


max

Re: no support for multiple queries.
Some have suggested that multiple queries are not supported in order to avoid people being able to drop tables.
This seems a weak argument to me, since security should be handled on the server. Why not create and use a user that doesn't have the required permissions?
I find a need to use a temporary table. Without multiple queries, they're impossible. An alternative is to use a real table, which means I need to increase the permissions for the user I am using (which was previously read-only).
So, it would seem that, in my case, not supporting multiple queries has opened a security hole, not closed one.
In fact, it's plausible that one might not even have the authority to change a user's permissions, so this would mean the only alternative is to use a sub-query, which is quite a bit slower (according to my testing on mysqld5), and even crashes mysqld4.


php

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."


allen a brooker gb net

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


earlsinclair2001

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'];
?>


rob desbois

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.


halion

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


harmor

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;
}
?>


duane

Max: since you are using MySQL 5, what about creating some views to get round your problem?

me

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;
}


matt

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);
}
?>


x_terminat_or_3

It says in the manual:
The query string should not end with a semicolon.
However, I read somewhere that it is good practice to add a semicolon at the end of your query, and that it increases security against SQL-injection attacks. Therefore I have issued a requirement that all my developers end queries with semicolons.
So far, PHP & MySQL have happily accepted those queries, with only one exception:
If you use more then one query in the same call to mysql_query, MySQL sometimes only executes the first query and fails on the others.


webmaster

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'")
}
?>


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";
   }
?>


justinb

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.


patrick

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']."'");
?>


predrag supurovic

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;
}


jon

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";
}
?>


claude_minette

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


gconnor

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" ;
       }


noah

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>
<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.


php

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


spencer

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.


aidan

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 = "";
      }
    }
  }
 }
?>


ix

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 = "";
       }
     }
   }
 }
?>


celtic

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 = "";
      }
    }
  }
 }
?>


cc+php

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 );
               }
       }
?>


congaz

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...


axiak

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;
}
?>


babba

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;
}
?>


kagekonjou

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.


yankee

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>");
  }
}
?>


andregodin

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  *********************************\
*  Paramêtres :                                       *
* 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'exécuter la requête");
$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 décider d'afficher quelques champs seulements *
*  en spécifiant les champs désiré                            *
**************************************************************/
$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 élément du tableau
print "$sentence
";
}
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 :)


joe

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


antony%40terian%2eco%2euk

A useful URL for queries to search for duplicate records is: http://www.onlamp.com/pub/a/onlamp/excerpt/mysqlckbk/index1.html?page=last

thomas -at - pixtur -dot- de

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.


geert

@ x_terminat_or_3 at yahoo dot fr
10-Mar-2007 12:27
"However, I read somewhere that it is good practice to add a semicolon at the end of your query, and that it increases security against SQL-injection attacks. Therefore I have issued a requirement that all my developers end queries with semicolons."
Erm... I don't believe you've understood the principle of executing one query at the same time, as the manual explains. Suffixing your query with a semicolon does not influence vulnerability to SQL-injection; as PHP would otherwise add the semicolon by itself at the end of your query.
Better instruct your developers that they use mysql_real_escape_string and surround user input in queries with single quotes (').


a

###The reason that multiple queries are not supported is to help prevent exploits.###
For example, the user could enter something like:
 "; DELETE * FROM users;
in the name field of a form, which would erase everything in the table when the query is executed.
By allowing mysql_query to support only single commands, this hole is closed.


Change Language


Follow Navioo On Twitter
mysql_affected_rows
mysql_change_user
mysql_client_encoding
mysql_close
mysql_connect
mysql_create_db
mysql_data_seek
mysql_db_name
mysql_db_query
mysql_drop_db
mysql_errno
mysql_error
mysql_escape_string
mysql_fetch_array
mysql_fetch_assoc
mysql_fetch_field
mysql_fetch_lengths
mysql_fetch_object
mysql_fetch_row
mysql_field_flags
mysql_field_len
mysql_field_name
mysql_field_seek
mysql_field_table
mysql_field_type
mysql_free_result
mysql_get_client_info
mysql_get_host_info
mysql_get_proto_info
mysql_get_server_info
mysql_info
mysql_insert_id
mysql_list_dbs
mysql_list_fields
mysql_list_processes
mysql_list_tables
mysql_num_fields
mysql_num_rows
mysql_pconnect
mysql_ping
mysql_query
mysql_real_escape_string
mysql_result
mysql_select_db
mysql_set_charset
mysql_stat
mysql_tablename
mysql_thread_id
mysql_unbuffered_query
eXTReMe Tracker