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, Jun 16 '16 at 03:11
The PDO version: http://stackoverflow.com/a/27467588/4357238 — Edward, Jun 16 '16 at 03:21

Pini Cheyni · Answer 1 · 2016-06-19T12:12:20.653

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 '16 at 12:12

answered Jun 16 '16 at 03:55

Pini Cheyni

5,073
2
40
58

score 2 · Accepted Answer · answered Jun 16 '16 at 03:19

2

Use db in your connection

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

And use query like this

 $sql = "SHOW TABLES";

answered Jun 16 '16 at 03:19

Niklesh Raut

34,013
16
75
109

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` ? – code-8 Jun 16 '16 at 03:21
1

Check mysqli you took mysql not mysqli here – Niklesh Raut Jun 16 '16 at 03:24
I'm accepting your answer. Thanks for your help mate. – code-8 Jun 16 '16 at 03:25
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 ? – code-8 Jun 16 '16 at 03:31
Check this : http://stackoverflow.com/questions/1526688/get-table-column-names-in-mysql – Niklesh Raut Jun 16 '16 at 03:36

score 1 · Answer 3 · answered Jun 16 '16 at 03:14

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 '16 at 03:14

GROVER.

4,071
2
19
66

First, I want to query all the tables in my database, Not all the rows in my table. – code-8 Jun 16 '16 at 03:18
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. – code-8 Jun 16 '16 at 03:19
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 – GROVER. Jun 16 '16 at 03:19

score 1 · Answer 4 · answered Jun 16 '16 at 04:06

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 :(

List all the tables in your MySQL database using PHP

I tried

Result

4 Answers4