Multiple SQL queries in one statement

Question

I want to do multiple query in one statement here is my code and it's not working.

$stmt = $mysqli->prepare("UPDATE  menu SET DESCRIPTION= ('$txt1'), VISIBLE=('$ckHOME1')
where id='1';
UPDATE  menu SET DESCRIPTION= ('$txt1'), VISIBLE=('$ckHOME1') where id='1';
UPDATE  menu SET DESCRIPTION= ('$txt2'), VISIBLE=('$ckHOME2') where id='2';
UPDATE  menu SET DESCRIPTION= ('$txt3'), VISIBLE=('$ckHOME3') where id='3';
UPDATE  menu SET DESCRIPTION= ('$txt4'), VISIBLE=('$ckHOME4') where id='4';");
$stmt->execute();
if (!mysqli_query($con,$sql))
{
die('Error: ' . mysqli_error($con));
}
mysqli_close($con);
}

And if you're using mysqli_prepare, why are you interpolating variables instead of using placeholders? — Barmar
– Barmar, Commented Jan 6, 2014 at 6:45
I'm wondering wtf the checks if(!mysqli_query...) are doing :D — Ivan Yonkov
– Ivan Yonkov, Commented Jan 6, 2014 at 6:46

John Woo · Accepted Answer · 2014-01-06 06:41:31Z

Alternatively, you can also make it as one UPDATE statement using CASE.

UPDATE  menu 
SET     
        DESCRIPTION = CASE WHEN id = 1 THEN '$txt1'
                            WHEN id = 2 THEN '$txt2'
                            WHEN id = 3 THEN '$txt3'
                            WHEN id = 4 THEN '$txt4'
                    END,
        VISIBLE = CASE WHEN id = 1 THEN '$ckHOME1'
                            WHEN id = 2 THEN '$ckHOME2'
                            WHEN id = 3 THEN '$ckHOME3'
                            WHEN id = 4 THEN '$ckHOME4'
                    END
where   id IN (1, 2, 3, 4)

Barmar · Accepted Answer · 2014-01-06 06:39:49Z

3

You have to use mysqli_multi_query() to execute multiple queries in one call.

But there's no need to do multiple queries for what you're doing:

UPDATE menu 
SET description = 
        CASE id
            WHEN 1 THEN '$txt1'
            WHEN 2 THEN '$txt2'
            WHEN 3 THEN '$txt3' 
            WHEN 4 THEN '$txt4'
        END,
    visible = 
        CASE id
            WHEN 1 THEN '$ckHOME1'
            WHEN 2 THEN '$ckHOME2'
            WHEN 3 THEN '$ckHOME3' 
            WHEN 4 THEN '$ckHOME4'
        END
WHERE id IN (1, 2, 3, 4);

answered Jan 6, 2014 at 6:39

Barmar

789k57 gold badges554 silver badges668 bronze badges

2 Comments

Barmar Over a year ago

@user2990463 You call it the same way you call mysqli_query.

user2990463 Over a year ago

Thank you very much Gentlemen, it ROCKS!

Collectives™ on Stack Overflow

Multiple SQL queries in one statement

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related