MySQL input from a file

This is driving me nuts!!

I want to execute MySQL commands from a text file.

I cannot find a MODx "approved" or a PHP 4 method for doing this so I have created the following snippet:

$command="mysql databasename <input.sql";
return system ($command);

This does not work, as when I view $command all I see is "mysql databasename ", i.e. it is truncating at the "<" character.

So this is really 2 questions in one -

1. Is there a better way to do this?
2. Why is the command string truncated?

The input file is over 2Mb in size so I really do not want to read it in and process it line by line if possible.

Thanks.

Hi,

The easiest way for you to do is to use phpMyAdmin, if you have one. There is an option to run sql command manually. SO what you need to do is copy all the mysql query you have on file, and paste it in the textarea on phpMyAdmin, and run it from there.

If you don’t have one, you can user MODx.
Make a new snippet, and use this code



Load the snippet to one of you unused page, then use your browser to open that page JUST ONCE!.
After that remove the snippet.

You’re all set.

Hope it helps...

Thanks, Wendy, but that is the whole point.

The file is too big for phpMyAdmin, unless I gzip it first. There are more that 8,000 inserts. It’s OK to do it that way at the moment (in development) but when the site is live this will need to be done on a weekly basis, by users less technical than myself. They would also probably have to take the site down as it takes around 10 minutes to update the database.

I need to make it automatic so that it
1. locks access to that part of the database
2. performs it’s update
3. unlocks access
4. presents a page indicating any new items which may need attention

Heemm...

That’s really weird. Is there any specific reason why you did this? Why you need to backup the database every week and re-import it again? I don’t think you need to do that with MODx, right?

If you can give a brief description of what you’re trying to do, maybe the guys in here able to give a better suggestion. Sometimes 2 people thinking on the same problem will result in a better solution, rather than one.

The other way to do it is to make an upload file script that will get the uploaded file and read it line by line and put it in the string stream to be passed on to the mysql database query. I don’t really think that this is a good way to do it though, because it will stall your site for a moment, an causing corruption to the db, if there is another query being processed simultaneously on the same server.

Regards,

The data is sourced externally, and arrives weekly via RSS feed, as XML. I am not sure at this time whether it is pushed to us or whether we have to go and fetch it.

If you are interested it is a schedule of all films (movies to you ) showing in the UK for that week. The app is "simply " a "what’s on near me" query. It’s been done many times before on the web but never with MODx, I bet!

Initially I was looking at pure XSL queries and transforms but, as the file is so large, my first attempts took so long to process that I decided to do a relatively simple transform to SQL and load it into the database. Once there I can perform my query quite quickly, using indexes, and retrieve the data in a much simpler XML document, more suited to our needs. Then I can (hopefully) transform this to screen, newsfeed, and mobile format.

Having learned a bit more about XSLT I may go back and see if I can’t improve on my original attempt, however the database solution is so simple it will be hard to beat.

I had the thought earlier that I might try this database load external to MODx. I will give this a go tomorrow, as it’s getting quite late here.

Thanks.

Hi,

Is the xml feed is that big? I mean, do they feed all the latest movies or they include all the previous movie that had been uploaded to you since last week or before?

If that’s the case, I would rather write a external script outside MODx to do all the fetching of XML and feeding it into database, and let this being done either by a person or by crontab. While the lag in uploading all that data will take a few minutes, it’s best to lock the website down for a few minutes, this can be done together while syncronizing the data using crontab or by individual, because MODx has the ability to lock the whole website down, and I believe it can be done externally, by changing the config data outside MODx using external script that you build.

But I assume there will be no editing capability from the users, which means that the only database modification happen during the syncronization with the external source, which I believe a RSS feed, right? So while inserting the database, even when you did not lock the MODx website, it still able to perform smoothly, even though at that time the users will have different entries everytime they browse through the movie though.

Another solution, if it’s a big kind of project website, you can use two mysql database server, and both of them will syncronize each other. So while the other synronizing with the rss feed, the other will serve the user with the old unsyncronize data, and later after the syncronization done, the system will swap and use the latest syncronized database, and the other one will be syncronized again with the rss feed or from that latest updated database. (Sounds too complicated I believe)

Anyway, hope you can find your best solution. The project sounds interesting though.