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 :)
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.