How to check the existence of mysql database?

Question

$pdo_mysql = new PDO("mysql:host=localhost", "ales", "alespass");
$result = $pdo_mysql->query("SHOW DATABASES LIKE `dbname`");
if ($result) { //not work.
    print "no";
} else {
    print "yes";
}

You can not use CREATE DATABASE IF NOT EXISTS or new PDO("mysql:host=localhost;db=dbname" ...

Answer 1

You have a typo error in your comment on @robin s answer, I think the condition should be if (!empty($result)) with that exclamation mark. It is printing not exists because $result is not empty when database exists. Therefore using this $result = $pdo_mysql->query("SHOW DATABASES LIKE '" . DB_NAME . "'"); if (!empty($result)) { print "exists"; } else { print "no exists"; } should work. I hope that helps

Answer 2

CREATE DATABASE IF NOT EXISTS

should do the trick. If it doesn't, check for the existence first via

SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'dbname'

it will return you the database name itself, so you can check if the count of the query is > 0, the result is != 0...and so on. However you like.

The problem with your queries is the wrong use of backticks, you need to use single quotes, not backticks.

' instead of `

Answer 3

Manual:

PDO::query() returns a PDOStatement object, or FALSE on failure.

Your query is valid and should never fail (ignoring possible lower level issues).

$result will always evaluate to TRUE-ish, so the flow will always enter the if ($result) block.

(if (empty($result)) makes little to no sense)

You need to check whether the query returns zero row (zero result is not an error).

---

On second thought:

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

It is safer to do something like this:

if ($result->fetch() === FALSE) {
    // result set is empty
}