Syntax error with SQL in PHP

Question

I'm trying to set the column of a table to a string in every row if the column exists. However, I am receiving SQL syntax errors and can't pinpoint what exactly the issue is:

while($db = mysqli_fetch_array(mysqli_query($conn, "SHOW DATABASES"))){
    $id = $db['id'];

    $sql = /** @lang text */
           "SELECT IF( EXISTS(
                   SELECT DISTINCT `info`
                       FROM INFORMATION_SCHEMA.COLUMNS
                       WHERE COLUMN_NAME IN ('borrower')
                           AND TABLE_SCHEMA = '$db'))
                   BEGIN
                       UPDATE `info`
                           SET `borrower` = '****'
                           WHERE `id` = '$id'
                   END";

    $query = mysqli_query($conn, $sql);

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

The syntax error I receive back:

information_schema
db1
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 ')
BEGIN
UPDATE info
SET borrower = '****'
' at line 5

I know it must be something very small but I have been stuck for a while and can't seem to find my error.

`begin/end` is only valid in stored procedures, not in a general `select` query. what you're trying to do is not possible in a `select`. select only pulls data OUT of a table. it cannot CHANGE the contents of a table, or any other table. you'd need `update ... join`, and even then that can target only specific fields, not arbritary ones pulled from a select. — Marc B, Jun 21 '16 at 16:51
What are you doing there? Querying the database schema and taking that into a table - that looks horribly wrong — juergen d, Jun 21 '16 at 16:56
You have MySQL and SQL-Server tagged but your error and code suggests it's MySQL. If it were SQL-Server you could get rid of the very first `SELECT` statement and I believe it would work. — BJones, Jun 21 '16 at 17:00
@MarcB The BEGIN/END is valid because it's in relation to the OP's IF statement, is it not? I think the problem is that `SELECT IF(etc...` is not valid. Remove that first `SELECT` and you're just left with an IF statement that runs an `UPDATE` if the condition is met. — , Jun 21 '16 at 17:05
@Kalmino I have tried that but apparently it is illegal in MySQL to have an `IF` statement without `SELECT` unless it is within a stored procedure — Jodo1992, Jun 21 '16 at 17:08
Huh, weird; learn something new every day. Sorry, I can't help further; my forté is SQL Server, not MySQL and apparently they're juuuusst different enough to mess with my head. — , Jun 21 '16 at 17:12
Yeah that is wierd and I am SQLFillding it now to figure it out but like @Kalmino I am mainly a SQL server guy. My other thought for you would be to separate your logic. Return something from the SCHEMA query to a php variable test php variable in php and then run a 2nd sql statement from php to update if true. I don't like the 2 round trips but it should be light anyway..... — Matt, Jun 21 '16 at 17:24
@Kalmino: `if()` doesn't use begin/end. it's `if ... then ... elseif ... endif` — Marc B, Jun 21 '16 at 17:24

score 2 · Accepted Answer · edited May 23 '17 at 12:25

Try this syntax for the query-

 IF EXISTS (SELECT DISTINCT `info`
                   FROM INFORMATION_SCHEMA.COLUMNS
                   WHERE COLUMN_NAME IN ('borrower')
                       AND TABLE_SCHEMA = '$db')) THEN
     UPDATE `info`
                       SET `borrower` = '****'
                       WHERE `id` = '$id'


  END IF;

I added Then in the end of the if condition and End if instead of only if, and I removed the select to. What are you trying to select? it is only update clause.

update

Example from here

you can't use it like a normal query. Control structures like IF or WHILE are only allowed in stored procedures or functions.

MYSQL panel

delimiter $$
create procedure YourIfConditionSP()
begin
  IF EXISTS (SELECT DISTINCT `info`
                   FROM INFORMATION_SCHEMA.COLUMNS
                   WHERE COLUMN_NAME IN ('borrower')
                       AND TABLE_SCHEMA = '$db') THEN
    UPDATE `info`
                       SET `borrower` = '****'
                       WHERE `id` = '$id';


  END IF;
end $$
delimiter ;

And you have to change your php code for calling the stored procedure.

PHP

//run the stored procedure
$result = mysqli_query($connection, 
         "CALL YourIfConditionSP") or die("Query fail: " . mysqli_error());

How to call a MySQL stored procedure from within PHP code?

With this I get a different syntax error: `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 DISTINCT info FROM INFORMATION_SCHEMA.COLUMNS' at line 1` — Jodo1992, Jun 21 '16 at 17:20
You can't use this if condition in a regular query, you need to use it as store procedure. — The scion, Jun 21 '16 at 17:26

Syntax error with SQL in PHP

1 Answers1