Mysql can't perform more than 1 query at a time

Question

When I do:

$q = 'insert into movies values("lion king"); select * from movies;';
$result = $db->query($q);
echo mysqli_num_rows($result);

it says that $result is boolean, not mysqli result. if I check $result like this:

if(!$result) echo 'fail';

it outputs 'fail'.

Is mysql not capable of handling more than 1 query at a time? How do I solve this?

score 7 · Answer 1 · answered Dec 21 '09 at 21:37

7

You need to use mysqli::multi_query.

answered Dec 21 '09 at 21:37

Jacob Relkin

161,348
33
346
320

score 3 · Accepted Answer · answered Dec 21 '09 at 21:37

3

$db->query('insert into movies values("lion king");');
$db->query('select * from movies;');

answered Dec 21 '09 at 21:37

Derek Illchuk

5,638
1
29
29

So is mysql not able to do those together?? – Gal Dec 21 '09 at 21:37
3

You really don't want to do those together. You should check that your insert worked and then do the other query. – Arthur Thomas Dec 21 '09 at 21:39

score 0 · Answer 3 · answered Dec 21 '09 at 21:37

0

$q = 'insert into movies values("lion king")';
$result = $db->query($q);
$q = 'select * from movies';
$result = $db->query($q);
echo mysqli_num_rows($result);

answered Dec 21 '09 at 21:37

David Oneill

12,502
16
58
70

+1 I would recommend *not* using multi-query as some have suggested, because multi-query opens up new types of consequences if one has SQL injection vulnerabilities. – Bill Karwin Dec 21 '09 at 21:42
2

@Bill, having a potential SQL injection leak is just a very lame excuse not to use functions. – Jan Jongboom Dec 21 '09 at 21:45
+1 for Jan. If you've got SQL injection vulnerabilities... fix them. They're a huge problem whether you use mysqli::multi_query or not. You might as well say, of a car with 3 flat tires, "I drive it on the highway, but never above 40 miles an hour. Because driving 65 with flat tires is dangerous." – Frank Farmer Dec 21 '09 at 22:00
Do people not actually hit the up vote when they comment saying '+1'? I thought that saying +1 was explaining why you up-voted... – David Oneill Dec 22 '09 at 13:14

score 0 · Answer 4 · answered Dec 21 '09 at 21:37

0

You can use multi_query which is difficult to use, but in general no, you should only use one query at a time.

answered Dec 21 '09 at 21:37

MindStalker

14,629
3
26
19

That's bogus. There are plenty of valid reasons to return multiple recordsets. – Jan Jongboom Dec 21 '09 at 21:42

score 0 · Answer 5 · answered Dec 21 '09 at 22:42

Don't confuse how you write 'queries' in the MySQL command line interface with how you do it with the API.

The MySQL command just wraps the API with something more shell like.

Using the API, you can really only do one query at a time. Of course the client side of the API could do something smart and interpret the semi-colons, splitting into multiple queries for you, but that probably just isn't that useful for enough people.

Mysql can't perform more than 1 query at a time

5 Answers5

Linked

Related