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 Manager

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

Test If MySQL Table Exists With PHP.

01.25.2007

| 56333 views |

        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

Tags:

MySQL
php

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();