Darkmatter5 Posted January 12, 2009 Share Posted January 12, 2009 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! Quote Link to comment Share on other sites More sharing options...
Darkmatter5 Posted January 12, 2009 Author Share Posted January 12, 2009 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. Quote Link to comment Share on other sites More sharing options...
bluesoul Posted January 12, 2009 Share Posted January 12, 2009 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 Quote Link to comment Share on other sites More sharing options...
sasa Posted January 12, 2009 Share Posted January 12, 2009 SELECT title, devname, genre, GROUP_CONCAT(pubname SEPARATOR ', ') as pubnames, sysname FROM table_name GROUP BY pubname Quote Link to comment Share on other sites More sharing options...
Darkmatter5 Posted January 12, 2009 Author Share Posted January 12, 2009 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 Quote Link to comment Share on other sites More sharing options...
sasa Posted January 12, 2009 Share Posted January 12, 2009 sorry you must GROUP BY title not devname Quote Link to comment Share on other sites More sharing options...
Darkmatter5 Posted January 12, 2009 Author Share Posted January 12, 2009 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? Quote Link to comment Share on other sites More sharing options...
Darkmatter5 Posted January 12, 2009 Author Share Posted January 12, 2009 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? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.