MySQL WHERE and LIKE question

[pancakepalace]

Hi,

This is my first time posting here so I would like to say hi to all and thanks in advance if you can help.

I am running the following query to a MYSQL database:

SELECT m.album_id, m.user_id, at.albumtitle, ar.artist, m.year, ge.genre, sg.subgenre, f.format, cd.condition, m.forsale, m.price, cu.currency FROM rc_music AS m LEFT JOIN rc_music_albumtitles at ON (m.albumtitle_id = at.albumtitle_id) LEFT JOIN rc_music_artists ar ON (m.artist_id = ar.artist_id) LEFT JOIN rc_music_genres ge ON (m.genre_id = ge.genre_id) LEFT JOIN rc_music_subgenres sg ON (m.subgenre_id = sg.subgenre_id) LEFT JOIN rc_music_formats f ON (m.format_id = f.format_id) LEFT JOIN rc_music_conditions cd ON (m.condition_id = cd.condition_id) LEFT JOIN rc_music_currencies cu ON (m.currency_id = cu.currency_id) WHERE m.user_id=2 AND (at.albumtitle LIKE '%l%') OR (ar.artist LIKE '%l%') OR (m.year LIKE '%l%') OR (ge.genre LIKE '%l%') OR (sg.subgenre LIKE '%l%') OR (f.format LIKE '%l%') OR (cd.condition LIKE '%l%') OR (m.forsale LIKE '%l%') OR (m.price LIKE '%l%') OR (cu.currency LIKE '%l%')

The problem is that this query doesn't care about WHERE m.user_id=2. Instead it returns results for all users. I also tried putting the likes before the Where m.user_id=2 like so...:

SELECT m.album_id, m.user_id, at.albumtitle, ar.artist, m.year, ge.genre, sg.subgenre, f.format, cd.condition, m.forsale, m.price, cu.currency FROM rc_music AS m LEFT JOIN rc_music_albumtitles at ON (m.albumtitle_id = at.albumtitle_id) LEFT JOIN rc_music_artists ar ON (m.artist_id = ar.artist_id) LEFT JOIN rc_music_genres ge ON (m.genre_id = ge.genre_id) LEFT JOIN rc_music_subgenres sg ON (m.subgenre_id = sg.subgenre_id) LEFT JOIN rc_music_formats f ON (m.format_id = f.format_id) LEFT JOIN rc_music_conditions cd ON (m.condition_id = cd.condition_id) LEFT JOIN rc_music_currencies cu ON (m.currency_id = cu.currency_id) WHERE (at.albumtitle LIKE '%l%') OR (ar.artist LIKE '%l%') OR (m.year LIKE '%l%') OR (ge.genre LIKE '%l%') OR (sg.subgenre LIKE '%l%') OR (f.format LIKE '%l%') OR (cd.condition LIKE '%l%') OR (m.forsale LIKE '%l%') OR (m.price LIKE '%l%') OR (cu.currency LIKE '%l%') AND m.user_id=2

Same result...

Anyone know how to make it so I just get results for users with an id of 2..?

Thanks,
pel

[Mall23]

Its late and I'm getting blurry eyed and you two queries just look like base64 encoding to me.

I'd suggest breaking it up into smaller bites and digesting it that way. Then put the small pieces together to build the whole.

[SuzyUK]

Hi pancakeplace and Welcome to WebmasterWorld!

I'm new to MySQL as well so this could be wrong.. but try

SELECT m.album_id, m.user_id, at.albumtitle, ar.artist, m.year, ge.genre, sg.subgenre, f.format, cd.condition, m.forsale, m.price, cu.currency FROM rc_music WHERE m.user_id=2 AS m LEFT JOIN....etc....

Suzy

[Dijkgraaf]

The problem is that you have OR's in your where clause

WHERE m.user_id=2
AND (at.albumtitle LIKE '%l%')
OR (ar.artist LIKE '%l%')
OR (m.year LIKE '%l%')
OR (ge.genre LIKE '%l%')
OR (sg.subgenre LIKE '%l%')
OR (f.format LIKE '%l%')
OR (cd.condition LIKE '%l%')
OR (m.forsale LIKE '%l%')
OR (m.price LIKE '%l%')
OR (cu.currency LIKE '%l%')

If anyone of those OR's are try then the whole where clause is true. I think you probably didn't want this but rather

WHERE m.user_id=2
AND ((at.albumtitle LIKE '%l%')
OR (ar.artist LIKE '%l%')
OR (m.year LIKE '%l%')
OR (ge.genre LIKE '%l%')
OR (sg.subgenre LIKE '%l%')
OR (f.format LIKE '%l%')
OR (cd.condition LIKE '%l%')
OR (m.forsale LIKE '%l%')
OR (m.price LIKE '%l%')
OR (cu.currency LIKE '%l%'))

Notice the extra brackets after the AND and at the end.