Check whether the table exists or not

Question

$qry    = "SELECT * FROM nl_lang_var";
    if($qry === false)
        {
             $sql = "INSERT INTO  nl_lang_var set value='$value'        WHERE        name='$var' and langid=$langid"; }
            else {
 echo "already existed"; }

I want to know if the table nl_lang_var exists or not.

If it doesn't exist it must insert, otherwise give message.

Check this out: http://stackoverflow.com/questions/8829102/mysql-check-if-table-exists-without-using-select-from — D4V1D, Mar 27 '15 at 06:56
SELECT * FROM INFORMATION_SCHEMA.TABLES this will list all the tables in your database — Ceeee, Mar 27 '15 at 06:58

score 1 · Answer 1 · edited Mar 27 '15 at 08:37

1

There are 2 possible solutions.

Solution 1

SELECT 1 FROM nl_lang_var WHERE 1 LIMIT 1;

If the above query returns no error then table exists.

Solution 2

SELECT *
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_NAME = 'nl_lang_var';

edited Mar 27 '15 at 08:37

Keep Coding

636
9
26

answered Mar 27 '15 at 07:14

MaK

110
4

score 0 · Accepted Answer · answered Mar 27 '15 at 07:00

0

i havent tried it if this will work, but i hope you can get some idea

SELECT count(table_name) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE table_name = 'nl_lang_var'

answered Mar 27 '15 at 07:00

Ceeee

1,392
2
15
32

Navneet Garg · Answer 3 · 2015-03-27T07:31:04.547

0

Try this query

SHOW TABLES LIKE 'nl_lang_var';

Or

SHOW TABLE STATUS LIKE 'nl_lang_var';

SHOW TABLE is fater than information_schema

But both are depended on the privileges to the connected user

Ref

edited Mar 27 '15 at 07:31

answered Mar 27 '15 at 07:22

Navneet Garg

1,364
12
29

Check whether the table exists or not

3 Answers3