[RESOLVED] Getting one record based on date

[hparsons]

We're using Calendarix, a PHP based calendar. It does not use PHP's DATE() function to store the date of future events, but instead uses 3 fields that display the date of the event, a field for month, day, and year (those are the filed names as well).

I want to grab the first record whose month day and year are >= today's date.

Any suggestions?

[NogDog]

list($year, $month, $day) = explode('-', date('Y-n-j'));
$sql = "SELECT * FROM `table` WHERE `year` = $year AND `month` = $month AND `day` = $day LIMIT 1";

[hparsons]

Thanks for the amazingly quick response, but I'm new to PHP. Wouldn't that only return an event that is on the same day as today's date?

I'm looking for this to look for the next event even if there's not one on today's date.

[NogDog]

Sorry, I misread that comparison operator.

SELECT *, STR_TO_DATE(CONCAT_WS('-', `year`, `month`, `day`), '%y-%c-%e') AS `full_date`
  FROM `table_name`
  WHERE  `full_date` >= CURDATE() 
  ORDER BY `full_date` ASC
  LIMIT 1;

[hparsons]

Again, I'm new, so pardon my inexperience please.

I don't understand the variables in the following:

STR_TO_DATE(CONCAT_WS('-', year, month, day), '%y-%c-%e')

From what I've read, I THINK the %c and %e should have been %m and %d

Also, in my case, the calendar program stores the year as 2007, so I believe it should be:

STR_TO_DATE(CONCAT_WS('-', year, month, day), '%Y-%m-%d')

Is this correct?

Finally, what is the significance of the "," in this:

SELECT *,STR_TO_DATE(

Thanks again for your help. Belive it or not, I'm getting there.

[hparsons]

I should have mentioned this previously, but the calendar program stores the day and month what I consider improperly.

For Sept 10, 2007, the fields would be:
year 2007
month 9
day 10

For October 1, 2007, the fields would be:
year 2006
month 10
day 1

So, it appears to me, that I can't simply concatate the variables, as the exampls would come out as
2007910
2007101

which would make the Sept date appear to be > the Oct date.

How would this be overcome?

[NogDog]

Yes, capital "Y" is correct, typo on my part. The %c and %e should work, as they are the month and day numbers without leading zeros. The CONCAT_WS() function is putting a hyphen between the column values, So that year 2006, month 10, and day 1 would be concatenated into "2006-10-1", which matches the scheme of the format string '%Y-%c-%e'.

[hparsons]

I think I'm getting closer, but still not there. First of all, the CURRDATE() command doesn't work for me. I suspect that it's because Network Solutions is running an older version of PHP (version 4.4.7)

I substituted the DATE("Y-m-d") command

To work through this, and understand it, I put it in a tutorial code that was working for me. That code is as follows:

<?php
// command to open the configuration for the calendar removed
// command to connect to the database removed
$query = "SELECT *, STR_TO_DATE(CONCAT_WS('-', year, month, day), '%Y-%c-%e') AS full_date FROM calendarix_events";
$result = mysql_query($query);
$row = mysql_fetch_array( $result );
$row = mysql_fetch_assoc($result);
echo "Title :{$row['title']} <br/>" .
"Year : {$row['year']} <br/>" .
"Month : {$row['month']} <br/>" .
"Day : {$row['day']} <br/>" .
"Date : {$row['full_date']} <br/>" .
"Today : " . DATE('Y-m-d') ." <br>";
" <br/>" ;
mysql_free_result($result);
// command to close the database removed
?>

and that gives me the results I was expecting (you can see it at http://www.grapevinesailingclub.com/_php/mytest_1.php)

However, if I add the command
WHERE full_date >= DATE('Y-m-d') ORDER BY full_date ASC LIMIT 1
to the end of line 3, so it all reads:

<?php
// command to open the configuration for the calendar removed
// command to connect to the database removed
$query = "SELECT *, STR_TO_DATE(CONCAT_WS('-', year, month, day), '%Y-%c-%e') AS full_date FROM calendarix_events WHERE full_date >= DATE('Y-m-d') ORDER BY full_date ASC LIMIT 1";
$result = mysql_query($query);
$row = mysql_fetch_array( $result );
$row = mysql_fetch_assoc($result);
echo "Title :{$row['title']} <br/>" .
"Year : {$row['year']} <br/>" .
"Month : {$row['month']} <br/>" .
"Day : {$row['day']} <br/>" .
"Date : {$row['full_date']} <br/>" .
"Today : " . DATE('Y-m-d') ." <br>";
" <br/>" ;
mysql_free_result($result);
// command to close the database removed
?>

It all breaks. You can see the result at http://www.grapevinesailingclub.com/_php/mytest_2.php

I suspect because I'm trying to reuse what I did before, that I'm misusing the mysql_fetch_assoc command.

Can you guide me more from here?

Thanks in advance.

[bradgrafelman]

Use [man]mysql_error/man when debugging SQL queries, that way you can paste the error message to us.

Then again, the problem is obvious: the DATE() command won't return today's date... it will "extract the date part of a date or datetime expression. Instead, you should use CURDATE() (not CURRDATE()). If CURDATE() doesn't work, try CURRENT_DATE() instead.

[hparsons]

bradgrafelman wrote:

Use [man]mysql_error/man when debugging SQL queries, that way you can paste the error message to us.

Then again, the problem is obvious: the DATE() command won't return today's date... it will "extract the date part of a date or datetime expression. Instead, you should use CURDATE() (not CURRDATE()). If CURDATE() doesn't work, try CURRENT_DATE() instead.

Again, I'm new to PHP, but CURDATE() doesn't work for me, nor does CURRENT_DATE(). They return the following errors:

Fatal error: Call to undefined function: curdate() in /data/10/0/110/56/762382/user/779720/htdocs/_php/mytest_1.php on line 13

Fatal error: Call to undefined function: current_date() in /data/10/0/110/56/762382/user/779720/htdocs/_php/mytest_1.php on line 13

However, the DATE('Y-m-d') command returns 2007-07-30.
From http://www.php.net/date:
Returns a string formatted according to the given format string using the given integer timestamp or the current time if no timestamp is given. (my emphasis added)

I just did a little more research. CUR_DATE and CURRENT_DATE are MySQL 5.0 commands. The version running on the server I'm using (it's Network Solutions' server, so I can't change it) is 4.1.21.

However, the DATE(Y-m-d) command will return today's date in the format of YYYY-MM-DD (2007-07-30 for today's date).

