Jodo1992 Jodo1992 - 5 months ago 6
MySQL Question

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

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

Answer

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.TABLE
        WHERE TABLE_SCHEMA = '$db'
        AND TABLE_NAME = 'appusers'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
if ($row['count'] != 0) {
    $sql2 = "SELECT * FROM appusers";
    $query2 = mysqli_query($conn, $sql2);
    ...
} else {
    // deal with error
}