Page 1 of 1

Determine if table exists, if it's empty, without errors...?

Posted: Thu Jul 17, 2008 6:26 pm
by JAB Creations
It seems as though I've got the MySQL down but not the PHP as far as trying to determine if a table exists and then checking to see if it's empty.

With the SHOW TABLES MySQL query I ended up pulling an array of columns/rows of tables however only in PhpMyAdmin could I see the full list where as in the browser it appeared as though I could not get the second row with the second table's name...so in my mind last night I was unable to access a subarray in some sort of sense...?

Code: Select all

$check1 = mysql_query("SHOW TABLES FROM $db_name");
Secondly once I select a table (presuming it exists) I want to determine if it's empty of not. I read online that you want to check for the number of columns so while I have the idea/code for MySQL I'm coming up short in regards to PHP...

Code: Select all

$result = mysql_query("SELECT count(*) FROM $db_name");
//$row = mysql_fetch_row($result);
echo $result;
So I want to determine if a table exists without spawning any error messages.

Then (if the table exists) {I want to determine if that table is empty}.

Suggestions please?

Re: Determine if table exists, if it's empty, without errors...?

Posted: Thu Jul 17, 2008 6:43 pm
by EverLearning
If you're using MySql 5, you can use following query to find out the number of rows in a table

Code: Select all

SELECT TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'table_name';
For more information take a look at the MySql manual: INFORMATION_CHEMA

Re: Determine if table exists, if it's empty, without errors...?

Posted: Thu Jul 17, 2008 6:48 pm
by JAB Creations
I suppose that's an improvement and I thank you for that...

...however I really need to figure out what to do with it in regards to --> PHP <--.

Re: Determine if table exists, if it's empty, without errors...?

Posted: Thu Jul 17, 2008 7:24 pm
by JAB Creations
...to make this as simple as possible...all I want to do is echo the result. If it's in an array then I'll echo something along the lines of $row[0]. If it's not a variable ($row) or an array ($row[0].$row[1]...etc)) that I can echo then however I can echo it is what I need to know. Once I'm that far I'll know how to interact with it.

Re: Determine if table exists, if it's empty, without errors...?

Posted: Thu Jul 17, 2008 7:33 pm
by EverLearning
Something like this?

Code: Select all

$sql = "
    select TABLE_ROWS
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME = '$table_name' AND TABLE_SCHEMA = '$db_name'
";
$result = mysql_query($sql);
if ($result) {
    $row = mysql_fetch_assoc($result);
 
    if ($row) {
        $numRows = $row['TABLE_ROWS'];
        echo "Num rows: $numRows";
    } else {
        echo "table '$table_name' doesn't exits in db '$db_name'";  }
 
}
 

Re: Determine if table exists, if it's empty, without errors...?

Posted: Thu Jul 17, 2008 7:43 pm
by JAB Creations
Exactly like that! That has got to be one of the most spot-on replies I've ever gotten! Thank you so very much! :mrgreen: