IF condition MySQL not working

Question

I am trying to execute a query into MySQL but it keeps telling me i am using the wrong syntax, I tried searching the MySQL community but I am not getting anything usefull.. most of the answers i find on google are for other databases yet they label them for "MySQL", yet it keeps failing.

This is the statement i am trying to execute:

        $statement = "IF (SELECT ttb_id FROM timetable WHERE ttb_week = $i AND ttb_time = $j) THEN
                        BEGIN
                          UPDATE types SET typ_name = '$subj'
                          WHERE typ_name = 'student';
                        END;
                      ELSE
                        BEGIN
                          INSERT INTO types VALUES (null,`Yo`);
                        END;
                    ";

error:

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 (SELECT ttb_id FROM timetable WHERE ttb_week = 0 AND ttb_time = 0) THEN ' at line 1

I am using:

PHP Script Language Version 5.2.6
MySQL Database Version 5.0.51b

I have been looking around but to no avail, and the if condition stated on the MySQL dev website are not helping at all..

I am new to this and it is driving me mad! all the different queries i tried failed.. it is never the right syntax.

I found many answers for the problem on stackExchange and other websites but it is always wrong.. also I remember this structure from a VB.net lesson a while ago so maybe this is for MSSQL? then what about MySQL? everyone so far listed similar structure and said it works for MySQL, I took my answer from an answer on this community* labeled for MySQL and he claimed it worked. this is one of many i tried.

I would really appreciate your help

*: Usage of MySQL's "IF EXISTS"

I don't understand what your IF condition is attempting to evaluate. — Langosta, Apr 05 '16 at 17:48
encapsulate that in a `function($i, $j);` in mysql and call it from php. — Juan Carlos Oropeza, Apr 05 '16 at 17:50
I never ever use IF statements directly in SQL. Your example here is a good reason why. You should do one statement to pull back that date (or whatever). Then, run a PHP IF statement to decide which update/insert to run. — durbnpoisn, Apr 05 '16 at 17:51
i am using a 3 column constraints, where a record having the same value of 3 columns cannot occur, so inserting everytime is not good. if it exists i update it, if not i insert it. the queries after it checks if it exists are just tests to check it it worked only, the main thing i am trying to get right is the syntax, then i will change it to my need. it does this for 49 items for each update, so relying on many executes is bound to be a performance issue — bakz, Apr 05 '16 at 17:55
According to your last comment it looks like a typical case for [`INSERT ON DUPLICATE KEY UPDATE`](http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html). You may not need all of this. — ForguesR, Apr 05 '16 at 18:35

Dresden · Answer 1 · 2016-04-05T18:49:00.397

My first thought would be you're not comparing your SELECT return to anything to actually utilize your conditional logic. Are you just looking to see if your query returns values? If it doesn't return a value then you insert a new record in otherwise you update.

Maybe use IS NOT NULL or a check to see count on the select to see how many rows and compare to see if that's greater than 0.

IF (SELECT ttb_id FROM timetable WHERE ttb_week = $i AND ttb_time = $j IS NOT NULL) THEN

IF (SELECT COUNT(*) FROM timetable WHERE ttb_week = $i AND ttb_time = $j) > 0 THEN

IF condition MySQL not working

1 Answers1