update automation

[loomer]

I'm working on a project with PHP/MySQL that my require some automation but I'm not quite sure how to go about it.

Basically there will be a MySQL database consisting of a number of images and reports that correspond to different customer orders. When a user is logged in he can go to page to view an order history with these reports. What my client was interested in doing is sending me .csv files (or whatever files are needed) that I would upload to MySQL on a daily basis. These files would basically be the customer orders. So, basically the customer reports would be updated at the end of the day so they are kept current.

Is there a way to setup an automated process like this? Where either he could send me files to import into MySQL or where my client could do so directly? I'm working on a Windows machine.

Thanks.

[raf]

They only good way to handle this, is by letting your client input the orders directly into the MySQL database. You only need o write a few PHP pages for this (sort of administration-module). A loginpage (cause this form should only be accesible for authorized users). A regular form where he inputs all date. And a PHP to check all the values and insert the record.
Or better still, let the customers themselves insert the orders (if possible) and add a status to them. In the administration arrear, your client can then view the orders that were inserted but not yet aproved. He could then approve them and have an automatically generated mail send to the customer. etc etc.

If he doesn't wan't to fill them in online; or if they are extracted from another app, you could write small scriptfile ( which canbe automatically run in batchmode http://www.mysql.com/doc/en/Batch_mode.html ) where the file is imported with the load data command ( http://www.mysql.com/doc/en/LOAD_DATA.html )
On a windowsmachine, you can set up a tast that will run daily (settings, control pannel, systemmanagement, sheduled tasks or something like that. Then there is a wizard for creating a new task)

But it's kinda risky, these scheduled tasks. You'd need to make sure that the file is renamed or so after it was processed ...

[loomer]

Okay I have some more details from my client.

The situation is my client produces a piece of software. He has a system where he can extract order information from his clients. This data is basically just a spreadsheet of information... user info, order info, dates, etc. He can provide this information in the form of a .csv file.

Now, basically I am creating a website/database that will sort of be a duplicate of a lot of the data he is extracting. Some of that information will be made available online to registered users (like order status, order info, etc). A lot of the data will not be viewable by the end user but will still be stored in MySQL.

Sooooo.:D What my client would like is for him to extract the data from his client on a daily basis and for him to send me a file (preferably .csv or anything easily imported into MySQL) which would then be imported into MySQL on a daily basis. Basically having the user order status database updated on a daily basis.

Now I know your first thought is get my client's client to input the data. Firstly, there is a large amount of data/fields for each order and the software they are using is basically doing that already. So I doubt they will want to do that. Orders are also coming from different salespeople and such.

So back to my original question... Is there a solution to take care of some of this automation? I can recieve FTP files no problem but I'm not sure how I can setup MySQL to import a file at the end of each day for example. Is CRON able to do this kind of thing? (I'm not familiar with it).

Anways.. Any suggestions or pointers would be wonderful. I hope I didn't waste too much of anybody's life with my questions.
:D :

Cheers.

[michael.hd]

So what if... rather than send you the csv file, they could go on your website and upload it through a form.

PHP can do this with little effort. On uploading of file a php script could automatically parse the file into an array of values and insert them into the database.

On error an email could be sent to you which identified how many records were/were not inserted.

You can sit down and have a coffee - hoping that you never get that email!

[loomer]

Interesting suggestion! Thanks for pointing that out... I'll take a look into that... though I'm not a coffee drinker:p

Thanks