PHP 8.4.0 RC2 available for testing

mysql_query

(PHP 4, PHP 5)

mysql_queryEnvoie une requête à un serveur MySQL

Avertissement

Cette extension était obsolète en PHP 5.5.0, et a été supprimée en PHP 7.0.0. À la place, vous pouvez utiliser l'extension MySQLi ou l'extension PDO_MySQL. Voir aussi MySQL : choisir une API du guide. Alternatives à cette fonction :

Description

mysql_query(string $query, resource $link_identifier = NULL): mixed

mysql_query() envoie une seule requête (les requêtes multiples ne sont pas supportées) à la base de données courante sur le serveur associé avec le link_identifier spécifié.

Liste de paramètres

query

Une requête SQL

La chaîne de requête ne doit pas se terminer par un point-virgule. Les données contenues dans la requête doivent être échappées proprement.

link_identifier

La connexion MySQL. S'il n'est pas spécifié, la dernière connexion ouverte avec la fonction mysql_connect() sera utilisée. Si une telle connexion n'est pas trouvée, la fonction tentera d'ouvrir une connexion, comme si la fonction mysql_connect() avait été appelée sans argument. Si aucune connexion n'est trouvée ou établie, une alerte de niveau E_WARNING sera générée.

Valeurs de retour

Pour les requêtes du type SELECT, SHOW, DESCRIBE, EXPLAIN et les autres requêtes retournant un jeu de résultats, mysql_query() retournera une ressource en cas de succès, ou false en cas d'erreur.

Pour les autres types de requêtes, INSERT, UPDATE, DELETE, DROP, etc., mysql_query() retourne true en cas de succès ou false en cas d'erreur.

La ressource de résultat retournée doit être passée à la fonction mysql_fetch_array(), et les autres fonctions permettant d'explorer le résultat des tables, pour accéder aux données retournées.

Utilisez mysql_num_rows() pour trouver le nombre de lignes retournées pour une requête du type SELECT ou mysql_affected_rows() pour trouver le nombre de lignes affectées par les requêtes du type DELETE, INSERT, REPLACE, ou UPDATE.

mysql_query() échouera et retournera false si l'utilisateur n'a pas les autorisations nécessaires pour accéder à la (aux) table(s) référencée(s) par la requête.

Exemples

Exemple #1 Requête invalide

La requête suivante est syntaxiquement invalide, donc mysql_query() va échouer et retourner false.

<?php
$result
= mysql_query('SELECT * WHERE 1=1');
if (!
$result) {
die(
'Requête invalide : ' . mysql_error());
}

?>

Exemple #2 Requête valide

La requête suivante est valide, donc mysql_query() va retourner une ressource.

<?php
// Ceci peut être demandé par un utilisateur, par exemple :
$firstname = 'fred';
$lastname = 'fox';

// Formulation de la requête
// C'est la meilleur façon d'exécuter une requête SQL
// Pour plus d'exemples, voir 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));

// Exécution de la requête
$result = mysql_query($query);

// Vérification du résultat
// Ceci montre la requête envoyée à MySQL ainsi que l'erreur. Utile pour déboguer.
if (!$result) {
$message = 'Requête invalide : ' . mysql_error() . "\n";
$message .= 'Requête complète : ' . $query;
die(
$message);
}

// Utilisation du résultat
// Tenter d'affichager $result ne vous donnera pas d'informations contenues dans la ressource
// Une des fonctions MySQL de résultat doit être utilisée
// Voir aussi 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'];
}

// Libération des ressources associées au jeu de résultats
// Ceci est effectué automatiquement à la fin du script
mysql_free_result($result);
?>

Voir aussi

add a note

User Contributed Notes 15 notes

up
12
jack dot whoami at gmail dot com
17 years ago
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.
up
7
halion at gmail dot com
17 years ago
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);

}
up
2
Anonymous
18 years ago
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";
}
?>
up
-1
fbraz3 at gmail dot com
6 years ago
This project implements a wrapper to mysql functions in PHP7.0+

https://github.com/OOPS-ORG-PHP/mysql-extension-wrapper

tested and working fine =)
up
-1
Mr. Tim
16 years ago
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'
up
-2
ialsoagree
15 years ago
When you run a select statement and receive a response, the data types of your response will be a string regardless of the data type of the column.

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

// Echoes: string
echo gettype($array['user_id']);
?>
up
-2
nikhil-php at nols dot com
24 years ago
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
up
-3
masteracc0 at aol dot com
16 years ago
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! :)
up
-4
ddlshack [at] gmail.dot.com
13 years ago
Use this to neatly insert data into a mysql table:

<?php
function mysql_insert($table, $inserts) {
$values = array_map('mysql_real_escape_string', array_values($inserts));
$keys = array_keys($inserts);

return
mysql_query('INSERT INTO `'.$table.'` (`'.implode('`,`', $keys).'`) VALUES (\''.implode('\',\'', $values).'\')');
}
?>

For example:

<?php

mysql_insert
('cars', array(
'make' => 'Aston Martin',
'model' => 'DB9',
'year' => '2009',
));
?>
up
-3
davidc at edeca dot net
21 years ago
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.
up
-3
Anonymous
21 years ago
Until this function prohibits them, watch out for SQL comments (--) in your input.
up
-4
rob desbois
18 years ago
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.
up
-4
php at arcannon dot com
19 years ago
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
up
-5
veyita_angi at hotmail dot com
18 years ago
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);
up
-5
cc+php at c2se dot com
18 years ago
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 );

}
}

?>
To Top