Page 1 of 1

Multi table csv

Posted: Sun May 16, 2010 1:49 am
by kerepuki
Hi guys, I am hoping someone can help.

I am trying to create a CSV from multiple tables but I cant seem to figure out the code.

My tables:
t_application which has application details including a serialized column of services. i.e. gas, electricity, water, telephone and a customer id which is related to the customer table.
t_customer which has the details of the customer, first name, last name etc.
t_plan which has the details of the plan for each (electricity, gas, water...) service and whether the plan is the default and the provider it belongs to.
t_service which has the details of the service (electricity, gas, water etc).
t_service_connection which is acting as a join table between application, plan, service.

Now, if a new application is submitted by customer John Smith with electricity and gas selected, the code writes the data to t_application with the serialized column a:2:{i:0;s:11:"Electricity";i:1;s:3:"Gas";}. At the same time, the customer details are written to t_customer and details written to t_service_connection (app_id, plan_id, service_id).

Now, some services can have the same provider, provider_1 = electricity, gas, provider_2 = water, provider_3 = telephone and so forth.

Each service (elec, gas etc) is a new row in t_service_connection:
row 1 = service_id:1, plan_id:1, app_id:1
row 2 = service_id:2, plan_id:2, app_id:1
row 3 = service_id:3, plan_id:3, app_id:1
row 4 = service_id:4, plan_id:4, app_id:1
row 5 = service_id:1, plan_id:1, app_id:2
row 6 = service_id:2, plan_id:2, app_id:2
row 7 = service_id:3, plan_id:3, app_id:2
row 8 = service_id:4, plan_id:4, app_id:2
etc...

When I get my application query I receive 2 records, app_id:1 and app_id:2. This is fine.
I now need to unserialize the service column to check what services the application needs. app_id:1 needs elec and gas. app_id:2 needs water etc.
I now need to look up the join table (t_service_connection) to get the plan_id for each service required. Because app_id:1 needs elec and gas, I only need to get these rows. Thats fine too.

So now I have 3 results. 2 of those results have the same application/customer details which I only really need one set but with the different plan details.

I need to now put 2 rows in a CSV with one set of customer details for each row and both (elec, gas) plan details i.e.

app_id:1, first_name:John, last_name:Smith, elec_plan:elec home, gas_plan:gas home...
app_id:2, first_name:Jane, last_name:Smith, water_plan:water home...

but instead I have

app_id:1, first_name:John, last_name:Smith, elec_plan:elec home...
app_id:1, first_name:John, last_name:Smith, gas_plan:gas home...
app_id:2, first_name:Jane, last_name:Smith, water_plan:water home...

I dont know how to write this query. A bit confusing but I hope someone can help.