MySQL SHOW TABLES from DATABASE except one

Question

So ive got a fully working page to display a dropdown list of all tables avaliable in my database. however i want it to display the list of all tables except one (as one of the tables contains user information which i dont want shown...) Is there a way to do this?

This is the section of the code ive got so far... $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;
}
if (mysql_select_db($dbname, $conn))
{
?>
    <form method="post" action="Browse.php">
    <select name="tables">
    <?php
    while ($row = mysql_fetch_row($result)) {
    ?>    
    <?php
        echo '<option value="'.$row[0].'">'.$row[0].'</option>';
    }
    ?>
    </select>
    <input type="submit" value="Show">
</form>
<?php
//mysql_free_result($result);
if (isset($_POST) && isset($_POST['tables']))
{
    $tbl=$_POST['tables'];
    //echo $_POST['tables']."<br />";
    $query="SELECT * from $tbl ORDER BY title ASC";
    $res=mysql_query($query);
    //echo $query;
    if ($res)
    {
    ?>
    <table border="1">
    <?php
        while ( $row = mysql_fetch_array($res))
        {
            echo "<tr>";
            //echo "<td>".$row[0]."</td>";
            echo "<td>".$row[1]."</td>";
            //echo "<td>".$row[2]."</td>";
            //echo "<td>".$row[3]."</td>";      
            echo "</tr>";
        } ?>
    </table>
    <?php
    }
}
}

Its no biggie if there isnt an easy way to do so as i've figured out that by doing the $query="SELECT * from $tbl ORDER BY title ASC"; it wont display the data (as there isnt a title column in the user details table)..but i dont want the table name to be shown in the dropdown box

Just a general query really... Thanks

Vincent · Accepted Answer · 2020-03-06 19:52:42Z

5

You can use a where clause with the SHOW TABLES statement.

The trick is knowing the name of the column that's generated by the SHOW TABLES statement as its name depends on your database name. The name of the column will be "tables_in_{your_dbname}". So if your database name is "blah" the column name will be "tables_in_blah".

So if the table you want to omit from your result set is called "secret_table" you can execute the statement like so:

SHOW TABLES WHERE tables_in_blah <> 'secret_table';

Or if you want to use a wildcard you can do it like so:

SHOW TABLES WHERE tables_in_blah NOT LIKE '%secret_table%';

answered Mar 6, 2020 at 19:52

Vincent

2,26733 silver badges42 bronze badges

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

Comments

Michael Durrant · Accepted Answer · 2012-03-11 21:30:35Z

3

Try this:

SELECT table_name FROM information_schema.tables  
WHERE table_schema = 'your_data_base_name'  
AND table_name NOT LIKE '%USER_TABLE%';

Without like

SELECT table_name FROM information_schema.tables  
WHERE table_schema = 'your_data_base_name' AND table_name <> 'USER_TABLE';

edited Mar 11, 2012 at 21:30

Michael Durrant

97.2k102 gold badges352 silver badges536 bronze badges

answered Mar 11, 2012 at 21:26

Oyeme

11.3k4 gold badges46 silver badges66 bronze badges

Comments

Neil · Accepted Answer · 2012-03-11 21:28:16Z

2

Another alternative is to avoid outputting the table in question when you construct the list of options:

<?php
while ($row = mysql_fetch_row($result)) {
    if ($row[0] != 'my_private_table_name') {
        echo '<option value="'.$row[0].'">'.$row[0].'</option>';
    }
}
?>

answered Mar 11, 2012 at 21:28

Neil

55.5k8 gold badges65 silver badges74 bronze badges

Collectives™ on Stack Overflow

MySQL SHOW TABLES from DATABASE except one

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related