MySQL Query Problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
theoph
Forum Commoner
Posts: 47
Joined: Wed Jul 30, 2003 5:26 pm
Location: Lexington, KY USA

MySQL Query Problem

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
theoph
Forum Commoner
Posts: 47
Joined: Wed Jul 30, 2003 5:26 pm
Location: Lexington, KY USA

Post by theoph »

Puting the () around the <conditions-to-restrict-rows-returned> arguments fixed the problem. :wink:

Thanks
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Post 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
Paddy
Forum Contributor
Posts: 244
Joined: Wed Jun 11, 2003 8:16 pm
Location: Hobart, Tas, Aussie
Contact:

Post by Paddy »

Can you use an '&' instead of an 'and'? I didn't know that.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
Cruzado_Mainfrm
Forum Contributor
Posts: 346
Joined: Sun Jun 15, 2003 11:22 pm
Location: Miami, FL

Post by Cruzado_Mainfrm »

it's not &, it's &&, i think he missed that
Post Reply