List all the tables in your MySQL database using PHP

Question

I have a hard time trying to list all the tables in my database.

I tried

<?php 

//Configuration
$dbname      = 'local';
$user        = 'root';
$host        = '127.0.0.1';
$pass        = '';
$date        = date('Y-m-d');
$export_type = 'mysql'; // option : mysql | psql
$file_name   = $date.'-portal';
$file_path   = $file_name;



// Create connection
$conn = mysqli_connect($host, $user, $pass);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Connected successfully";

$sql = "SHOW TABLES FROM $dbname";
$res = mysqli_query($conn, $sql);

if($res != false){

    echo "Connected successfully";
    $FILE = fopen("output.csv", "w");

    $table = array();
    while($row = mysql_fetch_array($res)){
       $table[] = $row['0'];
    }

    foreach($tables as $table) {
        $columns = array();
        $res = mysqli_query($conn, "SHOW COLUMNS FROM $table");

        while($row = mysql_fetch_array($res, MYSQL_NUM)) {
            $columns[] = "$row[0]";
        }
        fwrite($FILE, implode(",", $columns)); fwrite("\n");
        $resTable = mysqli_query($conn, "SELECT * FROM $table");

        while($row = mysql_fetch_array($resTable, MYSQL_NUM)) {
            fwrite($FILE, implode(",", $row)); fwrite("\n");
        }
    }
}else{
    die(mysql_error());
}

?>

Result

if($res != false){
//.. everything in here never get executed
}

`$res` kept returning `false`.

What did I do wrong that could have lead to this ?

mysql_fetch_array should be mysqli_fetch_array, mysql_error should be mysqli_error($conn). Any better? — bishop
– bishop, Commented Jun 16, 2016 at 3:11

Pini Cheyni · Accepted Answer · 2016-06-19 12:12:20Z

3

You can always execute the query:

Show tables;

After you selected database.

By the way you also can execute:

Show databases;

To list all of the databases your current user has permission to view.

edited Jun 19, 2016 at 12:12

answered Jun 16, 2016 at 3:55

Pini Cheyni

5,5072 gold badges45 silver badges58 bronze badges

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

Comments

Niklesh Raut · Accepted Answer · 2016-06-16 03:19:02Z

2

Use db in your connection

 mysqli_connect($host, $user, $pass, $dbname);

And use query like this

 $sql = "SHOW TABLES";

answered Jun 16, 2016 at 3:19

Niklesh Raut

34.9k17 gold badges82 silver badges112 bronze badges

5 Comments

code-8 Over a year ago

Works. My $res variable now return true. But do you know how to fix mysql_fetch_array() expects parameter 1 to be resource object given ?

Niklesh Raut Over a year ago

Check mysqli you took mysql not mysqli here

code-8 Over a year ago

I'm accepting your answer. Thanks for your help mate.

code-8 Over a year ago

Last, question, I promise, do you know why $res = mysqli_query($conn, "SHOW COLUMNS FROM $table"); keep returning false. Did I do anything unusual again ?

Niklesh Raut Over a year ago

Check this : stackoverflow.com/questions/1526688/…

GROVER. · Accepted Answer · 2016-06-16 03:14:38Z

1

You need to pass through your database in the connection script.

Like so:

$conn = mysqli_connect($host, $username, $pass, $dbname);

Then, when you want to pull rows from a table, you do it like this:

mysqli_query($conn, "SELECT rows FROM table");

One of the reasons this wasn't working for you was because you weren't passing through your database name through the connection. Also, rather than doing the above query, you selected a table from a database; rather than a row from a table.

Also, I noticed that you're using the mysql_* error output on the last line.

answered Jun 16, 2016 at 3:14

GROVER.

4,4082 gold badges31 silver badges78 bronze badges

3 Comments

code-8 Over a year ago

First, I want to query all the tables in my database, Not all the rows in my table.

code-8 Over a year ago

Second, I've added the $dbname as my fourth arguments and my $res variable still returning false - and that still preventing my entire block of code from executing.

GROVER. Over a year ago

I'm well aware of this. You have to do this another way. There are various sources specifying how to do so. Also, you can change which rows you would like to select. I used SELECT * just as an example

tttony · Accepted Answer · 2016-06-16 04:06:39Z

1

Here is a working version

Changes:

Added $dbname to mysqli_connect function
Added the backtick ` char between the table names, to avoid errors with reserved keyword from MySQL
Changed mysql_ functions to mysqli_
Close the file
Close the connection

Here is the code

NOTE: sorry I don't why, but when I pasted the code in the answer, all de code identation was messed up, even trying to indent it properly, I wasted like 10 minutes :(

answered Jun 16, 2016 at 4:06

tttony

5,1324 gold badges29 silver badges45 bronze badges

Collectives™ on Stack Overflow

List all the tables in your MySQL database using PHP

I tried

Result

4 Answers 4

Comments

5 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

I tried

Result

4 Answers 4

Comments

5 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related