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.
Multi table csv
Moderator: General Moderators