Что возвращает mysql query

PHP mysqli_query() Function

The mysqli_query() function accepts a string value representing a query as one of the parameters and, executes/performs the given query on the database.

Syntax

Parameters

con(Mandatory)

This is an object representing a connection to MySQL Server.

query(Mandatory)

This is a string value representing the query to be executed.

mode(Optional)

This is a integer value representing the result mode. You can pass MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT as values to this parameter.

Return Values

For SELECT, SHOW, DESCRIBE and EXPLAIN queries this function returns a mysqli_result object holding the result of the query in case of success and, false if failed.

For other queries this function returns an boolean value which is, true if the operation/query is successful and, false if not.

PHP Version

This function was first introduced in PHP Version 5 and works works in all the later versions.

Example

Following example demonstrates the usage of the mysqli_query() function (in procedural style) −

This will produce following result −

If you observe the contents of the table in the database you can see the inserted records as shown below −

Example

In object oriented style the syntax of this function is $con->query(); Following is the example of this function in object oriented style $minus;

This will produce following result −

If you observe the contents of the table in the database you can see the inserted records as shown below −

Example

Following example prints the results of INSERT and SELECT queries −

This will produce following result −

Example

Assume we have created a table players in the database and populated it, as shown below −

Что возвращает mysql query

int mysql_query(MYSQL *mysql, const char *query)

8.4.3.168 Описание

Выполняет запрос SQL, указанный в аргументе query в виде строки с нулевыми окончаниями. Данный запрос должен состоять из одной команды SQL. Нельзя добавлять к этой команде в качестве завершающих элементов точку с запятой ( `;’ ) или \g .

Функция mysql_query() не может использоваться для запросов, содержащих двоичные данные; вместо этого необходимо использовать функцию mysql_real_query() (двоичные данные могут содержать символ `\0′ , который mysql_query() интерпретирует как окончание строки запроса).

Для проверки, вернул данный запрос результирующий набор или нет, можно использовать функцию mysql_field_count() . See section 8.4.3.85 mysql_field_count() .

8.4.3.169 Возвращаемые значения

Нуль при успешном выполнении запроса. Величина, отличная от нуля, если произошла ошибка.

mysql_query

Данный модуль устарел, начиная с версии PHP 5.5.0, и удалён в PHP 7.0.0. Используйте вместо него MySQLi или PDO_MySQL. Смотрите также инструкцию MySQL: выбор API. Альтернативы для данной функции:

• mysqli_query()
• PDO::query()

Описание

mysql_query() посылает один запрос (посылка нескольких запросов не поддерживается) активной базе данных сервера, на который ссылается переданный дескриптор link_identifier .

Список параметров

Запрос не должен заканчиваться точкой с запятой. Данные в запросе должны быть корректно проэкранированы.

Соединение MySQL. Если идентификатор соединения не был указан, используется последнее соединение, открытое mysql_connect() . Если такое соединение не было найдено, функция попытается создать таковое, как если бы mysql_connect() была вызвана без параметров. Если соединение не было найдено и не смогло быть создано, генерируется ошибка уровня E_WARNING .

Возвращаемые значения

Для запросов SELECT, SHOW, DESCRIBE, EXPLAIN и других запросов, возвращающих результат из нескольких рядов, mysql_query() возвращает дескриптор результата запроса ( resource ), или false в случае возникновения ошибки.

Для других типов SQL-запросов, INSERT, UPDATE, DELETE, DROP и других, mysql_query() возвращает true в случае успешного выполнения и false в случае возникновения ошибки.

Полученный дескриптор результата нужно передать в функцию mysql_fetch_assoc() или любую другую функцию, работающую с результатами запросов.

Используйте mysql_num_rows() для выяснения количества рядов в результате SELECT-запроса или mysql_affected_rows() для выяснения количества обработанных рядов запросами DELETE, INSERT, REPLACE и UPDATE.

mysql_query() также завершится с ошибкой и вернёт false , если у пользователя нет доступа к какой-либо из таблиц, фигурирующих в запросе.

Примеры

Пример #1 Неверный запрос

Следующий запрос составлен неправильно и mysql_query() вернёт false .

<?php
$result = mysql_query ( ‘SELECT * WHERE 1 = 1’ );
if (! $result ) <
die( ‘Неверный запрос: ‘ . mysql_error ());
>

Пример #2 Верный запрос

Следующий запрос верен, поэтому mysql_query() вернёт resource .

<?php
// Эти данные, к примеру, могли быть получены от пользователя
$firstname = ‘fred’ ;
$lastname = ‘fox’ ;

// Формируем запрос
// Это лучший способ выполнить SQL-запрос
// Ещё примеры можно найти в документации 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 ));

// Выполняем запрос
$result = mysql_query ( $query );

// Проверяем результат
// Это показывает реальный запрос, посланный к MySQL, а также ошибку. Удобно при отладке.
if (! $result ) <
$message = ‘Неверный запрос: ‘ . mysql_error () . «\n» ;
$message .= ‘Запрос целиком: ‘ . $query ;
die( $message );
>

// Используем результат
// Попытка напечатать $result не выведет информацию, которая в нем хранится
// Необходимо использовать какую-либо mysql-функцию, работающую с результатом запроса
// Смотрите также mysql_result(), mysql_fetch_array(), mysql_fetch_row() и т.п.
while ( $row = mysql_fetch_assoc ( $result )) <
echo $row [ ‘firstname’ ];
echo $row [ ‘lastname’ ];
echo $row [ ‘address’ ];
echo $row [ ‘age’ ];
>

// Освобождаем ресурсы, ассоциированные с результатом
// Это делается автоматически в конце скрипта
mysql_free_result ( $result );
?>

Смотрите также

• mysql_connect() — Открывает соединение с сервером MySQL
• mysql_error() — Возвращает текст ошибки последней операции с MySQL
• mysql_real_escape_string() — Экранирует специальные символы в строках для использования в выражениях SQL
• mysql_result() — Возвращает данные результата запроса
• mysql_fetch_assoc() — Возвращает ряд результата запроса в качестве ассоциативного массива
• mysql_unbuffered_query() — Посылает запрос MySQL без авто-обработки результата и его буферизации

User Contributed Notes 26 notes

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.

This project implements a wrapper to mysql functions in PHP7.0+

tested and working fine =)

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

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;

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′»),
);

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

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

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’

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’ ]);
?>

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

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.

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

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:

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

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

I believe there is a typo in celtic at raven-blue dot com version with:

I think you really ment:

I changed the $sql to $tsl

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.

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

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

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

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

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

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

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 = «» ;
>
>
>
>
?>

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

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.

mysql_query

This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. 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:

• mysqli_query
• PDO::query

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

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

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 had been 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
$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 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 ));

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