Can MySQL & PHP accomplish what I need?

[Hokie007]

Hello,

 

I would really appreciate an experienced user's advice.  I am a newb and only have limited exposure to databases, mainly MS Access.  I have a task at work to construct a database. 

 

The database needs to:

1.  read/import the data from multiple CSV formatted files contained within a directory.  The CSV files share identical row header text.  (The files contain sensor data from systems installed on automobiles.) 

2.  be accessible by an external/off-site employee (over the Internet).

3.  be able to search, count, and filter through the data contained within the CSV files and display the results of those operations to the off-site employee for the purposes of algorithm testing.

 

Some practical examples would be for the off-site employee to be able to run a query to count the number of CSV files that have an Acceleration value somewhere within the individual file of less than 0.4 

 

I know that databases can be very powerful, so the simple sorting/filtering/counting based upon specified criteria does not seem to be a lot to ask from a database.  Since MS Access does not appear to have a good ability to be accessed from the Internet, would a Linux>Apache>MySQL>PHP solution work for what I need?  I'd like to see if it is a feasible solution before proceeding further with learning and installation.  If it is indeed feasible, how difficult do you estimate that it would be for me (with no experience with Apache, MySQL, or PHP) to accomplish?

 

Thanks for your time and I look forward to hearing your opinions and recommendations.

[kickstart]

Hi

 

Certainly possible and not that difficult.

 

Do the files you read change or are they static once uploaded? Rereading hundreds of files each time the page is accessed to see which have changed would be time consuming.

 

How long it will take will depend on how flexible the solution needs to be. Do the number of columns on the input files change over time? Do you need to cope with these changes without any code changes?

 

All the best

 

Keith

[Hokie007]

Thanks for your reply Keith. 

 

The files do not change once in the source directory.  They are static, however there would likely be new files to be added weekly or so.  I suppose that MySQL would be able to account for those newly added files via a incremental import process/command.

 

The columns are fixed across all the CSV files.  The only difference would be in the number of rows, longer duration (time) files would have more rows containing data values, but the number of columns and the "name"/ header of the column is the same across all the CSV files, as if they were all created based on a template.

 

For the external employee, is there a "console" they can access, from which to interface with the database and write SQL queries, or will that need to coded in PHP as a web form? 

 

As it appears that this may be a valid solution to pursue, I'll proceed with a LAMP setup and hope not to hit any major sticking points in the process.

 

Thanks again for taking the time to reply and for your thoughts.  I sincerely appreciate it. 

 

Pete

[kickstart]

Hi

 

Would be best to run some kind of extract regularly.  Loop through the files in the directory and process any new ones. Extract the data from the new ones and put it into a suitable set of database tables.

 

If the columns are consistent then you can probably import them easily. However if some of the columns have a limited number of values then you might want a table of those values and insert on the main table just a pointer to the appropriate value (ie, if a column was for car make, have a table of makes with a numeric ID field for each, with your main table just storing the numeric id).

 

There are front ends for MySQL (ie, phpmyadmin) which allow access to the tables and to run SQL but they are not designed for user use and I would advise against opening the tables up that way. It would probably be best to code the extracts that people want so they can just click a button to get the data they want.

 

All the best

 

Keith