Page 1 of 1

MySQL Query Problem

Posted: Thu Oct 09, 2003 9:58 am
by theoph
I know this is a php forum, but since everyone here seem real smart and that many also are familar with mySQL, I have a question regarding a query that is giving me weird problems.

What is wrong with this query? When I take the "OR" argument out, the the query works, however, if I leave it in, the output is all messed up—data between table laos and table oikos don't matching up. It is like the argument "oikos.id = laos.oikos_id" is being ignored.

SELECT laos.lname, laos.fname, laos.directory, oikos.family, oikos.id, laos.oikos_id, oikos.address, oikos.city, oikos.st, oikos.zip, oikos.hphone, laos.wphone, laos.mphone, laos.email, laos.web, laos.pcell, DATE_FORMAT(laos.birthday, '%m-%d') AS birth, DATE_FORMAT(laos.aniversary, '%m-%d') AS wedding, UNIX_TIMESTAMP(DATE_ADD(laos.datestamp, INTERVAL 3 hour)) AS dstamp, laos.scell
FROM laos, oikos
WHERE oikos.id = laos.oikos_id AND laos.lname LIKE 'lastname%' AND laos.directory = 'Y' AND laos.pcell LIKE '$cell%' OR laos.scell LIKE '%$cell%'
ORDER BY laos.lname, laos.pfamily

Posted: Thu Oct 09, 2003 10:24 am
by JAM
We have an SQL section also... ;)

If I'm thinking correct about your issue, it might be because of:

Code: Select all

select * from table where id = 1 and foo = bar or bar = foo
select * from table where id = 1 and (foo = bar or bar = foo)
Spot the difference? Adding () around something, makes that evaluate as on its own...

Code: Select all

SELECT 
    laos.lname, laos.fname, laos.directory, oikos.family, oikos.id, laos.oikos_id, oikos.address, oikos.city, oikos.st, oikos.zip, oikos.hphone, laos.wphone, laos.mphone, laos.email, laos.web, laos.pcell, DATE_FORMAT(laos.birthday, '%m-%d') AS birth, DATE_FORMAT(laos.aniversary, '%m-%d') AS wedding, UNIX_TIMESTAMP(DATE_ADD(laos.datestamp, INTERVAL 3 hour)) AS dstamp, laos.scell
FROM 
    laos, oikos
WHERE 
    oikos.id = laos.oikos_id AND 
    laos.lname LIKE 'lastname%' AND 
    laos.directory = 'Y' AND 
    (laos.pcell LIKE '$cell%' OR 
    laos.scell LIKE '%$cell%')
ORDER BY 
    laos.lname, laos.pfamily
Move the () around to fit your needs. Additionally, you might read up on MySQL's JOIN's (not really an issue in this case, but worth reading).

Hope it helped.

Posted: Thu Oct 09, 2003 10:40 pm
by theoph
Puting the () around the <conditions-to-restrict-rows-returned> arguments fixed the problem. :wink:

Thanks

Posted: Fri Oct 10, 2003 12:37 am
by itsmani1

Code: Select all

select id FROM &#1111;table name] where id='$id' & abc='$abc'
you will give name of ur table instead of [table name]


Abdul Mannan

Posted: Fri Oct 10, 2003 12:40 am
by Paddy
Can you use an '&' instead of an 'and'? I didn't know that.

Posted: Fri Oct 10, 2003 5:04 am
by twigletmac
Paddy wrote:Can you use an '&' instead of an 'and'? I didn't know that.
Just tested it and you can't.

Mac

Posted: Fri Oct 10, 2003 4:22 pm
by Cruzado_Mainfrm
it's not &, it's &&, i think he missed that