Portal Home > Knowledgebase > Articles Database > regular expression help... again!
regular expression help... again!
| Posted by larwilliams, 06-02-2009, 04:50 PM |
| Hi guys,
Thanks to everyone who replied to my previous thread. I got a new situation with a new issue. I am trying to parse a .SQL file and import it using PHP. I figured out how to split the individual commands into an array, however I am having some trouble stripping out the SQL comments themselves (with a partially working solution).
The database is below
The PHP I am using to clean up the newlines and strip the comments is as follows:
It works fine in my tests, but I fear the preg_replace could cause problems, as it matches a # that is followed by a newline at some point.
As SQL comments usually have a newline preceding them (all the ones I've seen anyways), I tried the following instead...
but it didn't work.
Am I missing something?
|
| Posted by phsource, 06-02-2009, 06:26 PM |
| To replace multiline statements using Perl regular expressions, you have to add the "m" flag. So, use the following:
preg_replace("/\n#.*/mi", '', $str);
|
| Posted by foobic, 06-02-2009, 07:21 PM |
| My first thought is: Don't - use the mysql shell command instead. But I know that's not always an option. If you really must use PHP, why not use the library function from phpMyAdmin? Parsing special file formats using regexps can start out simple but end up being a lot of work.
|
| Posted by larwilliams, 06-02-2009, 07:23 PM |
| library function? I just googled it and got nothing useful. Care to point me in the right direction?
|
| Posted by foobic, 06-02-2009, 07:26 PM |
| It's an open-source PHP program. Download it and take a look.
|
| Posted by larwilliams, 06-02-2009, 07:33 PM |
| Oh, you are talking about phpMyAdmin itself. Unfortunately, that is not an option, as this is to be an automated program to import an uploaded database for a client of ours (non-techie type of person).
|
| Posted by foobic, 06-02-2009, 07:46 PM |
| No, I'm talking about copying one of the library functions from phpMyAdmin (the one that parses and imports an SQL file) and calling it from your own code, which can present whatever interface you like to your non-techie client.
It may be hard going at first but while you're at it you'll learn something about how phpMyAdmin works and how others write PHP programs.
|
| Posted by larwilliams, 06-02-2009, 07:58 PM |
| I just downloaded the phpMyAdmin source and looked over the import function itself. It looks very good, but overkill for my purpose. Thanks for making me aware of it though
As it stands, the solution provided in this thread works for my purpose and seems to be okay.
|
| Posted by ghostweb, 06-02-2009, 09:37 PM |
| If you are worried about the # matching in the middle of the line use the ^ to anchor it to the start of the line. So it will only match comments on lines that start with the #. If that is true then the following would be much safer:
$sql_statements = preg_replace("/^#.*?\n/i","",$sql_statements);
If you wanted you could make it a bit more generic using the \s special character to match any whitespace prior to the #.
|
| Posted by mwatkins, 06-03-2009, 12:47 AM |
| Or you could do it without regex:
Produces (pretty printed) a list of statements:
Last edited by mwatkins; 06-03-2009 at 01:02 AM.
|
| Posted by Steve_Arm, 06-03-2009, 01:10 AM |
| Just curious, why would you want to parse the sql file?
I see that you will also create tables, which means that the user has
advanced privileges to the database, making the application less secure.
I would just create the database/tables beforehand and use a portable format
for the data to be inserted.
|
Add to Favourites 
Print this Article
Also Read
Shared Hosting
Shared Hosting
