Web app to subset, link and extract from multiple MySQL tables

[bairn1]

Hi there,

 

I'm currently developing a web application in PHP / MySQL and am looking to add a feature that is going to be pretty complex and I'm not even sure whether it'll work. Any advice would be much appreciated!...

 

I've got a database with around 15 tables, most with a few thousand records, but a couple with hundreds of thousands, and with some containing just a few fields, but some around 100. All contain a numeric "User ID" field which can be used as a link between tables, with most tables having more than one record for each user.

 

What I'm looking to do is to develop a webpage that allows someone without MySQL experience to extract linked information from multiple tables.

 

For example, they might select "Table 2" from a drop down list of all the table names, and choose 10 fields from a list box as the fields from this table they want to extract. They might also use a list box and an input field to apply a condition of, say, "gender" = "male", to only include some of the records in this table.

 

They would then choose fields and set subsetting conditions for another few tables in the same way.

 

Then when they click on an "extract" button, there would be PHP code to generate some pretty complex MySQL to subset and link the tables and extract the results. I'd presumably create temporary MySQL tables for each and then use JOINs to link these and get the final results.

 

As a further complication, one of the tables needs to be matched on a date as well as the user ID. If I call this table "Table 10", and say it has an ID field, a date field and an address field, with a record created every time the user changes the address, then when I want to link this table in with the others, I want to compare the date on this table with a date field on another table (e.g. an "order date") to get the relevant address at that time.

 

Also, for cases with multiple records per user ID, I would want every possible combination to be extracted. This could mean that one big query looking at lots of tables with no subsetting conditions could result in millions of records in the final results!

 

I hope all this makes sense. Basically, before I take this any further I'm trying to find out, first of all, if this might be possible. I'm pretty confident I could program the PHP to do it, but am worried that the queries will just take too long to run that it will be completely unusable.

 

Also, if this is possible, I was wondering if anyone knew of an off-the-shelf package that I could either just plug the database into and hey presto, or I could adapt to work with my database.

 

Many thanks for your time, and any help is very much appreciated.

[fenway]

Sounds like you're trying to build crystal reports.