Determine array row by value search?

[sorenchr]

Hi, i have a fairly compley problem i just can't get my head around. I was hoping you guys could point me in the right direction.

Im using a mysql query that fetches the following table of data:

 

username

mid

rating

testUser

1

10

testUser

2

8

testUser

3

5

dummyUser

1

1

dummyUser

2

3

dummyUser

3

3

 

And i want to display the table on my php-page like this:

 

[/td]

[td]mid:1

mid:2

mid:3

testUser

10

8

5

dummyUser

1

3

3

 

I've tried multiple functions with headache outcomes. I somehow have to assign each users mid rating to his/her username, so that the username only pops up once. Im thinking about storing the data in an array, however this just seems like the dumbest solution. Isn't there an easier way to sort this out?

 

Thanks for your replies.

Best regards

Sorenchr

[laPistola]

wouldn't it be alot easier to have the DB fields as

 

username, mid1, mid2, mid3 ??

then in the mid1, mid2, mod3 fields insert the rating value

[sorenchr]

I've already created the db and used its structure in my php files, so changing it would require a lot more work versus finding a solution for this problem

[laPistola]

it miffs me to why you would have designed your db that way but im sure you have a reason, does the table have a primary key?

[sorenchr]

The select-query is actually a join of two tables. Im registering the users ratings of mid's in one table, and the users are registered in another. They are joined together by userid's.

[Barand]

use GROUP_CONCAT

 

SELECT username, GROUP_CONCAT(mid) as mids
FROM tablename
GROUP BY username

 

(Sounds like laPistola never heard of normalization)

[laPistola]

(Sounds like laPistola never heard of normalization)

 

Sounds like Barand never heard of curtacy (spelling sorry) to new members that may not know as much as the ones that spend day in and day out programing!!

[Barand]

it miffs me to why you would have designed your db that way ...

 

And what about your lack of curtesy getting miffed because he didn't take your (bad) advice?

[lampstax]

Hey Barand, with your technique

 

Wouldn't the results be a string like

 

testUser      "10 8 5"

dummyUser  "1 3 3"

 

So it would need to be exploded after retrieving to get individual values ?  Or am I getting something wrong. 

 

Dont worry about laPistola man .. you're the sql-superman for rookies like me .. haha .. jk .. but you did help me a few times before.

 

 

[Barand]

Well, by default they would be comma-separated, but at least you would get everything conveniently in a single row for outputting in the desired format.

 

You could do this to aid output in html table

 

SELECT username, GROUP_CONCAT(mid SEPARATOR '</td><td>') as mids
FROM tablename
GROUP BY username