Page 1 of 1
Multiple Line Queries
Posted: Sun Sep 08, 2002 2:01 pm
by MattF
If I have a textarea similar to phpMyAdmin's where people can paste a mySQL dump, or write a query that goes over serveral lines for example two inserts entered one after the other like this:
INSERT INTO table (id) VALUES ('1')
INSERT INTO table (id) VALUES ('2')
INSERT INTO table (id) VALUES ('3')
How do I get mysql to accept it as a query? I tried seperating with semi-colons and it throws an error at me, is there a valid way of doing this? I also looked at the phpMyAdmin source but I couldn't find what I was looking for.
Posted: Sun Sep 08, 2002 2:27 pm
by Takuma
I tried once but never worked out why it's not working...
Posted: Sun Sep 08, 2002 2:39 pm
by Takuma
I had a look on MySQL website --- no luck

Posted: Sun Sep 08, 2002 5:40 pm
by Coco
errr...
i thought only strings were supposed to go in ''
surely its gonna flip an error cos of the wrong data type...
try taking out the _'_ and adding in ;'s
(course thats assuming your storing an int... but is it any different anyhow?)
Posted: Sun Sep 08, 2002 7:33 pm
by m3mn0n
They are supose to end with a semi-colon:
;

Posted: Mon Sep 09, 2002 1:08 am
by Takuma
Oromian wrote:They are supose to end with a semi-colon:
;

Yes but the thing is that this doesn't work...
Code: Select all
INSERT INTO table VALUES("hello","1");
INSERT INTO tabl2 VLUES("hi","1");
Posted: Mon Sep 09, 2002 8:06 am
by mikeq
Maybe you could use explode() function using ; as the separator, this would put each MySQL command into an array. You could then run through the array executing each one in turn.
Possible???
Posted: Mon Sep 09, 2002 9:15 am
by m3mn0n
hmm,
here is a copy of a working
.SQL file i have...
Code: Select all
INSERT INTO lottery
( num, ticket, cash ) VALUES
( 0 , 0 , 100000000), #current jackpot
( 0 , 1 , 100000000), #last jackpot
( 0 , 2 , 0 ),
( 0 , 3 , 0 ),
( 0 , 4 , 0 );
INSERT INTO lottery2
( num, ticket, cash ) VALUES
( 0 , 0 , 100000000), #current jackpot
( 0 , 1 , 100000000), #last jackpot
( 0 , 2 , 0 ),
( 0 , 3 , 0 ),
( 0 , 4 , 0 );
maybe there is a typo somewhere in your code...

Posted: Mon Sep 09, 2002 9:53 am
by mikeq
That is using the shortened Insert syntax, have you tried running that as one query from a PHP mysql_query function, does it work?
Posted: Mon Sep 09, 2002 10:38 am
by m3mn0n
Takuma wrote:Oromian wrote:They are supose to end with a semi-colon:
;

Yes but the thing is that this doesn't work...
Code: Select all
INSERT INTO table VALUES("hello","1");
INSERT INTO tabl2 VLUES("hi","1");
do thoses two typos affect the execution?

Posted: Mon Sep 09, 2002 11:35 am
by Takuma
eh????
If I do this
Code: Select all
<?php
mysql_query("INSERT INTO members ('user1','password2'); INSERT INTO members ('user2','password3'); ");
?>
This creates an error!

Posted: Mon Sep 09, 2002 11:46 am
by Coco
errr...
INSERT INTO Table (col1, col2) VALUES ('user1', 'pass1');
is the syntax no?
Posted: Mon Sep 09, 2002 11:48 am
by Takuma
My syntax works also

Posted: Mon Sep 09, 2002 1:06 pm
by kcomer
Could you explode the textarea by the "\n" character and then loop through the array doing a mysql_query for each element of the array? I think that would work. Of course if someone puts random \n's everywhere it will crash.
Keith
Posted: Tue Sep 10, 2002 1:15 am
by Takuma
But that would be slower