Peoblem with JOIN and AND / OR

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
me!
Forum Contributor
Posts: 133
Joined: Sat Nov 04, 2006 8:45 pm

Peoblem with JOIN and AND / OR

Post by me! »

This is what I have:

Code: Select all

$query = "SELECT id, generator_id, city, last_name, first_name, business_name, last_service, customers.type AS customer_type FROM customers, generators
            WHERE customers.id = generators.customer_id
            AND generators.track_service='yes'
            OR generators.flag='on'
            ORDER BY generators.flag, generators.last_service, customers.last_name, customers.business_name";
It is part of a service system for generator maintenance, what I want is a list of all costumes that we are tracking service for and only the ones I care about.
So I put in "AND generators.track_service='yes'", but if there is a problem win any generator we need it to also show up on the top of the list, so I did "OR generators.flag='on'"

The problem is whenever there is a generator with the "flag" set to on the quarry shows all records in the BD?
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Peoblem with JOIN and AND / OR

Post by Jonah Bron »

I'm not entirely sure I understand your question, but try this:

Code: Select all

$query = "SELECT id, generator_id, city, last_name, first_name, business_name, last_service, customers.type AS customer_type FROM customers, generators
            WHERE customers.id = generators.customer_id
            AND (generators.track_service='yes'
            OR generators.flag='on')
            ORDER BY generators.flag, generators.last_service, customers.last_name, customers.business_name";
If that doesn't work try

Code: Select all

$query = "SELECT id, generator_id, city, last_name, first_name, business_name, last_service, customers.type AS customer_type FROM customers, generators
            WHERE (customers.id = generators.customer_id
            AND generators.track_service='yes')
            OR generators.flag='on'
            ORDER BY generators.flag, generators.last_service, customers.last_name, customers.business_name";
Post Reply