I have an error in my syntax using if else then

Question

I am trying to update if exists else insert, but am recieving an error this is my query:

            IF EXISTS
            (
                SELECT * FROM pages_staff 
                WHERE pages_admin_page = :page
                AND pages_admin_type = :type
                AND pages_admin_user = :user
            ) 
            THEN 
            BEGIN 
                UPDATE pages_staff
                SET pages_admin_page = :page,
                    pages_admin_type = :type,
                    pages_admin_user = :user,
                    pages_admin_permissions = :permission
                WHERE pages_admin_page = :page
                AND pages_admin_type = :type
                AND pages_admin_user = :user
            END
            ELSE 
            BEGIN
                INSERT INTO pages_staff
                (
                    pages_admin_page,
                    pages_admin_type,
                    pages_admin_user,
                    pages_admin_permissions
                )
                VALUES
                (
                    :page, 
                    :type, 
                    :user, 
                    :permission
                );
            END

and here's my PHP:

    //iniate sql
    $add_staff = $database->prepare($sql);  

    //bind params
    $user = Session::get('id');
    $add_staff->bindParam(":page", $page, PDO::PARAM_INT);
    $add_staff->bindParam(":type", $type, PDO::PARAM_STR);
    $add_staff->bindParam(":user", $user, PDO::PARAM_INT);

    //what permission are they being given
    switch($permission):
        case 'admin':
            $permission = 'admin';
        break;
        case 'editor':
            $permission = 'editor';
        break;
        case 'moderator':
            $permission = 'moderator';      
        break;
        case 'advertiser':
            $permission = 'advertiser';
        break;
        case 'analyst':
            $permission = 'analyst';
        break;
        default: exit;
    endswitch;

    //bind last param and execute
    $add_staff->bindParam(":permission", $permission, PDO::PARAM_STR);
    $add_staff->execute();

But I am getting the error

Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 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 pages_staff WHERE pages_admin_page =' at line 1 in C:\xampp\htdocs\application\model\EmployeesModel.php on line 468
{"success":false,"error":"There was an error adding permissions to this user"}

How can I fix this?

See this : http://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists - IF can only be used in a function/procedure, so maybe modify the PHP. — PaulF, Nov 24 '15 at 16:26
The BEGIN END must wrap all the stored procedure not different parts of your code. — leonardo_palma, Nov 24 '15 at 16:31
If you have a unique index then this may be a solution : http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists — PaulF, Nov 24 '15 at 16:32

score 0 · Accepted Answer · answered Nov 24 '15 at 16:40

Use insert on duplicate key update:

INSERT INTO pages_staff(pages_admin_page, pages_admin_type, pages_admin_user, pages_admin_permissions)
    VALUES (:page, :type, :user, :permission)
ON DUPLICATE KEY UPDATE pages_admin_permissions = :permission;

Then use a unique index/constraint to prevent duplication:

CREATE UNIQUE INDEX unq_pages_staff_3 ON pages_staff(pages_admin_page, pages_admin_type, pages_admin_user);

I have an error in my syntax using if else then

1 Answers1