[SOLVED] filtering data

[fivestringsurf]

Hello,

 

I wrote an emailing script (in php )that builds a list of customer ids in a table called "emails" Then whenever an email is sent the customer is "checked off" in the emails table by changing "status=1"  Right now I have it so that when a new email job is created only a the list of customers in that group are added to the emails table.  It would be much simpler to dump all customers into the "emails" table and just set their status's differently, but writting 1000s of extra rows if I don't have to seems like a waste.

 

the tables are: customers, emailJobs, emails

 

The problem is in my "add/remove" a customer's email page...  I'm not getting the results i need to populate a list of customers that i can select to add to the email job list.

 

The customers on this "add" list should only be the customers from my master customers list and if they are not on the emal job list already

 

here's my query

it returns doubles for customers that are listed twice in the "emails" list

$makeList= mysql_query("SELECT * FROM customers
				   LEFT JOIN emails
				   USING (c_id)
				   WHERE emails.c_id is null AND 
				   emails.j_id != '$j_id' OR 
				   emails.c_id is not null AND 
				   emails.j_id != '$j_id' OR
				   emails.status ='3' AND
				   customers.status = '1' OR
				   emails.j_id = '$j_id' AND
				   emails.status ='3' AND
				   customers.status = 'inc'
				   ");

 

 

I'm looking to return only customer's email addresses that are not on the "emails" list under the current job "j_id"

btw emails.status ='3' simply means they were on the list but were taken off

I'm using php 5 and mysql 5

Thank you

 

 

[fenway]

I think you're mixing ORs and ANDs without parentheses...

[fivestringsurf]

parentheses huh? funny i've worked with dozens of db's but never ran into them or even thought to try them.  Do they work like all other math functions and serve as a way to group precedence?  I searched the web and all of my books but didn't find too many working examples of parentheses in action (for mysql). 

[fenway]

Yup -- no different that anything else.  Make sure you isolate each condition.

[fivestringsurf]

I nailed it!  I used subqueries and parenthesis!

here's what worked:

mysql_query("SELECT distinct c_id, email, firstName, lastName FROM customers WHERE
   c_id not in (select c_id from emails where j_id='$j_idMaster') OR
   c_id in (select c_id from emails where j_id='$j_idMaster' AND status='3')
    ");

 

Thanks fenway

 

I'm having a hard time finding sytax examples for mysql_query() used from php.  the mysql reference is like a dictionary of terms and my book and many sites seem to give examples but under a differnent syntax (command line?) -not even sure what the command line is, but i hear the term??? for example a book i have displays a similar query like this:

 

select distinct (c_id, email) from customers where...//you get the idea

 

those parenthesis don't work

Don't most folks just run querys via php?

[kickstart]

Hi

 

The SQL syntax doesn't change it is within a mysql_query call or not. You can have mysql running and query it from a command line interface. However suspect most people these days use some kind of graphical front end for playing with mysql, an example being PhpMyAdmin which is a browser based front end for MySQL written in php.

 

A way to do your above select withou using subselects:-

 

SELECT DISTINCT a.c_id, a.email, a.firstName, a.lastName

FROM customers a

LEFT OUTER JOIN emails b ON a.c_id = b.c_id AND j_id='$j_idMaster'  AND status!='3'

LEFT OUTER JOIN emails c ON a.c_id = c.c_id AND j_id='$j_idMaster'  AND status='3'

WHERE b.c_id IS NULL

OR c.c_id IS NOT NULL

 

All the best

 

Keith

[fivestringsurf]

love your tag line ..."10 types of people"  -fantastic

 

Your code looks longer and more confusing (only cause I have practically 0 experience with left joins)  I presume it's faster though....I think subqueries are slower because they fire several separate times?

 

Thank you so much.

[fenway]

Yes... avoid correlated subqueries if possible; but not all of them are evil.