mysql_query

(PHP 4, PHP 5)

mysql_query — Send a MySQL query

Warning

This extension is deprecated as of PHP 5.5.0, and will be removed in the future.
Instead, the MySQLi or PDO_MySQL extension should be used.
See also MySQL: choosing an API guide and
related FAQ for more information.
Alternatives to this function include:

Description

mysql_query() sends a 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

An SQL query

The query string should not end with a semicolon.
Data inside the query should be properly escaped.

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 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// This could be supplied by a user, for example$firstname = 'fred';$lastname = 'fox';

// Formulate Query// This is the best way to perform an 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));

User Contributed Notes 56 notes

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

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

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.

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.

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.

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-logPHP: 5.2.6Example:<?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));

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.

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.

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.

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.

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

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}

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

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

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.

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.

1) If the LIKE matches more than one column you get the enum from the first, so be careful with the $Column argument2) 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.

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.

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.

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_namefunction table_to_array($table_name){$columns = array();

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.

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.

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

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

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.