[RESOLVED] Problems with a WHERE statement

MichaelHe

I have a MySQL line where I write:

mysql_query("select * from Repertoire WHERE Decade>='1960' AND Decade<'1970' ORDER BY Artist");

But how do I get the WHERE statement to be as a $statement.

I have tried with

$decade="Decade>='1960' AND Decade<'1970'";
mysql_query("select * from Repertoire WHERE $decade ORDER BY Artist")

But that won't work.
I have some different WHERE statements, so It would be nice if I could make them in a $statement and then use the same mysql_query only by changing the $decade=

devinemke

MichaelHe wrote:
But that won't work.

what do you mean by that? what is the query returning. what does [man]mysql_error[/man] have to say about the query?

MichaelHe

It was a little hard to explane :o)
But let me try again.

I have a repertoire list where I have a field in my table called Decade.
In this field I have written 1960 1970 1980 and so on, on all the songs.

I now have a mysql_query like this.

$repertoire=mysql_query("select * from Repertoire ORDER BY Artist");
while ($row = mysql_fetch_array($repertoire)){... and so on.

This works fine, and the complete repertoire is listed.

But know I want to filter the list by decade.
I have 5 different filter.
Decade=<'70'
Decade=>'70' AND Decade <'80'
Decade=>'80' AND Decade <'90'
Decade=>'90'
And then no filter, if you want them all shows.

So I could write 5 different mysql_query lines like this.

IF ($decade=='1960'){$repertoire=mysql_query("select * from Repertoire WHERE Decade=<'70' ORDER BY Artist");}
IF ($decade=='1970'){$repertoire=mysql_query("select * from Repertoire WHERE Decade=>'70' AND Decade <'80' ORDER BY Artist");}
....And so on

But I thought that it might be possible to do something like this:

IF ($filter=='1960'){$decade="Decade=<70";};
IF ($filter=='1970'){$decade="Decade=>70 AND Decade <80";};
...And so on.

$repertoire=mysql_query("select * from Repertoire WHERE $decade ORDER BY Artist");

So the WHERE statement in the mysql_query is what is it in the $decade.
But I can't get it to work.

Hope you understand my question this time :o)

devinemke

the operator to say "greater than or equal to" is ">=" not "=>" as you have done in your query. again, [man]mysql_error[/man] would tell you precisely where the error is.

MichaelHe

Ofcause.. That was a typing error...
I have written >= in the script

There is no error, it just doesn't get anything from the database.

devinemke

$filter = '1970'; // just for testing

if ($filter == '1960') {$decade = "Decade BETWEEN '60' AND '69'";}
if ($filter == '1970') {$decade = "Decade BETWEEN '70' AND '79'";}
if ($filter == '1980') {$decade = "Decade BETWEEN '80' AND '89'";}

$sql = "SELECT * FROM Repertoire WHERE $decade ORDER BY Artist"; 
echo $sql;

incidentally, what field type is your Decade field? an integer? a string? does each record already have a separate DATE or DATETIME column. if so then it is unecessary to store an additional field for the decade. this can easily be derived from the DATE column.

MichaelHe

It look simelar to what I have tried..
I can't test it right now, since my webserver is suddently down. :o(

I have no date field.
The Decade field is an int(4) with only 1950, 1960, 1970, 1980, 1990 or 2000 written in it.

I will try the codes as soon as the server is back :o)

Thanks a lot.

devinemke

the code could be simplified even further like so:

$filter = '1920'; // just for testing 
$filter_plus9 = $filter + 9;

$decade = 'Decade BETWEEN ' . $filter . ' AND ' . $filter_plus9;
$sql = "SELECT * FROM Repertoire WHERE $decade ORDER BY Artist"; 

echo $sql;

this way you don't have hard code a bunch of specific decades.

MichaelHe

Now my server is up again, and the script is working.

Thank a lot for you help :o)

bradgrafelman

Don't forget to mark the thread resolved.

MichaelHe

Where do I do that ??