Last updated: Fri, 10 Oct 2008

mysql_query

(PHP 4, PHP 5, PECL mysql:1.0)

mysql_query — Send a MySQL query

Description

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 error 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, INSERT, 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 #1 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 #2 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);
?>


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 03: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 12: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
06-Aug-2008 08: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 04: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;


  }





?>

Ryan
07-Jul-2008 05:52


If you are using a variable loaded with user defined data in an SQL query you have to protect yourself from SQL injection.  Here is a simple function to keep sql queries safe, run the user defined data though this function before using it in a query to sanitize the input stopping SQL injection attempts.  Designed to work reguardless of PHP version.



<?php

function sql_safe($string){

  if(get_magic_quotes_gpc()) 

  {

    $string = stripslashes($string);

  }

  $badWords = "(delete)|(update)|(union)|(insert)|(drop)|(http)|(--)";

  $string = eregi_replace($badWords, "", $string);

  if (phpversion() >= '4.3.0')

  {

    $string = mysql_real_escape_string($string);

  }

  else

  {

    $string = mysql_escape_string($string);

  }

  return $string;

}

?>

fernandoleal at loytek dot com
16-Jun-2008 07: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 05: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 10: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 04: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 06: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);

    }

}

?>

dotpointer
14-Jan-2008 10:00


I got a strange fault when dealing with multiple connections and doing a SELECT against a database that didn't have the requested tables.



- mysql_query() returned FALSE.

- mysql_error() was empty.



So, if you get this combination, check if you're talking to the right database.

masteracc0 at aol dot com
17-Nov-2007 03: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 02: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 02: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 06: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
01-Aug-2007 10: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 05: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
08-Mar-2007 09: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 11: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
15-Dec-2006 11: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 08: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
23-Oct-2006 09: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 01: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 09: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 04: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 01: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 12: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 06: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 12: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'

mysql_query

Description

Parameters

Return Values

Examples

See Also