As simple in theory as it sounds I've done a fair amount of research and am having trouble figuring this out.
How can I check if a MySQL table exists and if it does do something. (I guess a simple php if/else statement could work for this)
Is there a way to do this?
This is what I have done with cwallenpoole's response:
mysql_connect("SERVER","USERNAME","PASSWORD");
mysql_select_db('DATABASE');
$val = mysql_query('select 1 from `TABLE`');
if($val !== FALSE)
{
print("Exists");
}else{
print("Doesn't exist");
}
This question is related to
php
mysql
if-statement
$res = mysql_query("SELECT table_name FROM information_schema.tables WHERE table_schema = '$databasename' AND table_name = '$tablename';");
If no records are returned then it doesn't exist.
The cleanest way to achieve this in PHP is to simply use DESCRIBE statement.
if ( mysql_query( "DESCRIBE `my_table`" ) ) {
// my_table exists
}
I'm not sure why others are posting complicated queries for a such a straight forward problem.
Using PDO
// assuming you have already setup $pdo
$sh = $pdo->prepare( "DESCRIBE `my_table`");
if ( $sh->execute() ) {
// my_table exists
} else {
// my_table does not exist
}
mysql_unbuffered_query("SET profiling = 1"); // for profiling only
@mysql_unbuffered_query("SELECT 1 FROM `table` LIMIT 1 ");
if (mysql_errno() == 1146){
// NO EXISTING TABLE CODE GOES HERE
}
elseif(mysql_errno() > 0){
echo mysql_error();
}
$results = mysql_query("SHOW PROFILE"); // for profiling only
Execution time, measured using mysql profiling, when a table exists, or not, is about .000125 sec. (125µs)
The LIMIT 1 is important for speed. This minimizes the Sorting Result and Sending Data query State times. And table size is not a factor.
I always use an unbuffered query when no results are required.
PROFILE RESULTS WHEN TABLE DOES NOT EXIST
QUERY STATE SECONDS
-------------------- -------
starting 0.000025
checking permissions 0.000006
Opening tables 0.000065
query end 0.000005
closing tables 0.000003
freeing items 0.000013
logging slow query 0.000003
cleaning up 0.000003
TOTAL 0.000123 <<<<<<<<<<<< 123 microseconds
WHEN TABLE EXISTS
QUERY STATE SECONDS
-------------------- -------
starting 0.000024
checking permissions 0.000005
Opening tables 0.000013
System lock 0.000007
init 0.000006
optimizing 0.000003
statistics 0.000009
preparing 0.000008
executing 0.000003
Sending data 0.000019
end 0.000004
query end 0.000004
closing tables 0.000006
freeing items 0.00001
logging slow query 0.000003
cleaning up 0.000003
TOTAL 0.000127 <<<<<<<<<<<< 127 microseconds
Or you could use
show tables where Tables_in_{insert_db_name}='tablename';
It was already posted but here it is with PDO (same query)...
$connection = new PDO ( "mysql:host=host_db; dbname=name_db", user_db, pass_db );
if ($connection->query ("DESCRIBE table_name" )) {
echo "exist";
} else {
echo "doesn't exist";
}
Works like a charm for me....
And here's another approach (i think it is slower)...
if ($connection->query ( "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name' AND table_name ='tbl_name'" )->fetch ()) {
echo "exist";
} else {
echo "doesn't exist";
}
You can also play with this query:
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name'
I think this was suggested to use in the mysql page.
DO NOT USE MYSQL ANY MORE. If you must use mysqli but PDO is best:
$pdo = new PDO($dsn, $username, $pdo); // proper PDO init string here
if ($pdo->query("SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name'")->fetch()) // table exists.
You can use many different queries to check if a table exists. Below is a comparison between several:
mysql_query('select 1 from `table_name` group by 1'); or
mysql_query('select count(*) from `table_name`');
mysql_query("DESCRIBE `table_name`");
70000 rows: 24ms
1000000 rows: 24ms
5000000 rows: 24ms
mysql_query('select 1 from `table_name`');
70000 rows: 19ms
1000000 rows: 23ms
5000000 rows: 29ms
mysql_query('select 1 from `table_name` group by 1'); or
mysql_query('select count(*) from `table_name`');
70000 rows: 18ms
1000000 rows: 18ms
5000000 rows: 18ms
These benchmarks are only averages:
SHOW TABLES LIKE 'TableName'
If you have ANY results, the table exists.
To use this approach in PDO:
$pdo = new \PDO(/*...*/);
$result = $pdo->query("SHOW TABLES LIKE 'tableName'");
$tableExists = $result !== false && $result->rowCount() > 0;
To use this approach with DEPRECATED mysql_query
$result = mysql_query("SHOW TABLES LIKE 'tableName'");
$tableExists = mysql_num_rows($result) > 0;
mysql_query("SHOW TABLES FROM yourDB");
//> loop thru results and see if it exists
//> in this way with only one query one can check easly more table
or mysql_query("SHOW TABLES LIKE 'tblname'");
Don't use mysql_list_tables(); because it's deprecated
Even faster than a bad query:
SELECT count((1)) as `ct` FROM INFORMATION_SCHEMA.TABLES where table_schema ='yourdatabasename' and table_name='yourtablename';
This way you can just retrieve one field and one value. .016 seconds for my slower system.
<?php
$connection = mysqli_connect("localhost","root","","php_sample_login_register");
if ($connection){
echo "DB is Connected <br>";
$sql = "CREATE TABLE user(
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255)NOT NULL,
email VARCHAR(255)NOT NULL,
password VARCHAR(255) NOT NULL
);";
if(mysqli_query($connection,$sql)) {
echo "Created user table";
} else{
echo "User table already exists";
}
} else {
echo "error : DB isnot connected";
} ?>
Source: Stackoverflow.com