checking if a table exists

[Derokorian]

Hi all,

I wrote this function to check if a table exists in the database or not. It seems to work just wondering if this is the best method or if you have any comments. =D Thanks in advance!

function checkTable($db,$table) {
	// check if $db is a valid MySQLi resource
	if( !is_object($db) || get_class($db) != 'mysqli' ) {
		trigger_error('First argument is not a valid mysqli resource as supplied to checkTable.',E_USER_NOTICE);
		return FALSE;
	}
	$sql = 'SELECT 1 FROM `'.$table.'` LIMIT 0';
	if( $db->query($sql) )
		return TRUE;
	else
		return FALSE;
}

[bradgrafelman]

Thread moved to Code Critique forum.

While I think it's commendable you added some sanity checks to make sure $db is what you say it should be, note that you could also (or alternatively?) use type hinting (man page: [man]oop5.typehinting[/man]) in the parameter list to indicate that $db should be a MySQLi object.

Also note that you might add some more checks to verify that you're actually connected to a MySQL server, know the name of the database you want to check, etc.

Finally, note that if the table doesn't actually exist you'll be generating a SQL error by trying to SELECT data from a nonexistent table. I personally would opt for a better approach such as executing a query like the following:

SELECT COUNT(*)
FROM information_schema.tables 
WHERE table_schema = '[database name]' 
AND table_name = '[table name]';

and check if any rows were returned.

[Derokorian]

bradgrafelman;10990388 wrote:

While I think it's commendable you added some sanity checks to make sure $db is what you say it should be, note that you could also (or alternatively?) use type hinting (man page: [man]oop5.typehinting[/man]) in the parameter list to indicate that $db should be a MySQLi object.

Ah never heard of type hinting before, will definitely see about switching that around!

bradgrafelman;10990388 wrote:

Also note that you might add some more checks to verify that you're actually connected to a MySQL server, know the name of the database you want to check, etc.

How would I do that? Right now I have my $db = new mysqli supply the DB name on instantiation, and return an error if connection fails. I'm not sure how to check the $db object for this information...

bradgrafelman;10990388 wrote:

Finally, note that if the table doesn't actually exist you'll be generating a SQL error by trying to SELECT data from a nonexistent table. I personally would opt for a better approach such as executing a query like the following:

SELECT COUNT(*)
FROM information_schema.tables 
WHERE table_schema = '[database name]' 
AND table_name = '[table name]';

and check if any rows were returned.

Would I then need to pull the db name from my constant defined in the config or should I pass it as an argument (maybe defaulting to the constant?) Also wrote it to check the value of count, because it will return 1 row even if count is 0 will it not?

[johanafm]

Derokorian;10990391 wrote:

because it will return 1 row even if count is 0 will it not?

Yes, you will either have to inspect the returned value to see if it is 0 or 1, or you could still use your initial approach in this regard, i.e. SELECT 1 [rest as recommended by brad], and check number of rows in the result set.

[Derokorian]

Question about type hinting: Should I still have if( !is_object($db) || get_class($db) != 'mysqli' ) I tested to see that php throws a catchable fatal error, so does that mean that this check is superfluous?

[NogDog]

Yep...no reason to test for it within the function if you've type-hinted that parameter. (You may, however, want a try/catch block around the code that would call that function.)

PS: You may want to test for NULL, though, as a null value will not trigger an exception for a type-hinted parameter.

[Derokorian]

NogDog;10990506 wrote:

You may want to test for NULL, though, as a null value will not trigger an exception for a type-hinted parameter.

Tested with:

var_dump(checkTable(NULL,'FakeTable'));

got this response:

Catchable fatal error: Argument 1 passed to checkTable() must be an instance of mysqli, null given, called in ... on line 4 and defined in ... on line 93

[Weedpacket]

The problem is that it's not an exception, it's an error (of type E_RECOVERABLE_ERROR), and "catchable" doesn't refer to exception handling. I know, it's silly, but I didn't write the message.

What it actually refers to is the use of [man]set_error_handler[/man].

[Derokorian]

Yeah I found that on stackoverflow thats why I removed it from my post lol 😛