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: ;

8)

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

8)
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: ;

8)
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? 8)

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! :cry:

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 :o

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