how to see if database exists with PDO

Question

So I recently decided to switch to PDO due to the mysqli prepared statement complexity and irregularities. This was my mysqli function to test for a database:

public static function is_database($database) {
    self::connect();
    if( mysqli_select_db(self::$conn,$database) ) {
        self::$dbname = $database;
        return true;
    } else {
        return false;
    }
    self::disconnect();
}

The only thing I've read so far about PDO and a database of anything was this:

$pdo->exec("use database");

Which is not what I want, unless using this with a try catch would work. Something of this sort

public static function is_database($database){
   self::connect();
   try {
      self::$conn->exec('use '.$database);
      return true;
   } catch(PDOException $e){
     print($e->getMessage();
     return false;
     die();
   }
}

Need a little assistance for the 1 hour new PDO user. Thanks for any advice.

PDO doesn't include a built-in function to switch databases like mysqli. Instead of trying to switch to the database, maybe you should be using a count(*) on a query like this: http://stackoverflow.com/questions/838978/how-to-check-if-mysql-database-exists — Devon Bessemer, May 06 '15 at 02:20
I wasn't trying to switch just to see if it actual exists like what you said using count — EasyBB, May 06 '15 at 02:33

score 5 · Answer 1 · edited Apr 18 '22 at 09:56

5

In accordance with the query found at How to check if mysql database exists

$stmt = $pdo->query("SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'DBName'");
return (bool) $stmt->fetchColumn();

We check the count which will return 1 or 0 and type cast it as an boolean (true/false).

Of course this requires an existing connection through PDO. This wouldn't work for you to check the database before the first connection.

edited Apr 18 '22 at 09:56

Your Common Sense

156,878
40
214
345

answered May 06 '15 at 02:22

Devon Bessemer

34,461
9
69
95

You can't connect with PDO without a db? ` new PDO('mysql:host=localhost;', USERNAME, PASSWORD);` and be null and it will connect?? – EasyBB May 06 '15 at 02:35
AFAIK, you cannot. PDO works for multiple DBMS, not just MySQL. Not all DBMS have multiple databases you can switch between. – Devon Bessemer May 06 '15 at 02:37
If you were trying to check if a database exists initially, you could just setup a try catch block during the PDO instantiation. However, you would need to check the error message for why the connection failed. There most likely is an error code specific to a database not existing. – Devon Bessemer May 06 '15 at 02:39
I don't see many use cases for if a database exists. Most web applications should be restricted to a single database. – Devon Bessemer May 06 '15 at 02:41
well its a startup cms, the user needs to enter the db name and it checks if it is correct. – EasyBB May 06 '15 at 02:41
I think at that point, you could just display the PDOException message to the user if the database connection fails. – Devon Bessemer May 06 '15 at 02:42
Yeah I'll have to think over the best possible solution at this point. Thanks for the information though. – EasyBB May 06 '15 at 02:43
You're welcome. I added an example on the try catch with the constructor, but I think you already grasped what I was saying. – Devon Bessemer May 06 '15 at 02:46

OMEGA · Answer 2 · 2022-04-18T15:36:00.763

-1

A very simple answer execute your code in "try-catch" exception; and if you want to throw an error then echo your error and if you don't then just live empty your catch block; how useful try catchs are

if DB does not exist it will create
error of your choice or no error

the example is given below.

        try {
            $conn->exec($sql);
        } catch (PDOException $th) {
            echo "<br> sql error";
        }

edited Apr 18 '22 at 15:36

answered Apr 18 '22 at 09:44

OMEGA

9
2

There are **many** database errors. It could be Too many connections or Mysql has gone away, or [anything out of 1000s possible errors](https://dev.mysql.com/doc/mysql-errors/8.0/en/global-error-reference.html). You **must** compare the error message/code with the expected one. Otherwise this code will do more harm than good. Also, `echo "
sql error";` makes no sense whatsoever – Your Common Sense Apr 18 '22 at 15:40
Yes, `echo "
SQL error";` make no sense because I don't want to make sense, I don't want any error. and if you want to compare then use if-else or use compare sign there are a lot of options for a lot of errors. I want to say that this is for a beginner not for professionals, `this is for checking that DB exists or not`. and ya if you wanna use a professional way then read more spend more time searching and a lot of ways to get your answer. – OMEGA Apr 18 '22 at 15:58
And yaa your source is very useful I never knew that `THANK YOU`. – OMEGA Apr 18 '22 at 16:05
"read more spend more time searching and a lot of ways to get your answer" - this is **not** how we answer questions here on Stack Overflow. – Your Common Sense Apr 18 '22 at 16:09

how to see if database exists with PDO

2 Answers2