Multiple Line Queries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
MattF
Forum Contributor
Posts: 225
Joined: Sun May 19, 2002 9:58 am
Location: Sussex, UK

Multiple Line Queries

Post 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.
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post by Takuma »

I tried once but never worked out why it's not working...
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post by Takuma »

I had a look on MySQL website --- no luck :(
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Post 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?)
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

They are supose to end with a semi-colon: ;

8)
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post 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");
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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???
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post 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... :?
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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?
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post 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)
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post 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:
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Post by Coco »

errr...

INSERT INTO Table (col1, col2) VALUES ('user1', 'pass1');
is the syntax no?
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post by Takuma »

My syntax works also :o
kcomer
Forum Contributor
Posts: 108
Joined: Tue Aug 27, 2002 8:50 am

Post 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
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post by Takuma »

But that would be slower
Post Reply