[SOLVED] Query Help

[[email protected]]

What i am trying to do is get all videos where the game has a console of #123, and has a group of #12345.

Now i have to do it differently because the consoles are not in the video table, they are in the game table.

 

SELECT

video.groups,video.randname,games.consoles,video.timestamp,video.id

FROM

video,

games

WHERE

video.groups LIKE "%12345%"

AND

games.consoles LIKE "%123%"

LIMIT 3

 

That query is, when i do while($row=mysql_fetch_array...etc, outputting 3 of the same video randnames. It outputs 3 of the same video id's.

 

Help please?

 

[MadTechie]

try this

SELECT DISTINCT(video.id, games.id), 
video.groups,video.randname,games.consoles,video.timestamp
FROM
video,
games
WHERE
video.groups LIKE "%12345%"
AND
games.consoles LIKE "%123%"
LIMIT 3

[[email protected]]

I appreciate your help, thank you.

But when i try to use that query, i get this mysql error:

Operand should contain 1 column(s)

[MadTechie]

okay.. try grouping

SELECT video.id, games.id, video.groups,video.randname,games.consoles,video.timestamp
FROM
video,
games
WHERE
video.groups LIKE "%12345%"
AND
games.consoles LIKE "%123%"
GROUP BY video.id, games.id
LIMIT 3

[[email protected]]

Doing that worked, but now its back to displaying the same video each time.

[MadTechie]

can you post some sample data deom the database

[[email protected]]

Tables:

table: video

important rows:

id int(30)

randname int(

gameid int(10)

groups text

 

table: games

id int(30)

name text

consoles text

 

Samples:

Games:

id: 8

name: Half Life 2

consoles: 4,8,9

 

Video:

id: 6

randname: 57954341

gameid: 8

groups: 78239

 

 

Now i want to get all videos which it's game has the console number 4, and that are in the group 78239. I can get the group, but getting the console of the game is different, i'm not sure how to do it.

[MadTechie]

well i normally i would create another table for consoles and link it in..

but you could do this

SELECT video.id, games.id, video.groups,video.randname,games.consoles,video.timestamp
FROM
video, games
WHERE
video.groups LIKE "%12345%"
AND
CONCAT(',', games.consoles, ',') LIKE "%,4,%"
GROUP BY video.id, games.id
LIMIT 3

 

basically it changes "4,8,9" to ",4,8,9," then looks for [something],4,[something]

this works better as [something]4[something] would find 4 or 40 or 14, and if we don't concat the comma's then 1,2,3,4 wouldn't work (no trailing comma)

[fenway]

Two things --- first, DISTINCT is not a function, it's a modifier to SELECT; it filters out rows where ALL field values in the column list match.  Very different than GROUP BY.  Second, you can use FIND_IN_SET() if you values are comma separated without all that string manipulation.  But it's a bad idea to store lists in fields.

[MadTechie]

Thanx fenway the DISTINCT & FIND_IN_SET() info was useful to me also,

and i totally agree with the part about stroring lists in fields as i said

well i normally i would create another table for consoles and link it in..