
PHP Fatal error: Uncaught mysqli_sql_exception: No database selected (SOLVED)
September 6, 2023
Let's look at a small PHP code that tries to connect to a MySQL or MariaDB DBMS and execute a query with a “SELECT” statement:
<?php $db_user = "root"; $db_password = ""; $mysqli = new mysqli("localhost", $db_user, $db_password); if ($mysqli->connect_errno) { printf("Somehow we failed: %s\n", $mysqli->connect_error); exit(); } $query = "SELECT * FROM TestTABLE;"; $result = $mysqli->query($query); while ($row = $result->fetch_assoc()) { echo $row["id"] . ': ' . $row["test_column"] . PHP_EOL; }
This script crashes with an error:
PHP Fatal error: Uncaught mysqli_sql_exception: No database selected in /home/mial/test/php/no_db_selected.php:13 Stack trace: #0 /home/mial/test/php/no_db_selected.php(13): mysqli->query('SELECT * FROM T...') #1 {main} thrown in /home/mial/test/php/no_db_selected.php on line 13 Fatal error: Uncaught mysqli_sql_exception: No database selected in /home/mial/test/php/no_db_selected.php:13 Stack trace: #0 /home/mial/test/php/no_db_selected.php(13): mysqli->query('SELECT * FROM T...') #1 {main} thrown in /home/mial/test/php/no_db_selected.php on line 13
The cause of the problem is indicated in the error text: “No database selected”. Because no database is specified, MySQL or MariaDB simply don't know which one the query is for.
A very similar issue is covered in the article “ERROR 1046 (3D000): No database selected” in MySQL and MariaDB (SOLVED). But there this error occurs when connecting to the database server using a client with a command line interface. The solutions to this problem shown in that note are also geared towards situations where you make queries to a MySQL or MariaDB server in a command line client.
In this note, I will show how to fix this error in a PHP script. Although I run PHP scripts on the command line, everything in this article also applies when running PHP scripts on a web server and outputting the results to a web browser.
1. Specify the database with a pre-query with a “USE” statement
Before executing the main query, execute the query with the “USE” statement. As with the console client, this expression allows you to specify the database that subsequent queries will target.
$query = "USE TestDB;"; $mysqli->query($query);
Full PHP script code:
<?php $db_user = "root"; $db_password = ""; $mysqli = new mysqli("localhost", $db_user, $db_password); if ($mysqli->connect_errno) { printf("Somehow we failed: %s\n", $mysqli->connect_error); exit(); } $query = "USE TestDB;"; $mysqli->query($query); $query = "SELECT * FROM TestTABLE;"; $result = $mysqli->query($query); while ($row = $result->fetch_assoc()) { echo $row["id"] . ': ' . $row["test_column"] . PHP_EOL; }
As you can see, the script now completed without errors and displayed the simple contents of the table.
What you have to remember when using the “USE” statement in a PHP script:
1) The selected database with “USE” is saved within the current connection session to the MySQL (MariadDB) server. That is, for the created mysqli instance, the selected database is remembered until the connection will be closed or the PHP script completes. This is a bit counter-intuitive. Simply put, you do NOT need to combine a query with a database select and a table query, like this:
$query = "USE TestDB; SELECT * FROM TestTABLE;";
These two queries can be run separately.
By the way, this is why the effect of choosing a character set for a connection is preserved:
$mysqli->query("SET NAMES utf8mb4");
2) You can use the “USE” statement again at any time to select a different database. This will automatically cancel the previous selection – just like when working through the MySQL (MariaDB) client with a command line interface.
3) By the way, if you intend to work with several databases in one script, then instead of juggling “USE” statements, it is more productive to open two or more separate connections to the MySQL server. For each of these connections, you can choose your own database.
2. Specify database when connecting to MySQL and MariaDB server
When opening a new connection to the MySQL server, you can specify the database as the fourth optional argument, for example:
$mysqli = new mysqli("localhost", $db_user, $db_password, "TestDB");
Full PHP script code (now without the “USE” statement):
<?php $db_user = "root"; $db_password = ""; $mysqli = new mysqli("localhost", $db_user, $db_password, "TestDB"); if ($mysqli->connect_errno) { printf("Somehow we failed: %s\n", $mysqli->connect_error); exit(); } $query = "SELECT * FROM TestTABLE;"; $result = $mysqli->query($query); while ($row = $result->fetch_assoc()) { echo $row["id"] . ': ' . $row["test_column"] . PHP_EOL; }
The script completed again without error.
This syntax is very convenient and, perhaps, this option for selecting a database is the most common.
You can select a different database at any time using a “USE” statement. But I would recommend that instead of using the “USE” statement, open another connection to the MySQL server and use different connections to work with different databases.
3. Specify the database in the query
In “SELECT” queries and other expressions that use a table name, you can specify the “path” to the table in the “DATABASE.TABLE” format. That is, you should start with the database name, then a period, and then the table name.
Pay special attention to the following line:
$query = "SELECT * FROM TestDB.TestTABLE;";
Full script code:
<?php $db_user = "root"; $db_password = ""; $mysqli = new mysqli("localhost", $db_user, $db_password); if ($mysqli->connect_errno) { printf("Somehow we failed: %s\n", $mysqli->connect_error); exit(); } $query = "SELECT * FROM TestDB.TestTABLE;"; $result = $mysqli->query($query); while ($row = $result->fetch_assoc()) { echo $row["id"] . ': ' . $row["test_column"] . PHP_EOL; }
Execution result without error:
You need to know this syntax because it is impossible to do without it when querying multiple tables from different databases.
Using this syntax you can query any database without changing the current database with the “USE” statement.
The “SHOW TABLES” statement can also specify a database and therefore does not need to use the “USE” statement first:
$query = "SHOW TABLES FROM TestDB;"; $result = $mysqli->query($query);
Related articles:
- PHP not displaying emoji from MySQL/MariaDB database (SOLVED) (100%)
- “ERROR 1366 (22007): Incorrect string value” in MySQL/MariaDB (SOLVED) (100%)
- How to list MySQL/MariaDB users and their privileges (using RDBMS client and PHP) (82.6%)
- How to list columns in MySQL/MariaDB. How to check if a column exists in a table in PHP (82.6%)
- ERROR 1044 (42000): Access denied for user 'mial'@'localhost' to database 'TestDB'. Can't create MySQL database (SOLVED) (76.4%)
- Firmware files for drivers in Linux (RANDOM - 1.7%)