DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Test If MySQL Table Exists With PHP.

01.25.2007
| 61501 views |
  • submit to reddit
        Tests if table exists in MySQL database  

<?
function table_exists ($table, $db) { 
	$tables = mysql_list_tables ($db); 
	while (list ($temp) = mysql_fetch_array ($tables)) {
		if ($temp == $table) {
			return TRUE;
		}
	}
	return FALSE;
}

/** How to use it **/
if (table_exists(test_table, my_database)) {
	echo"Yes the table is there.";
}
?>

Drakxter, http://www.oxyscripts.com/itemdisplay.php?id=1003&code=yes

||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

And a shorter way. In the above example mysql_list_tables is deprecated in favor of mysql_query().

<?
// here is a much more elegant method to check if a table exists ( no error generate)

if( mysql_num_rows( mysql_query("SHOW TABLES LIKE '".$table."'")))
{
 //...
}

?>

php.net    

Comments

Snippets Manager replied on Fri, 2008/11/07 - 1:48pm

These are some great ideas here. Only one thing though.....you don't have and db closes anywhere. I don't know if this was left out intentionally or not, but opening a db connection and not closing it is a very bad programming practice. The first shown function should have included a mysql_close($db); both before the return true; and return false; statments. This insures that the db is closed before continuing

Snippets Manager replied on Sat, 2008/07/19 - 7:15pm

--Here is a mysql function to use DELIMITER | CREATE FUNCTION `does_table_exist` ( `s_database_name` nvarchar(255), `s_table_name` nvarchar(255) ) RETURNS tinyint(1) DETERMINISTIC BEGIN IF EXISTS ( SELECT 1 FROM Information_schema.tables WHERE table_name = s_table_name AND table_schema = s_database_name ) THEN return true; else return false; end if; END| DELIMITER ; --------------------------------------------------- DELIMITER | CREATE PROCEDURE `zz_test_table_detect` () DETERMINISTIC BEGIN DECLARE is_it_there BOOLEAN; DECLARE function_succeeded BOOLEAN; SET is_it_there = does_table_exist ( 'religion', 'choice' ); IF is_it_there = true then SET is_it_there = does_table_exist ( 'religion', 'choices' ); if is_it_there = false then SET function_succeeded = true; ELSE SET function_succeeded = false; END IF; ELSE SET function_succeeded = false; END IF; select function_succeeded as function_succeeded ; END| DELIMITER ; --------------------------------------------------- CALL zz_test_table_detect();