So I have a MySQL table:
Table Data:
id | domain | title | Full Url to Page | ... etc ...
1 | place.com | Place Something | http://place.com/1...
2 | place.com | Place Something Else | http://place.com/2...
3 | place.com | Some Other Place | http://place.com/3...
4 | pets.com | Cats Dogs Oh My | http://pets.com/2....
5 | pets.com | Bird Edition | http://pets.com/3....
What I need (in PHP / JQuery) is to get an array of id's for each unique domain. So the final output that I want for the above table is:
$finalArray = array('place.com' => array(1, 2, 3), 'pets.com' => array(4, 5));
My current solution is to grab all rows ordered by domain, heres the MySQL statment:
SELECT `id`, `domain` FROM `gsort_linkdata` ORDER BY `domain`
Which returns:
1 | place.com
2 | place.com
3 | place.com
4 | pets.com
5 | pets.com
I then loop through the rows in PHP and break them into arrays. I would prefer to pull already broken up data from the database. Is that possible? Thank you!
You could use GROUP_CONCAT:
SELECT GROUP_CONCAT(`id`), `domain` FROM `gsort_linkdata` GROUP BY `domain`
(Be aware of group_concat_max_len.)
But because there is no way to pass an array from MySQL to PHP, you need to split up the resulting string in PHP or JS, so I think your current method is the better one. Your method is safe and really just a one-liner in PHP, anything else (including GROUP_CONCAT) is more complicated .