SELECT

[erikjan]

Please can someone help? Thank you so much in advance!

I want to select from my database artists in the category "art" whose work was made in between 1960 and 1970 :

$sql="SELECT * FROM objects WHERE category='art' AND object_date LIKE '196%';

So far, so good.

When a work has been sold, it must not be on the website anymore, but has to remain in the database.
So I made a field "date_sold" in the database, which remains empty when the work has not been sold yet;
when it is sold, the selling date has to be filled in.

$sql="SELECT * FROM objects WHERE category='art' AND object_date LIKE '196% AND object_date_sold='' ";

So far, so good.

Now, some of the objects that have been sold, have to remain on the website for marketing reasons.

So I added a field in the database "object_stay_on_site", which is default 0, but gets the value 1 if
I want the item to remain on the website.


$sql="SELECT * FROM objects WHERE category='art'  AND object_date LIKE '196%  AND object_date_sold=' ' [u]OR[/u] object_stay_on_site='1' ORDER BY artist_surname ASC";

The [u]OR[/u] does not work because I will get also objects which are for instance made in 1920.

Can someone help me?

[projectshifter]

You're running into problems of your and/or statement.  It evaluates left to right, so put your two OR parts inside one parenthese-ed set and you'll be good :)

[Psycho]

Conditions are read from left to right. The way you have it written it is interpreted like this:

(category='art'  AND object_date LIKE '196%  AND object_date_sold=' ') OR object_stay_on_site='1'

So, if all three of the first conditions are true OR if the last condition is true, then the record is pulled. You just need to use parens to group your conditions accordingly:

category='art'  AND object_date LIKE '196%  AND (object_date_sold=' ' OR object_stay_on_site='1')

[erikjan]

Thanks!!

[keeB]

This is also a good time to review what a relational database is like, to make problems like this so much easier without 'complex' query's. I would have created a status table, myself, to control all of this behavior. It would have been much easier to manage, because I then wouldn't have to change my script too much if I needed to add a new condition like "object_open_but_reserved"