GROUP_CONCAT problem

MattG

I use GROUP_CONCAT to group together a set of names that all match another field the trouble is if I want to view it by an individual person it won't group the names together and will only have the name of the person's id that I match it to in the database.

Is there anyway to get around this so that the GROUP_CONCAT will work in this way?

As an example if I'm viewing all items in category A it'll group them together perfectly and output

catID: 1
catName: foobar
People: Joe Bloggs,John Doe

but if I then try to look at it through Joe Blogg's profile linking it to his id in the database (WHERE id = $_GET[pid]) it'll output

catID: 1
catName: foobar
People: Joe Bloggs

I don't want this because I want it to group together all people that match a certain criteria no just everyone in a specific category.

I don't want to have to have a for loop in the PHP code going through every result in the table trying to match the id because eventually this table will be very big - in the 10s of thousands I'd imagine - so would create more work for the server every time someone goes into a specific person's page.

If anyone can help with a way to do it I'd be very grateful.

The output fields works out as

CatID, SubCatID, PersonID, People

The SubCatID is what links people together within a parent category.

If there's anything else that doesn't make sense let me know.

tomhath

You need to select on the catagory, not the pid. Can't help much more without seeing the queries you already have.

MattG

My query is...

SELECT title.name, scenes.details, GROUP_CONCAT(people.name) as starring FROM titles
LEFT JOIN scenes ON titles.id = scenes.titleid LEFT JOIN people ON scenes.personid =
people.id WHERE scenes.siteid = $_GET[siteid] AND people.id = $_GET[pid] GROUP BY
scenes.sceneid

It's used to list all actors that feature in the scene of a particular title. The example above was one I felt simplified it but hopefully you can help me with the above query.

MattG

okay, I've sorted one that works but I need to know if this is going to cause a lot of strain or site slowness when it comes to many people accessing the page and having this query due to the the length and different actions (SUBSTRING, CONCAT, GROUP_CONCAT etc)

I'm not too savvy on that area so hopefully it won't cause too much strain/slowdown as it's the only way I've managed to do it...

SELECT websites.name as sitename, scenes.details, CONCAT(',', SUBSTRING(GROUP_CONCAT(scenes.personid),1,7)) as pids, GROUP_CONCAT(people.name) as starring FROM websites LEFT JOIN scenes ON websites.id = scenes.siteid LEFT JOIN people ON scenes.personid = people.id WHERE scenes.siteid = '$GET[sid]' GROUP BY scenes.sceneid HAVING pids LIKE '%,$GET[pid],%'