I have data stored in MYSQL database as shown below:
id t_id type color Y S M L
2 2606 2 Black DNA 1,5,8 4,2,6 7,3,9 10,11,12
I want the query to return like this with PHP:
Total:
Black DNA
1 (Y)
2 (S)
3 (M)
4 (S)
5 (Y)
6 (S)
7 (M)
8 (Y)
9 (M)
10 (L)
11 (L)
12 (L)
I am using Joomla 2.7, Here is what I have tried in PHP:
$query = "SELECT DISTINCT(e.id) as id, e.color, GROUP_CONCAT(e.S) as small FROM #__bl_equipment as e WHERE e.type = 4 AND e.t_id = 2606";
$db->setQuery($query);
$equip1 = $db->loadObjectList();
<table>
<?php foreach($this->equip1 as $equip){
echo '<tr><td>';
echo $equip->color;
echo '</td><td>';
echo $equip->small;
echo '</td></tr>';}
?>
</table>
Only result I have been able to get:
Black DNA 1, 2, 3
That would be too complicated with SQL (and doubt it's possible even)
I'd do the following - Get Y, S, M and L columns - Parse each using commas (so you get numbers) and turn them into arrays - Sort each
So now you should have something like
y_numbers = [1,5,8]
s_numbers = [2,4,6]
m_numbers = [3,7,9]
l_numbers = [10,11,12]
Then inside a loop, look at first element of each array, find the smallest, print letter based on which array it is, iterate the pointer for that array... continue until you've exhausted all numbers