Php mysqli query exception

mysqli_query

Для не-DML запросов (не INSERT, UPDATE или DELETE), эта функция равносильна вызову функции mysqli_real_query() , а затем mysqli_use_result() или mysqli_store_result() .

• mysqlnd на платформе Linux возвращает код ошибки 1153. Сообщение об ошибке означает » размер пакета превышает max_allowed_packet байт «.
• mysqlnd на платформе Windows возвращает код ошибки 2006. Это сообщение об ошибке означает » отказ сервера «.
• libmysqlclient на всех платформах возвращает код ошибки 2006. Это сообщение об ошибке означает » отказ сервера «.

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

Только для процедурного стиля: Идентификатор соединения, полученный с помощью mysqli_connect() или mysqli_init()

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

Либо константа MYSQLI_USE_RESULT , либо MYSQLI_STORE_RESULT в зависимости от требуемого поведения функции. По умолчанию используется MYSQLI_STORE_RESULT .

При использовании MYSQLI_USE_RESULT все последующие вызовы этой функции будут возвращать ошибку Commands out of sync до тех пор, пока не будет вызвана функция mysqli_free_result()

С константой MYSQLI_ASYNC (доступна при использовании mysqlnd) возможно выполнять запросы асинхронно. В этом случае для получения результатов каждого запроса необходимо использовать функцию mysqli_poll() .

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

Возвращает FALSE в случае неудачи. В случае успешного выполнения запросов SELECT, SHOW, DESCRIBE или EXPLAIN mysqli_query() вернет объект mysqli_result. Для остальных успешных запросов mysqli_query() вернет TRUE .

Список изменений

Версия	Описание
5.3.0	Добавлена возможность выполнять асинхронные запросы.

Примеры

Пример #1 Пример использования mysqli::query()

$mysqli = new mysqli ( «localhost» , «my_user» , «my_password» , «world» );

/* проверка соединения */
if ( $mysqli -> connect_errno ) printf ( «Не удалось подключиться: %s\n» , $mysqli -> connect_error );
exit();
>

/* Создание таблицы не возвращает результирующего набора */
if ( $mysqli -> query ( «CREATE TEMPORARY TABLE myCity LIKE City» ) === TRUE ) printf ( «Таблица myCity успешно создана.\n» );
>

/* Select запросы возвращают результирующий набор */
if ( $result = $mysqli -> query ( «SELECT Name FROM City LIMIT 10» )) printf ( «Select вернул %d строк.\n» , $result -> num_rows );

/* очищаем результирующий набор */
$result -> close ();
>

/* Если нужно извлечь большой объем данных, используем MYSQLI_USE_RESULT */
if ( $result = $mysqli -> query ( «SELECT * FROM City» , MYSQLI_USE_RESULT ))

