Adding a new OR to my sql statement

[dlcmpls]

Here's my current sql statement which gives me almost what I want:

 

mysql_select_db ("$DBName");

/********************************************

Write the query, call it, and find the number of fields

/********************************************/

$export = mysql_query("

(SELECT

subscribers.bfname, 

subscribers.bname, 

subscribers.baddr1 as addr1, 

subscribers.baddr2 as addr2, 

subscribers.bcity as city, 

subscribers.bstate as state, 

subscribers.bzip as zip,

subscribers.email as email,

subscribers.user_id,

subscribers.password,

subscriptions.expiration_date,

subscriptions.type

FROM subscribers, subscriptions

WHERE saddr1 = ''

AND subscriptions.user_id = subscribers.user_id

AND (subscriptions.recieve_by = 'mail' OR subscriptions.recieve_by = 'both')

)

 

UNION

 

(SELECT

subscribers.bfname, 

subscribers.bname, 

subscribers.saddr1 as addr1, 

subscribers.saddr2 as addr2, 

subscribers.scity as city, 

subscribers.sstate as state, 

subscribers.szip as zip,

subscribers.email as email,

subscribers.user_id,

subscribers.password,

subscriptions.expiration_date,

subscriptions.type

FROM subscribers, subscriptions

WHERE saddr1 <> ''

AND subscriptions.user_id = subscribers.user_id

AND (subscriptions.recieve_by = 'mail' OR subscriptions.recieve_by = 'both')

)

 

ORDER BY bname, bfname, user_id, expiration_date DESC

 

");

 

See the 2nd AND?  That gives me 2 bits of data about how a user receives their newsletter.  But there's a third option which is "e-mail."  I need the query to include anyone who has a recieve_by set to "e-mail."

(yes, I know "recieve" is misspelled!)

 

If I simply extend both AND's like this:

 

AND (subscriptions.recieve_by = 'mail' OR subscriptions.recieve_by = 'both' OR subscriptions.recieve_by = 'e-mail' )

 

I get a 500 internal server error.

 

What am I doing wrong?

 

 

 

 

[Zane]

I'm not exactly sure what the problem is..

 

Though since you claim it works without the OR "e-mail" part just fine, my best guess is that the query is breaking for some reason on the dash in email.

 

What's your collation on your table?.. and what is the e-mail column's DATATYPE.

[Mchl]

How about

AND subscriptions.recieve_by IN('mail','both','e-mail')

?

 

Try using mysql_error to find out what error message is being returned from MySQL.

[dlcmpls]

I tried this:

 

AND subscriptions.recieve_by IN('mail','both','e-mail')

 

but that returned 0 records.

 

Here's more of my query, including the mysql_error which doesn't return an error:

 

$dbh=mysql_connect ("$Host", "$User", "$Password") or die ('I cannot connect to the database because: ' . mysql_error());

mysql_select_db ("$DBName");

/********************************************

Write the query, call it, and find the number of fields

/********************************************/

$export = mysql_query("

(SELECT

subscribers.bfname, 

subscribers.bname, 

subscribers.baddr1 as addr1, 

subscribers.baddr2 as addr2, 

subscribers.bcity as city, 

subscribers.bstate as state, 

subscribers.bzip as zip,

subscribers.email as email,

subscribers.user_id,

subscribers.password,

subscriptions.expiration_date,

subscriptions.type

FROM subscribers, subscriptions

WHERE saddr1 = ''

AND subscriptions.user_id = subscribers.user_id

AND (subscriptions.recieve_by = 'mail' OR subscriptions.recieve_by = 'both')

)

 

UNION

 

(SELECT

subscribers.bfname, 

subscribers.bname, 

subscribers.saddr1 as addr1, 

subscribers.saddr2 as addr2, 

subscribers.scity as city, 

subscribers.sstate as state, 

subscribers.szip as zip,

subscribers.email as email,

subscribers.user_id,

subscribers.password,

subscriptions.expiration_date,

subscriptions.type

FROM subscribers, subscriptions

WHERE saddr1 <> ''

AND subscriptions.user_id = subscribers.user_id

AND (subscriptions.recieve_by = 'mail' OR subscriptions.recieve_by = 'both')

)

 

ORDER BY bname, bfname, user_id, expiration_date DESC

 

");

 

the "recieve_by" column in my table is varchar(10)

 

I know that the dash in "e-mail" isn't a problem because I can change the AND to:

 

AND (subscriptions.recieve_by = 'e-mail' OR subscriptions.recieve_by = 'both')

 

and I don't get an error.

 

Could it be a Union problem?  I don't have a recieve_by column in both tables.  Would that cause a problem?

 

 

[Mchl]

AND (subscriptions.recieve_by = 'mail' OR subscriptions.recieve_by = 'both' OR subscriptions.recieve_by = 'e-mail' )

and

AND subscriptions.recieve_by IN( 'mail', 'both' ,'e-mail' )

are exactly same thing for MySQL (latter is being rewritten to former). It's a bit strange you get fewer result with it...