SQL Query with Date Functions Assistance Needed 1

[Zych]

Hello All,

I am trying to run a SQL query that is based on a date. I am not sure how to do this. Here is the basic query that works:

$result=@mysql_query("SELECT * FROM mytable WHERE thedate IS NOT NULL ORDER BY thedate");

Now what I want to add is a AND part that says the thedate must be greater than today. How would I go about doing this? In other words I do not want dates that have already past.

Is there a way to limit this to just one record?

Thanks,

Zych

 

[sleipnir214]

You would change your WHERE clause to read something like:

WHERE thedate IS NOT NULL AND date(thedate) > date(now())



You would limit this to a single record through the use of a LIMIT clause which must appear at the end of your query:

LIMIT 1


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Zych]

Thank you! A star for you! Worked great.

 

[Zych]

I have another problem now. I moved this to a Linux hosting solution and it no longer works. Here is the query:

SELECT * FROM bni WHERE ten IS NOT NULL AND date(ten) > date(now()) ORDER BY ten LIMIT 1

I know it is in the AND date(ten) > date(now()) part since I removed it then it worked. What is wrong with this that a Linux box has problems with it?

Thx,

Zych

 

[DRJ478]

It no longer works means:
a) It produces an error.
b) No error but no rows are returned.

I bet it's b.
Echo out your SQL statement to see that the expected variables are actually there.
Also, I would warn against using the @ operator, not having any chance to know if the mysql command threw an error or not.
The @ operator only hides the error, doesn't remedy.
I would attach error checking like this:

$result = mysql_query($SQL) OR die("Query failed: ".mysql_error());

 

[Zych]

Thanks. Here is the returned error:

Query failed: You have an error in your SQL syntax near '(ten) > date(now()) ORDER BY ten LIMIT 1' at line 1

I verified that ten is setup as a DATE field. Do I need to capitlize the funtion date or something?

 

[DRJ478]

If `ten` is a date field the function date appears superfluous.
Is there a difference in the version of the MySQL backend server? Is on 3.23.xx and the other one 4.0.x or 4.1.x ?

 

[Zych]

Is there a way to find out what version it is? The one on my system is the most current because I just downloaded it 5 days ago and installed it. I did try and capitalize date the following ways: Date and DATE which had not affect. I also put in the query like the following to see if it would generate any errors:

SELECT * FROM bni WHERE ten IS NOT NULL AND date(now()) > date(now()) ORDER BY ten LIMIT 1

It came back with:

Query failed: You have an error in your SQL syntax near '(now()) > date(now()) ORDER BY ten LIMIT 1' at line 1

Therefore I do not think it has anything to do with the field ten.

Any ideas?

 

[DRJ478]

A query "SHOW VARIABLES" should dump out a bunch of info that contains the version:

# wuick and dirty with not too much error catching
$SQL = "SHOW VARIABLES";
$result = mysql_query($SQL) OR die(mysql_error());
$row = mysql_fetch_assoc($result);
print_r($row);

 

[Zych]

That code only returns this:
Array ( [Variable_name] => back_log [Value] => 50 )

I ran the command "echo mysql_get_server_info();" and got this back:

3.23.58

Does the date commands work with this version?

 

[Zych]

I pulled this off of the mysql site:

DATE(expr)

Extracts the date part of the date or datetime expression expr.



mysql> SELECT DATE('2003-12-31 01:02:03');
-> '2003-12-31'


DATE() is available as of MySQL 4.1.1.

Since the host I am using is using version 3.23.58 of MySQL Date() does not work. Since ten is of type DATE I found out I really do not even need this funtion so I took it out. It now reads:

SELECT * FROM bni WHERE ten IS NOT NULL AND ten > now() ORDER BY ten LIMIT 1

This works fine now. Thanks for all of the suggestions.

- Chris