[RESOLVED] SELECT WHERE statement

XD2005

I am having difficulties getting my code to work how I want.

To set the stage, I have a weather database and I am trying to pull in current values (got that working fine), then minimum and maximum values for the day. This is where I cannot seem to get it to work (no errors, just no data either).

Below is the code I am working with.

$today_date = date('Y-m-d')

$result14 = mysql_query('SELECT outdoortempF FROM ongoing WHERE date="$today_date" ORDER BY outdoortempF DESC');
$maxtemp = mysql_fetch_row($result14);
$maxtemp = $maxtemp[0];

Basically, I want the code to look at the values in the "outdoortempF" field of the database, for all the rows where the "date" field matches the current date. I then want the temperature values to be in descending order, and finally I want it to establish the maxtemp variable as today's highest value.

Suggestions?

Thanks.

bradgrafelman

Is the date field in your database a DATE column or a DATETIME column? Also, how do you know MySQL isn't returning an error message? Normally, to debug a SQL query, I do something like:

$query = 'SELECT ...';
$exec = mysql_query($query) or die('MySQL error: ' . mysql_error() . '<hr>Query: ' . $query);

Also, you could try surrounding date with backticks ( ` ) so that MySQL knows you're specifying a column rather than the reserved word DATE. In addition, you don't need to use PHP to calculate the date - you can use MySQL's CURDATE() function for that.

XD2005

The date field in MySQL is a DATE data type, not DATETIME.

You're right about the error message thing. My apologies, I merely meant to say there were no visible, code-stopping errors displayed on the website.

I will see if I can do better at debugging the query.

Thanks.

XD2005

One thing, if I modify my code to the following:


$result14 = mysql_query('SELECT MAX(outdoortempF) FROM ongoing WHERE date="2007-12-09"');
$maxtemp = mysql_fetch_row($result14);
$maxtemp = $maxtemp[0];

In other words, if I hard code the date, it works, I just can't seem to get it to compare against a variable. If I echo my today_date variable, it shows as 2007-12-09, which is the same as I am hard coding.

Any thoughts on how to replace the hard coded date with the date variable?

Thanks.

bradgrafelman

Try echo'ing out the query with the variable - see if it looks right.

XD2005

Solution turned out to be:

$result14 = mysql_query('SELECT MAX(outdoortempF) FROM ongoing WHERE date="'.$datadate.'"');

spadmore

Most likely your $today_date is in an incompatible format for comp. with mysql date.... I see no other reason for there to be no data returned. Try printing out the query @ runtime then pasting that same query in a phpmyadmin sql window.....

-Shelon Padmore

spadmore

could be a date format prob in your php variable

-Shelon Padmore

XD2005

Shelon -

Thanks, I got it resolved. Solution shown in my last post.