通过使用php拆分为N个数组来组合mysql数据库记录

Question

I have a mysql database with thousands of rows, each row contains a data for a sale transaction for one of the products. These products have unique IDs and total number of products is undefined.

ID      Product Name     Transaction ID     Date

123   Product 1    ergkljdlkjfgklgh    Sept 1, 2015
144   Product 5    eyghfeoopjfgkl    Sept 5, 2015
123   Product 1    jdlkjfghrttgklgej    Sept 8, 2015
144   Product 5    ngfukhjkgfhjjjcs    Sept 15, 2015
123   Product 1    utebhjgdsdhjfdt    Sept 20, 2015

and so on.

With help of php, I need to convert database into a simple list with each row containing unique product ID, product name, number of occurrences of that product in database, and sale dates joined into one string (that will be processed later).

To illustrate, the table above should convert into:

ID      Product Name     Number of Sales     Dates

123   Product 1    3    Sept-1-2015_Sept-8-2015_Sept-20-2015
144   Product 5    2    Sept-5-2015_Sept-15-2015

I think the way to do it has to do with sorting and looping through different arrays, however I am a bit confused and cant quite wrap my mind around it.

Do I sort by ids, get all unique ids into array, loop through array to retrieve the groups and numbers of items, make a separate array for each group to get the dates?

Maybe someone who've encountered such challenge before can point me into the right direction :)

Answer 1

Do you want to create new table or work on the same one. If its a different one then its simple that you do a select using on the columns and then order them using the ID. To get the dates you can use COALESCE(field1, '_') || COALESCE(field2, ''). Else you can handle that programmatically in PHP by having a global variable that holds the value of date and then for each instance of the product append the dates using the dot operator.