how to invoke a set of update statements

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jasongr
Forum Contributor
Posts: 206
Joined: Tue Jul 27, 2004 6:19 am

how to invoke a set of update statements

Post by jasongr »

Hi people

I have a file that contains many SQL statements that are generated in runtime.
These are queries in the following syntax:

Code: Select all

DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
  `ID` int(10) unsigned not null,
...
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

# comment here

insert into table1 (`ID`, ....) values (1, ...);
insert into table1 (`ID`, ....) values (2, ...);
insert into table1 (`ID`, ....) values (3, ...);
Basically, the queries in the file fall under 4 formats:
1) drop table statements
2) create table statements
3) comments
4) insert statements

The file can be very large.
I need some way of invoking ALL the queries in the file using function mysql_query.

Initially what I did was to split the content of the file by ';'
each splitted part was invoked separately. This worked fine, until a semicolon was found in one of the "insert into" statements and that broke my code:

Code: Select all

insert into table1 (`ID`, `Text`) values (1, 'This string contains ;');
I am looking for a robust way to parse a file that contains SQL statements.
Possibly using regular expressions can achieve a more robust parsing.
If I can invoke all the sql statements in the file without any parsing, that would be perfect

any suggestions?

regards
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Post by matthijs »

Maybe use the file function, which reads a file line after line and returns an array (for each line). Only problem would be that you should write each statement on one single line.

But maybe it's easier to place the statements in a db table?
Or a php file (in which you place an array of the statements)?
jasongr
Forum Contributor
Posts: 206
Joined: Tue Jul 27, 2004 6:19 am

Post by jasongr »

I have no control over the content of the file.
As it is produced by a third party.
All I know is that its content must be a set of valid SQL statements.
For example,
the statements may not even be separated by newlines (all the statements will be in one long line in the file)
Some of the statements may be separated by newlines and others by white space...

I need a robust way to separate the statements which is based on the fact that these are valid sql statements

regards
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Post by matthijs »

In that case it will be - probably very complicated - regex. I can't help you with that I'm afraid.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Or maybe you can adopt some code of phpmyadmin for that matter.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

read through raghavan20's threads in Regex. ;)
Post Reply