/* Важно заметить, что мы не можем вызывать функции, которые взаимодействуют
с сервером, пока не закроем результирующий набор. Все подобные вызовы
будут вызывать ошибку ‘out of sync’ */
if (! $mysqli -> query ( «SET @a:=’this will not work'» )) printf ( «Ошибка: %s\n» , $mysqli -> error );
>
$result -> close ();
>

$link = mysqli_connect ( «localhost» , «my_user» , «my_password» , «world» );

/* проверка соединения */
if ( mysqli_connect_errno ()) printf ( «Не удалось подключиться: %s\n» , mysqli_connect_error ());
exit();
>

/* Создание таблицы не возвращает результирующего набора */
if ( mysqli_query ( $link , «CREATE TEMPORARY TABLE myCity LIKE City» ) === TRUE ) printf ( «Таблица myCity успешно создана.\n» );
>

/* Select запросы возвращают результирующий набор */
if ( $result = mysqli_query ( $link , «SELECT Name FROM City LIMIT 10» )) printf ( «Select вернул %d строк.\n» , mysqli_num_rows ( $result ));

/* очищаем результирующий набор */
mysqli_free_result ( $result );
>

/* Если нужно извлечь большой объем данных, используем MYSQLI_USE_RESULT */
if ( $result = mysqli_query ( $link , «SELECT * FROM City» , MYSQLI_USE_RESULT ))

/* Важно заметить, что мы не можем вызывать функции, которые взаимодействуют
с сервером, пока не закроем результирующий набор. Все подобные вызовы
будут вызывать ошибку ‘out of sync’ */
if (! mysqli_query ( $link , «SET @a:=’this will not work'» )) printf ( «Ошибка: %s\n» , mysqli_error ( $link ));
>
mysqli_free_result ( $result );
>

Результат выполнения данных примеров:

Таблица myCity успешно создана. Select вернул 10 строк. Ошибка: Commands out of sync; You can't run this command now

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

• mysqli_real_query() — Выполнение SQL запроса
• mysqli_multi_query() — Выполняет запрос к базе данных
• mysqli_free_result() — Освобождает память занятую результатами запроса

Источник

How to report errors in mysqli

Add the following line before mysqli_connect() (or new mysqli() ):

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); 

Introduction

Reporting errors is the most essential thing in programming. A programmer is effectively deaf-blind if they cannot see errors occurred during the script execution. So it is very important to configure error reporting for your code properly, including database interactions.

By default, when you are running mysqli_query() or — preferably — prepare()/bind()/execute() , mysqli will just silently return false if a query fails, telling you nothing of the reason.

You’ll notice the error only when the following command, when trying to use the query result, will raise an error, such as

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given.

Unfortunately, this error message tells you nothing of the actual problem. It just bluntly says that your query failed, and nothing else.

It happens because by default mysqli is configured to remain silent if a query returned an error. So you can tell that’s an extremely inconvenient behavior.

Luckily, mysqli can be configured to throw a PHP exception in case of a mysql error. It means that a PHP error will be thrown automatically every time a query returns an error, without any effort on your part!

Let’s see a small demonstration:

 // first of all set PHP error reporting in general
// in order to be sure we will see every error occurred in the script
ini_set('display_errors',1);
error_reporting(E_ALL);

/*** THIS! ***/
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
/*** ^^^^^ ***/
$db = $mysqli = new mysqli($host, $user, $pass, $database);

$res = $db->query("apparently not a valid SQL statement");
$res->fetch_assoc(); 

And run your script again. Whoa! An error appears in a puff of smoke, explaining what was the problem:

Fatal error: Uncaught exception 'mysqli_sql_exception'
with message 'You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version
for the right syntax to use near 
'apparently not valid SQL string' at line 1' in /home/www/test.php:16
Stack trace:
#0 /home/www/init.php(16): mysqli->query('apparently not . ')
#1 /home/www/test.php(2): include('/home/www/. ')
#2  
thrown in /home/www/init.php on line 16

As you can see, this is quite a detailed information, including the erroneous part of the query, and even a stack trace that helps you to find a particular function that called the erroneous query.

So again: just make sure that you always have this magic line before mysqli_connect() in all your scripts:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); 

What to do with the error message you get?

It should be a knee-jerk reflex to copy and paste the error message in Google search. It will lead you to several Stack Overflow questions related to this particular problem with many answers with solutions.

However, there is another possibility. You can try to actually read the error message you get. Usually, the error message explains it pretty straightforward, what is the error. However, some errors could be cryptic. You have to understand some considerations used in Mysql error messages:

• in case of a syntax error, the cited query part begins right after the error.
• it means that, therefore, if the error is at the very end of the query, the cited query part would be an empty string. Most likely it happens when you add a variable directly to the query and this variable happen to be empty (yet another reason to use prepared statements as they will make such errors just impossible to appear.

Related articles:

• Mysqli tutorial (how to use it properly)
• How to connect properly using mysqli
• How to check whether a value exists in a database using mysqli prepared statements
• Mysqli helper function
• Why mysqli prepared statemens are so hard to use?
• Authenticating a user using mysqli and password_verify()
• How to get the number rows that has been actually changed
• Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given.
• Mysqli examples
• Mysqli’s features you probably would like to know about
• How to run INSERT query using Mysqli
• How to use mysqli properly

Got a question?

I am the only person to hold a gold badge in , and on Stack Overflow and I am eager to show the right way for PHP developers.

Besides, your questions let me make my articles even better, so you are more than welcome to ask any question you have.

SEE ALSO:

• Top 10 PHP delusions
• PDO Examples
• Mysqli Examples
• Principles of web-programming
• Mysqli tutorial (how to use it properly)
• How to connect properly using mysqli
• How to check whether a value exists in a database using mysqli prepared statements
• Mysqli helper function
• Why mysqli prepared statemens are so hard to use?
• Authenticating a user using mysqli and password_verify()
• How to get the number rows that has been actually changed
• Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given.
• Mysqli examples
• Mysqli’s features you probably would like to know about
• How to run INSERT query using Mysqli
• How to use mysqli properly

Latest comments:

• 24.07.23 01:16
  Tim Dawson for PDO Examples:
  Further to my previous reply to your e-mail, I now find that the following, with double quotes.
  read more
• 23.07.23 16:47
  Tim Dawson for PDO Examples:
  I have a web site where visitors can look at accommodation reviews. A selection of reviews is.
  read more
• 17.07.23 21:18
  Jim for (The only proper) PDO tutorial:
  Thanks for the reply! I was hoping you would know if what I’m attempting is even possible! :).
  read more
• 16.07.23 00:35
  Jim for (The only proper) PDO tutorial:
  Hi, I just discovered this site today, so first and foremost, thank you for all your work.
  read more
• 27.06.23 05:30
  Jeff Weingardt for MVC in simpler terms or the structure of a modern web-application:
  I was just curious what you find the most effective way to learn php and become proficient? so.
  read more

Add a comment

Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator’s review.

Comments:

Reply:

Hello Daniel! That’s an interesting question. The simplest solution would be to upgrade the PHP version. Starting from 8.0 bind_param is throwing exceptions all right. For the older versions, it depends on what you need the exception for. Normally, there should be no difference: a Warning as in error all the same, it will be reported and the next call to execute will halt the code execution. But if you really need and exception, you can add a simple error handler to your code that would convert all errors to excepions, like

set_error_handler(function ($level, $message, $file = '', $line = 0)
 throw new ErrorException($message, 0, $level, $file, $line);
>); 

See more at https://phpdelusions.net/articles/error_reporting Hope some of these suggestions will help. If not, then please let me know.

a bit misleading if all you have to do is call mysqli_error() and mysqli_errno() no need to reconfigure anything.

Reply:

Hello Chris! There are two problems with this mindset. First, the logic is somewhat inverted here: as long as you have to run only one SQL query, there is not much difference. But even with a dozen, not to mention hundreds of SQL queries, this «call mysqli_error() and mysqli_errno()» after every query becomes «a bit misleading». Now compare it with just «reconfiguring» a single line of code. Second, the question is, what you’re going to do with all these mysqli_error() and mysqli_errno() calls. Are you sure you are going to use them properly? And what if some day you will decide to change that behavior? For example, now you want to die() with the error message but eventually you will learn that it’s just gross and it needs to be changed. So, which approach will make it simpler: when you have just a single place where error reporting is configured, or when you have dozens to hundreds places where you need to rewrite the code?

Источник