Multiple SQL commands in PHP and MySQL

Question

In MySQL I can perform multiple commands at once, for example:

Select * from Users; Select * from Classes;

How can I do the same when invoking the command from PHP, for example:

$sql=mysqli_query($link, "select * from Users;");
while($rows=mysqli_fetch_array($sql)){
    echo "<div>$rows[0]</div><div>$rows[1]</div>";
}

However, it doesn't work:

$sql=mysqli_query($link, "select * from Users; Select * from Classes");
while($rows=mysqli_fetch_array($sql)){
    echo "<div>$rows[0]</div><div>$rows[1]</div>";
}

I understand that in practice this may not be necessary. Consider I need this particular issue to simulate or to develop my own version of an interface using PHP to manage MySQL database.

Use concatenated statements. See here: http://stackoverflow.com/questions/10610675/php-mysql-with-multiple-queries — Laurence Moroney, Apr 19 '13 at 04:57
What do you expect as result from executing two select statements like that? How will you retrieve the found data? Please rethink your question hard...there probably is something wrong with your approach in general. — Till Helge, Apr 19 '13 at 07:46

sandy · Accepted Answer · 2013-04-19T05:33:25.713

3

you can achieve this through "multi_query" option.

$query = "select * from Users;";
$query .= "Select * from Classes";



if (mysqli_multi_query($link, $query)) {
    do {
        /* store first result set */
        if ($result = mysqli_store_result($link)) {
            while ($row = mysqli_fetch_row($result)) {
                printf("%s\n", $row[0]);
            }
            mysqli_free_result($result);
        }
        /* print divider */
        if (mysqli_more_results($link)) {
            printf("-----------------\n");
        }
    } while (mysqli_next_result($link));
}
mysqli_close($link);

edited Apr 19 '13 at 05:33

answered Apr 19 '13 at 05:00

sandy

1,096
1
7
17

1

There really is no point executing 2 SELECT statements together with mysqli_multi_query, since you will never be able to fetch the results of the first SELECT statement. – Jocelyn Apr 19 '13 at 07:42
@Jocelyn - you can fetch the results of both the select statements i have tried and i have got the result as well.... – sandy Apr 19 '13 at 07:46
@sandy And how do you address the different queries? How do you keep the two result sets apart? – Till Helge Apr 19 '13 at 07:47
its the "mysqli_more_results " which identifies different queries. Here i have separated with "-----------" which does the job. And "mysqli_next_result" breaks the connection of multiple queries and then after this statement you can use the regular queries as before... – sandy Apr 19 '13 at 07:49
Okay. Interesting. Never seen that before. So the question remaining is probably whether there is any advantage to doing this instead of two separate queries, which will make the code a lot simpler. Especially when you need to access fields of the results and they are different for each of the queries. – Till Helge Apr 19 '13 at 07:55
I tried running both the queries with there delimiters through command line, which works as a single query resulting the desired one. So what i see when considered PHP to MySql call might be once than running 2 queries separately – sandy Apr 19 '13 at 08:40

score 0 · Answer 2 · answered Apr 19 '13 at 06:31

In MySQL [console] I can perform multiple commands once

yes, you can.

But technically they are executed separately anyway.

So, 2 consequent calls to mysqli_query() will do exactly the same.

Thus, there is no problem to make 2 calls instead of one. Especially because there is not a single point in doing all at once.

Although you can use whatever multi-query solution, it will make not much sense and just make your code overcomplicated and error-prone for no reason.

score -1 · Answer 3 · answered Apr 19 '13 at 05:44

-1

You can not run two query simultaneously,by defaul mysql always take last query after semi column i. it will run Select * from Classes.Instead of doing this better to take union of both query.

answered Apr 19 '13 at 05:44

Ajeet Singh

32
2

1

pls check about mysqli_multi_query in the php manual – karthzDIGI Apr 19 '13 at 06:12

Multiple SQL commands in PHP and MySQL

3 Answers3