Skip to content
Advertisement

How do store a list of sorted tables with an SQL SHOW TABLES statement?

I am using the following code to show all tables, but how do I store the tables into a sorted array:

$sql = "SHOW TABLES FROM ".$counter_database." ORDER BY TABLES DESC";
$result = $conn->query($sql);
$outArray = array();
while($table = $result->fetch_assoc()) {
    $outArrayx[] = $table;
}

The following code spits out the data:

foreach ($outArrayx as $key => $value) {
    foreach ($value as $keys => $values) {
        $sql = "SELECT * FROM ".$values." WHERE user_id='0'"; 
        $result = $conn->query($sql);
        if (strpos($values, "ckia") === 0) {
            while($table = $result->fetch_assoc()) {
                $outArray[] = $table;
            }
        }
    }
}

Advertisement

Answer

Put the table names in a PHP array, and then sort the array. And if you don’t nest it in the row, you don’t need nested loops when you process it.

You can’t use ORDER BY with SHOW TABLES, but you can use LIKE to select only tables that fit a pattern. Then you don’t need the if statement in your loop.

$sql = "SHOW TABLES FROM ".$counter_database." LIKE 'ckia%%'";
$result = $conn->query($sql);
$outArray = array();
while($table = $result->fetch_row()) {
    $outArrayx[] = $table[0];
}

sort ($outArrayx);

foreach ($outArrayx as $table) {
    $sql = "SELECT * FROM ".$table." WHERE user_id='0'"; 
    $result = $conn->query($sql);
    while($row = $result->fetch_assoc()) {
        $outArray[] = $row;
    }
}

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement