howto run sql script with php?
in [General]
|
From: Stephan Ebelt on 13 Jun 2010 05:05 Hello, I am trying to run a .sql script produced with mysql-dump from a php script but failed to find a rudimentary robust solution yet. mysql_query() can only run one query at a time. Thus its necessary to somehow parse the input file and separate the queries... which is difficult since the syntax can be quite complex. So far I managed to run most CREATE TABLE and INSERT queries just by dropping newlines (\n) and explode()ing the input by ';'. Now I am stuck with a CREATE TRIGGER statement because mysql-dump does this: === DELIMITER ;; CREATE TRIGGER ... ON ... FOR EACH ROW BEGIN ... ... END; ;; DELIMITER ; === exploding that string by ';' results in 4 (or more) queries which are invalid syntax then. And I have no glue on what else mysql-dump may come up with on other occasions. does anyone know how to do that properly? I dont favor importing the dumps via shell command like 'cat some.dump | mysql' either. stephan
From: Peter Lind on 13 Jun 2010 05:29 On 13 June 2010 11:05, Stephan Ebelt <ste(a)shared-files.de> wrote: > Hello, > > I am trying to run a .sql script produced with mysql-dump from a php script but > failed to find a rudimentary robust solution yet. > > mysql_query() can only run one query at a time. Thus its necessary to somehow > parse the input file and separate the queries... which is difficult since the > syntax can be quite complex. > Use mysqli - it supports running multiple queries at once. Regards Peter -- <hype> WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind BeWelcome/Couchsurfing: Fake51 Twitter: http://twitter.com/kafe15 </hype>
From: Stephan Ebelt on 13 Jun 2010 06:52 On Sun, Jun 13, 2010 at 11:29:14AM +0200, Peter Lind wrote: > On 13 June 2010 11:05, Stephan Ebelt <ste(a)shared-files.de> wrote: > > Hello, > > > > I am trying to run a .sql script produced with mysql-dump from a php script but > > failed to find a rudimentary robust solution yet. > > > > mysql_query() can only run one query at a time. Thus its necessary to somehow > > parse the input file and separate the queries... which is difficult since the > > syntax can be quite complex. > > > > Use mysqli - it supports running multiple queries at once. sometimes its as easy as that. I'll give it a try. thanks, stephan
From: Ashley Sheridan on 13 Jun 2010 07:14 On Sun, 2010-06-13 at 12:52 +0200, Stephan Ebelt wrote: > On Sun, Jun 13, 2010 at 11:29:14AM +0200, Peter Lind wrote: > > On 13 June 2010 11:05, Stephan Ebelt <ste(a)shared-files.de> wrote: > > > Hello, > > > > > > I am trying to run a .sql script produced with mysql-dump from a php script but > > > failed to find a rudimentary robust solution yet. > > > > > > mysql_query() can only run one query at a time. Thus its necessary to somehow > > > parse the input file and separate the queries... which is difficult since the > > > syntax can be quite complex. > > > > > > > Use mysqli - it supports running multiple queries at once. > > sometimes its as easy as that. I'll give it a try. > > thanks, > stephan > > Why do you have to use PHP at all to do this? You could run the query directly at the MySQL console. Failing that, why not use a tool such as phpMyAdmin, which will do all the hard work for you? Thanks, Ash http://www.ashleysheridan.co.uk
From: Stephan Ebelt on 13 Jun 2010 11:03
On Sun, Jun 13, 2010 at 12:14:53PM +0100, Ashley Sheridan wrote: > On Sun, 2010-06-13 at 12:52 +0200, Stephan Ebelt wrote: > > On Sun, Jun 13, 2010 at 11:29:14AM +0200, Peter Lind wrote: > > On 13 June 2010 11:05, Stephan Ebelt <[1]ste(a)shared-files.de> wrote: > > > Hello, > > > > > > I am trying to run a .sql script produced with mysql-dump from a php script but > > > failed to find a rudimentary robust solution yet. > > > > > > mysql_query() can only run one query at a time. Thus its necessary to somehow > > > parse the input file and separate the queries... which is difficult since the > > > syntax can be quite complex. > > > > > > > Use mysqli - it supports running multiple queries at once. > > sometimes its as easy as that. I'll give it a try. > > thanks, > stephan > > > > Why do you have to use PHP at all to do this? You could run the query > directly at the MySQL console. > > Failing that, why not use a tool such as phpMyAdmin, which will do all the > hard work for you? I use it to deploy schema updates and run unittests. So there are many sql files running many times actually. Its been working fine for years until I came up with wanting a trigger recently... good point to check how phpMyAdmin does this. They must have faced the same problem. stephan > > Thanks, > Ash > [2]http://www.ashleysheridan.co.uk > > References > > Visible links > 1. mailto:ste(a)shared-files.de > 2. http://www.ashleysheridan.co.uk/ |