What is Wrong with this SQL Syntax that checks if a table exists?

Question

I'm querying a server and iterating through multiple databases using PHP, but for some reason this $sql2 query (which I have read works in countless threads) is returning a syntax error:

$res = mysqli_query($conn,"SHOW DATABASES");

if (!$res){
    // Deal with error
}

while ($d = mysqli_fetch_array($res)){

    $db = $d['Database'];

    $sql1 = "USE $db";
    $query1 = mysqli_query($conn, $sql1);

    if (!$query1){
        // Deal with error
    }

    $sql2 = "IF (EXISTS (SELECT *
                 FROM INFORMATION_SCHEMA.TABLE
                 WHERE TABLE_SCHEMA = '$db'
                 AND TABLE_NAME = 'appusers'))
             BEGIN
                 SELECT * FROM `appusers`
             END";

    $query2 = mysqli_query($conn, $sql2);

    if (!$query2){
        // Deal with error
    }
}

This is the error I receive:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE WHERE TABLE_S' at line 1

My MySQL Server version is 5.6.27 and my PHP interpreter is 5.6

You can't use that form of `IF` in a query, only in stored procs and functions. — Uueerdo, Jun 23 '16 at 19:07
@KIKOSoftware thank you I will change that now, but I think Uueerdo is correct. In which case, what would I write? — Jodo1992, Jun 23 '16 at 19:09
Why don't you perform the inner query first, and then do the `IF` in PHP? — KIKO Software, Jun 23 '16 at 19:10
Here are some examples of how to use `IF` in MySQL: http://www.w3resource.com/mysql/control-flow-functions/if-function.php — KIKO Software, Jun 23 '16 at 19:13
In many languages I work in, I find it helpful to create a `TableExists` function that takes a connection, a table name, and (optionally) a schema name. — Uueerdo, Jun 23 '16 at 19:22

Barmar · Accepted Answer · 2016-06-24T01:45:28.073

2

You can't use an IF statement as a query, only in a stored procedure. You'll need to perform two separate queries.

$sql = "SELECT COUNT(*) AS count
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = '$db'
        AND TABLE_NAME = 'appusers'";
$result = mysqli_query($conn, $sql) or die(mysqli_error($conn);
$row = mysqli_fetch_assoc($result);
if ($row['count'] != 0) {
    $sql2 = "SELECT * FROM appusers";
    $query2 = mysqli_query($conn, $sql2);
    ...
} else {
    // deal with error
}

edited Jun 24 '16 at 01:45

answered Jun 23 '16 at 19:35

Barmar

741,623
53
500
612

What exactly is being returned to `$result`? – Jodo1992 Jun 24 '16 at 01:15
It's the result set of the query. You use that to fetch the rows, or call `mysqli_num_rows()`, etc. – Barmar Jun 24 '16 at 01:16
This query returns a single row containing the count of rows in `INFORMATION_SCHEMA.TABLE` that match the criteria. – Barmar Jun 24 '16 at 01:17
I am now getting this error: `Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in `. Why is `$result` failing? – Jodo1992 Jun 24 '16 at 01:40
The name of the table is `TABLES`, not `TABLE`. – Barmar Jun 24 '16 at 01:46

score 0 · Answer 2 · answered Jun 23 '16 at 19:49

There is no if in SQL (although mysql has a function called if)

Apparently, you want to run a query if the table exists. The more common way would be to try running the query and check whether the error you get says that the table doesn't exist.

E.g. if you run the query in the mysql command line application, you might get this:

mysql> SELECT * FROM appusers;
ERROR 1146 (42S02): Table 'test.appusers' doesn't exist

You see two codes:

1146: internal mysql code (obtain this via mysqli_errno)
42S02: the SQL standard error state (obtain this via mysqli_sqlstate)

In your case, checking the SQL state might be better because it covers more cases. E.g., all of these mysql error codes map to SQL state 42S02:

1051 - Unknown table '%s'
1109 - Unknown table '%s' in %s
1146 - Table '%s.%s' doesn't exist

What is Wrong with this SQL Syntax that checks if a table exists?

2 Answers2