Sensible Method of Inserting Records In To MySQL

[Frank Pryor]

Hello,

I'd appreciate suggestions as I hash out my idea. Perhaps I'm going about
this the wrong way.

I have users using a third party windows application. They can export data
from this application directly to a text file (CSV). So far as I know,
there is no way to make this application talk directly to the MySQL server.

We're talking thousands of records here - it's not practical to have an HTML
form handled by PHP which inserts the data to MySQL... or is it??

What I thought about doing was to have them export the data to the CSV file
& then upload it to the web server using FTP. Once uploaded, I would have
them go to a page with a PHP script which would run the command to import
the data to MySQL. PHP can run shell commands, as I understand it from the
manual. Perhaps this would be a bitch on Windows, though.

Is this sensible? Suicide? Brilliant? Stoopid?

What would be the best method of tackling this issue?

FWIW, the site runs Apache on Windows 2000 w/ PHP4 / MySQL 4.

Thanks.

[Tom Thackrey]

Re: Sensible Method of Inserting Records In To MySQL


On 11-Aug-2003, "Frank Pryor" <news@groups.on ly> wrote:
[color=blue]
> I'd appreciate suggestions as I hash out my idea. Perhaps I'm going about
> this the wrong way.
>
> I have users using a third party windows application. They can export
> data
> from this application directly to a text file (CSV). So far as I know,
> there is no way to make this application talk directly to the MySQL
> server.
>
> We're talking thousands of records here - it's not practical to have an
> HTML
> form handled by PHP which inserts the data to MySQL... or is it??
>
> What I thought about doing was to have them export the data to the CSV
> file
> & then upload it to the web server using FTP. Once uploaded, I would have
> them go to a page with a PHP script which would run the command to import
> the data to MySQL. PHP can run shell commands, as I understand it from
> the
> manual. Perhaps this would be a bitch on Windows, though.
>
> Is this sensible? Suicide? Brilliant? Stoopid?
>
> What would be the best method of tackling this issue?
>
> FWIW, the site runs Apache on Windows 2000 w/ PHP4 / MySQL 4.[/color]

You might look at how phpMyAdmin handles this. They allow you upload the CSV
file and insert the records all from one page. The only problem I know of is
having the page timeout because of the large number of records.

--
Tom Thackrey

https://www.creative-light.com

[David Mackenzie]

Re: Sensible Method of Inserting Records In To MySQL

On Mon, 11 Aug 2003 20:16:40 -0400, "Frank Pryor" <news@groups.on ly>
wrote:
[color=blue]
>Hello,
>
>I'd appreciate suggestions as I hash out my idea. Perhaps I'm going about
>this the wrong way.
>
>I have users using a third party windows application. They can export data
>from this application directly to a text file (CSV). So far as I know,
>there is no way to make this application talk directly to the MySQL server.[/color]

I've never tried it, but you should be able to use an ODBC driver.
[color=blue]
>What I thought about doing was to have them export the data to the CSV file
>& then upload it to the web server using FTP. Once uploaded, I would have
>them go to a page with a PHP script which would run the command to import
>the data to MySQL. PHP can run shell commands, as I understand it from the
>manual. Perhaps this would be a bitch on Windows, though.[/color]

PHP needn't be run by a web server. You can run a PHP script from the
command line. If your program FTP'd the files into an upload
directory, you could write a script that checks this dir and processes
all the files therein. Schedule this script to run however often you
need it to.
[color=blue]
>Is this sensible? Suicide? Brilliant? Stoopid?
>
>What would be the best method of tackling this issue?[/color]

I'd try and get the app to talk directly to the database. Then you can
provide better feedback if things go wrong, and there's less to go
wrong in the first place.

Good Luck!

--
David (please modify address to david@ before replying!)