Help handling array data

[Darkmatter5]

I have an mysql query that gives me an array with the following data

 

game_idtitledevnamegenrepubnamesysname

5Blue DragonMistwalker, ArtoonRole-PlayingMicrosoft Game StudiosXbox 360

1Fable 2Lionhead StudiosAction Role-PlayingMicrosoft Game StudiosXbox 360

4Fallout 3Bethesda Game StudiosRole-PlayingZeniMax MediaXbox 360

4Fallout 3Bethesda Game StudiosRole-PlayingBethesda SoftworksXbox 360

 

Now notice Fallout 3 is listed twice as it has multiple publishers as pubname.  How can I handle the array to output the following.

 

Title: Blue Dragon

Developer(s): Mistwalker, Artoon

Genre: Role-Playing

Publisher(s): Microsoft Game Studios

System(s): Xbox 360

 

Title: Fable 2

Developer(s): Lionhead Studios

Genre: Action Role-Playing

Publisher(s): Microsoft Game Studios

System(s): Xbox 360

 

Title: Fallout 3

Developer(s): Bethesda Game Studios

Genre: Role-Playing

Publisher(s): Bethesda Softworks, ZeniMax Media

System(s): Xbox 360

 

Thanks!

[Darkmatter5]

Should I somehow form the data as it's written to the array or can you rearrange, merge, delete the data of the overall array so it's the way you need it before it's echoed to the page or do I echo it the way I want?  I'm really confused on this one.

[bluesoul]

I believe I'd try and add another publisher column or two to the table just for instances like this, giving them both the same game_id is going to be a primary key violation.  So perhaps...

 

game_id title devname genre pub1name pub2name sysname

4 Fallout 3 Bethesda Game Studios Role-Playing ZeniMax Media Bethesda Studios Xbox 360

[sasa]

SELECT title, devname, genre, GROUP_CONCAT(pubname SEPARATOR ', ') as pubnames, sysname FROM table_name GROUP BY pubname

[Darkmatter5]

I think I need to get all the data pieces at a time and along the way formulate and combine the data ultimately ending with an array with all the data formulated how I need it and then displaying the array.

 

I have an array that has the base info for each game.  Then I'm running a while on that array that will get all the publishers into an array for each specific game.  My problem is I can't figure out how to take the array and organize it and put it back into the base array.

 

Here's my base array

game_idtitle

4Fallout 3

 

Here's the publishers array

name

Bethesda Softworks

ZeniMax Media

 

So how can I take the data from the publishers array, combine the two rows into a string separating the two by a "," and putting that string into the base array with the Fallout 3 entry as publishers.  So the new base array will be:

 

game_idtitlepublishers

4Fallout 3Bethesda Softworks, ZeniMax Media

[sasa]

sorry

you must GROUP BY title not devname

[Darkmatter5]

I tried the way you had it but it wouldn't work.  I tried this and it almost worked.

 

SELECT games.*, GROUP_CONCAT(' ', CONCAT_WS(' ', publishers.name) ORDER BY publishers.name) AS pubname

FROM games

INNER JOIN game_publishers

ON game_publishers.game_id=games.game_id

INNER JOIN publishers

ON publishers.publisher_id=game_publishers.publisher_id

INNER JOIN game_systems

ON game_systems.game_id=games.game_id

INNER JOIN systems

ON systems.system_id=game_systems.system_id

GROUP BY games.title

ORDER BY games.title ASC

 

This orders things right, but the pubname field shows the same publishers multiple times.  This is because each game has multiple systems, I just didn't include that part of the tables.  So if Fallout 3 is on 360, PS3 and PC it'll show "Bethesda Softworks, Bethesda Softworks, Bethesda Softworks, ZeniMax Media, ZeniMax Media, ZeniMax Media" as pubname.  How can you make it only select distinct publishers?

[Darkmatter5]

Actually here's even better code

 

SELECT games.*,

GROUP_CONCAT(DISTINCT developers.name ORDER BY developers.name ASC) AS devnames,

GROUP_CONCAT(DISTINCT genres.genre ORDER BY genres.genre ASC) AS genres,

GROUP_CONCAT(DISTINCT publishers.name ORDER BY publishers.name ASC) AS pubnames,

GROUP_CONCAT(DISTINCT systems.name ORDER BY systems.name ASC) AS sysnames

FROM games

INNER JOIN game_developers

ON game_developers.game_id=games.game_id

INNER JOIN developers

ON developers.developer_id=game_developers.developer_id

INNER JOIN game_genres

ON game_genres.game_id=games.game_id

INNER JOIN genres

ON genres.genre_id=game_genres.game_id

INNER JOIN game_publishers

ON game_publishers.game_id=games.game_id

INNER JOIN publishers

ON publishers.publisher_id=game_publishers.publisher_id

INNER JOIN game_systems

ON game_systems.game_id=games.game_id

INNER JOIN systems

ON systems.system_id=game_systems.system_id

GROUP BY games.title

ORDER BY games.title ASC

 

This works perfectly, but I can't get a ", " in between each concated item.  I just uses the default "," and the SEPERATOR part of the code refuses to work.  I know that's what I need to use, but it won't work.

 

This is turning out to be a sql topic, is there a way to change this topic to the sql forum?