[bradgrafelman]

hparsons wrote:

CUR_DATE and CURRENT_DATE are MySQL 5.0 commands.

Yes.. that's why they're in the query string. You shouldn't be getting PHP error messages aout undefined functions, because you shouldn't be using PHP functions.

Furthermore, the current_date command has been around since MySQL v3 as far as I can tell.

[NogDog]

$query = "SELECT *, STR_TO_DATE(CONCAT_WS('-', year, month, day), '%Y-%c-%e')
AS full_date FROM calendarix_events WHERE full_date >= [color=red]CURDATE()[/color]
ORDER BY full_date ASC LIMIT 1";

[hparsons]

After playing around with this, I did finally get this to work. The instructions above were VERY helpful, but I still kept having problems. I got it to work, but there may be a simpler method.

Here's what I did. As I narrowed things down, my system had problems with this line:

$query = "SELECT *, STR_TO_DATE(CONCAT_WS('-', year, month, day), '%Y-%c-%e') AS full_date FROM calendarix_events WHERE full_date >= CURDATE() AND cat = $CAT) ORDER BY year, month, day LIMIT 1";

My system wouldn't accept the full_date variable shown above. When I changed it to the following:

$query = "SELECT *, STR_TO_DATE(CONCAT_WS('-', year, month, day), '%Y-%c-%e') AS full_date FROM calendarix_events WHERE (STR_TO_DATE(CONCAT_WS('-', year, month, day), '%Y-%c-%e') >= CURDATE() AND cat = $CAT) ORDER BY year, month, day LIMIT 1";

everything worked fine. As I said, if there's a way to clean this up, I'm all for it, but it's working and I'm grateful for the help.