Multiple SQL commands with one mysql_query?

Hi,
Is it possible to issue a bunch of SQL commands with one mysql_query?

TIA

You can try setting up a transaction:
http://www.devarticles.com/c/a/MySQL...QL-4.0-and-PHP

But its not with just one mysql_query, you could loop doing as much
queries as you like, and in the end of the loop commit them.

something like:
mysql_query("BEGIN");
for($i=0;$i<10;$i++) {
mysql_query("INSERT INTO TABLE (id, value) VALUES ($i, '$i')");
}
mysql_query("COMMIT");

no@email.com said the following on 27/02/2006 22:02:
> Hi,
> Is it possible to issue a bunch of SQL commands with one mysql_query?
>

No (unless you execute a stored procedure).

The solution is to use the mysqli functions instead. See the manual.


--
Oli

no@email.com wrote:

> Hi,
> Is it possible to issue a bunch of SQL commands with one mysql_query?
>
> TIA

Yes.

You could for example put as many sql commands as you like into a text file
and pass the file as a commandline argument to mysql. Or, you can use the
'source' command in mysql to read in the file and execute the queries found
there.

Ex
If you include the sql command

USE databasename;

In your query then you can use something like this,

mysql -u username -p < text_file

Otherwise you give the name of the database on the commandline.

You can also combine the two by giving the mysql internal command on the
commandline,

mysql -e 'source text_file;'

In either case the commands are in serial fashion, i.e. each is fully
complete before the next begins.

What about the output from a command? Well you can catch that too, you might
want it for a script or to display, it is in the standard sql output format
(everything is a table) except that the graphic lines are not included in
the output.

mysql < text_file > mysql.out

Might give,

names
Fred
Bob

Instead of the normal,
-------
|names|
-------
|Fred |
|Bob |

So for example a single file could create a table if it doesn't already
exist, then fill it with some data and run a query on it. Once you are used
to it it can be a quick way to pick out data from a comma seperated file,
one of those things you want just once in a while rather than a permanent
table.

It is important to remember though that processing of a file will normally
stop on the first error. It can be forced, but presumably the commands in a
single file would be in some way related, so forcing mysql to continue is
much more a debugging aid than a general purpose command.

Some examples of sql scripting are given in the MySQL manual (well worth
downloading the pdf version so that you have it handy). But in the mean
time here is an example,

#sql_example_script.sql
#use,
#mysql -u username -p < sql_example_script.sql

CREATE DATABASE IF NOT EXISTS addressbook;

CREATE TABLE addressbook.names (
id int,
name varchar( 15)
);
CREATE TABLE addressbook.birthdays (
id int,
birthday date
);

INSERT into addressbook.names ( fields ) VALUE ( data);

BearItAll said the following on 28/02/2006 09:15:
> no@email.com wrote:
>
>> Hi,
>> Is it possible to issue a bunch of SQL commands with one mysql_query?
>>

>
> Yes.
>
<...SNIP STUFF ABOUT USE OF COMMAND LINE...>


Umm, no... none of this is using mysql_query().


--
Oli

Oli Filth wrote:

> BearItAll said the following on 28/02/2006 09:15:
>> no@email.com wrote:
>>
>>> Hi,
>>> Is it possible to issue a bunch of SQL commands with one mysql_query?
>>>
>
>>
>> Yes.
>>
> <...SNIP STUFF ABOUT USE OF COMMAND LINE...>
>
>
> Umm, no... none of this is using mysql_query().
>
>

oops, he only wrote one line too, I must have read as far as 'Is it possible
to issue a bunch of SQL commands'.

They is only one solution to this, posters must write questions of ten words
or less, would someone add that to the news group faq please.

Maybe:
<?php
$sql="SELECT * FROM a;
Select * FROM b;
select * from c;";
mysql_query($sql,$connection);
?>
??

Drakazz wrote:
> Maybe:
> <?php
> $sql="SELECT * FROM a;
> Select * FROM b;
> select * from c;";
> mysql_query($sql,$connection);
> ?>
> ??
>
mysqli has the mysqli_multi_query() for times like this.

-david-