Problem checking against multiple fields

ld0121

Hi everyone.

I have a database with various fields, inc. Name, Info, Gay. Name = name of entry, Info = information about the entry, Gay = 1 for gay entry 2 for non gay entry.

I have constructed click through pages that show results from the database, always showing the gay entries first. ie checking for a 1 in the database. So thats all good and works fine. Im now trying to do a site search.... fill in a word and what should happen is it checks if the word is in name or info, then checks for gay 1 or 2. As the example below. %$trimmed% = search word.

//$query = "select * from ghdata1 where ghdata1.name like '%$trimmed%' OR ghdata1.info like '%$trimmed%' AND ghdata1.gay LIKE '1' ORDER by rand()";

if its gay = 1 then it puts the results on screen, then, also in the results page it goes on to

//$query = "select * from ghdata1 where ghdata1.name like '%$trimmed%' OR ghdata1.info like '%$trimmed%' AND ghdata1.gay LIKE '2' ORDER by rand()";

where it now looks for the '2' non gay entries and puts them on screen also.

But whats happening is that for some reason some gay entries are being shown with the non gay entries, so its not checking the 1 or 2 properly.

Ive had a mess with the code and it works perfectly if I only check against either name or info, but doesnt work when I try to check against both..,

this works...

//$query = "select * from ghdata1 where ghdata1.name like '%$trimmed%' AND ghdata1.gay LIKE '1' ORDER by rand()";

so what am i doing wrong how do i get it to check name and info and then gay.

Hope that makes sence.

Thanks in advance
Lee

leatherback

Use one query, and order by:
$query = "select * from ghdata1 where ghdata1.name like '%$trimmed%' OR ghdata1.info like '%$trimmed%' AND ORDER by ghdata1.gay, rand() ";

ps: You should use = whenever you know a value should match. So not ghdata1.gay LIKE '1' but ghdata1.gay = '1'

superwormy

First of all, you shouldn't be using LIKE '1' or LIKE '2' in SQL, its a crappy MySQL extension of the SQL standard to be able to use LIKE with integer fields. An integer is either equal or its not, so use = 1 or = 2 if its an integer field.

Also, you should clean up your SQL and use ( parenthesis ) so that it is clear what you're trying to do. Your SQL statement could mean either of these two things:

select * from ghdata1 where
(
ghdata1.name like '%$trimmed%' OR
ghdata1.info like '%$trimmed%'
)
AND ghdata1.gay LIKE '1' ORDER by rand()

select * from ghdata1 where
ghdata1.name like '%$trimmed%' OR
(
ghdata1.info like '%$trimmed%'
AND ghdata1.gay LIKE '1'
)
ORDER by rand()

See if those two things help. Also, if you're using the 'gay' field ( wouldn't be my choice of a field name seeing as its def. not politically correct in some people's eyes, but hey whatever ) as a boolean field, then the values should be 0 or 1 ( for MySQL without support for booleans ) or TRUE and FALSE if you're using a database with boolean support. 1 and 2 for a boolean field is not very intuitive or standard.

ld0121

Seems like swapping out LIKE '1' for = '1' has done the trick,

thanks for your help :o)

superwormy

Take it one step further, integer fields/values do not need to be surrounded in single quotes, in fact, some databases will barf and reject your query if you surround integers in single quotes:

fieldname = 1

instead of:

fieldname = '1'