Check if a MySQL database is empty using PHP

Question

Once a database is created how can it be checked if the database contains tables or table structure using PHP?

Scenario:

When the user accesses the system first time it creates a default database with no tables, then in the next step the user is supposed to import the customized database structure by uploading .sql file. I need to check if they have imported the database or has skipped that step by checking if the default database that was automatically created earlier has any table structure.

When I executed mysql_list_tables(defaultDBName) it just returns "Your SQL query has been executed successfully", but no result set in phpMyAdmin.

Rajat Singhal · Accepted Answer · 2012-10-06 17:47:17Z

7

<?php
$dbname = 'mysql_dbname';

if (!mysql_connect('mysql_host', 'mysql_user', 'mysql_password')) {
    echo 'Could not connect to mysql';
    exit;
}

$sql = "SHOW TABLES FROM $dbname";
$result = mysql_query($sql);

if (!$result) {
    echo "DB Error, could not list tables\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

while ($row = mysql_fetch_row($result)) {
    echo "Table: {$row[0]}\n";
}

mysql_free_result($result);
?>

from Documentation http://php.net/manual/en/function.mysql-list-tables.php

You can use if(mysql_num_rows($result) > 0) instead of looping through $result..

answered Oct 6, 2012 at 17:47

Rajat Singhal

11.3k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

janenz00 · Accepted Answer · 2012-10-06 17:55:06Z

3

The use of mysql_query and mysql_list_tables is discouraged. Instead, you are advised to use mysqli functions.

<?php
    $link = mysqli_connect("localhost", "my_user", "my_password", "world");

    if ($result = mysqli_query($link,"SHOW TABLES [FROM db_name] [LIKE 'pattern']")) {
        // Check mysqli_num_rows($result). 0 means you do not have your table.
    }

 ?>

answered Oct 6, 2012 at 17:55

janenz00

3,3105 gold badges30 silver badges37 bronze badges

Comments

kuncajs · Accepted Answer · 2012-10-06 17:53:54Z

1

If you feel you will need some where clauses or something like this in future then use:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'somedbname'

If you are looking for particular table or tables you can use

SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbname' AND table_name = 'tablename'

If you want more information then see what

SELECT * FROM information_schema.tables WHERE table_schema = 'somedbname'

returns

answered Oct 6, 2012 at 17:53

kuncajs

1,0981 gold badge9 silver badges21 bronze badges

Comments

JvdBerg · Accepted Answer · 2012-10-06 17:46:24Z

0

You can use the show tables command to check what tables there are in the database. If it comes back empty, then the database is empty.

answered Oct 6, 2012 at 17:46

JvdBerg

21.8k8 gold badges40 silver badges55 bronze badges

Collectives™ on Stack Overflow

Check if a MySQL database is empty using PHP

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related