mysql field having same value

help organizing

[electricocean]

Hi,

I was wondering if there is a way to organize fields with the same value like so:

database-
row 1:
field 1(name): Yamaha. (same name as below)
field 2(item): Speakers

row 2:
field 1(name): Yamaha. (same name as above)
field 2(item): Keyboards

Output:
Yamaha: Speakers, Keyboards

thanks,
electricocean

[breezeman]

SELECT * from yourtable WHERE field1='yamaha'

or make a table with all manufacturers and use that array to select per manufacturer from your current table.

[vincevincevince]

SELECT `name`,`item` FROM `table` GROUP BY `name`

That should return them grouped by 'Yamaha', so you will have all the e.g. Yamaha first, then the next brand, etc.. however it will still say the brand with each of multiple rows.

If you really don't want to do it like that, then you nest two queries in a loop:

$dh=mysql_query("SELECT DISTINCT `name` FROM `table`");
while ($r=mysql_fetch_assoc($dh))
 {
 print "Name: $r[name]<br>";
 $dh2=mysql_query("SELECT DISTINCT `item` FROM `table` WHERE `name`=$r[name]");
  while ($r2=mysql_fetch_assoc($dh))
   {
    print " - Item: $r2[item]<br>";
   }
 }

[electricocean]

hi,

i first tried: SELECT `name`,`item` FROM `table` GROUP BY `name`

I have three names in the db. 2 yamaha and 1 pearl

it out puts

Yamaha: Speakers (doesn't show keybords)
Pearl: Drums

whats wrong?

electricocean

[mcibor]

It must be:

SELECT name, item FROM table GROUP BY name, item

However other than nested queries I recommend using plain php to make a tree-like structure, as I have told you in Counting Mysql.

Best regards
Michal Cibor

[coopster]

MySQL >= 4.1 has an aggregate function named GROUP_CONCAT [dev.mysql.com] that will do what you want there.

SELECT 
 field1, 
 GROUP_CONCAT(field2 ORDER BY field2 SEPARATOR ', ') as field2 
 FROM mytable 
 GROUP BY field1 
;

[electricocean]

well I am running MySQL 4.0.24

my page now says: ERROR: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(genre), COUNT(*) FROM jokes GROUP BY genre' at line 1

[electricocean]

ok I got it now by using 2 query. Thanks all.

now I amjust having problems with html in the fields.

electricocean