p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

beginning_php thread: DATE_ADD


Message #1 by "Ken Goff" <macessen@a...> on Sat, 22 Feb 2003 20:04:01
I have a variable ($neweventdate) that represents a date in yyyy-m-d format
which I can use successfully within a query. I want to limit this query by
using something like DATE_ADD but I'm getting a PHP error when I try to
assign a value to the second variable ($datelimit).

- - - - - - -
$neweventdate = "$y-$m-$d";
$datelimit = DATE_ADD("$y-$m-$d",INTERVAL 14 DAY);
	
$result = mysql_query("SELECT EventId, DATE_FORMAT(EventDate, '%c-%e-%Y')
as FormattedEventDate, EventDate, Event, EventOrder FROM EventList WHERE
EventDate >= '$neweventdate' AND EventDate <= '$datelimit' ORDER BY
'EventDate','EventOrder'",$link_id);
- - - - - - -

The $datelimit line is tagged as having a "parse error" and I've not been
able to discover the correct syntax for this to work.

The following works in phpMyAdmin but any similar approach to assigning a
value to $datelimit produces an error within my php code:

SELECT  *  FROM `EventList` WHERE EventDate >= NOW() AND EventDate <
DATE_ADD(NOW() , INTERVAL 14 DAY);

This also works within phpMyAdmin:

SELECT  *  FROM `EventList` WHERE EventDate >= "2003-02-22" AND EventDate
<= DATE_ADD("2003-02-22", INTERVAL 14 DAY);

How can I properly format this second limiting variable?
Message #2 by "Pedro Graca" <bzzzt@f...> on Sat, 22 Feb 2003 20:29:48 +0000
On Sat, 22 Feb 2003 20:04:01, "Ken Goff" <macessen@a...>
said:
> $neweventdate = "$y-$m-$d";
> $datelimit = DATE_ADD("$y-$m-$d",INTERVAL 14 DAY);
[...]
> The $datelimit line is tagged as having a "parse error" and I've not been
> able to discover the correct syntax for this to work.

DATE_ADD is a MySQL function; not a PHP function

try this:
  $newdate = mktime(0, 0, 0, $m, $d, $y);
  $datelimit = $newdate + (14 * 24 * 3600); // 14 days (a day is 24
  hours)
                                            // (one hour is 3600 seconds)

Hope this helps
-- 
                                            ()    ribbon campaign     ()
                                            /\   against HTML mail    /\
Message #3 by "Ken Goff" <macessen@a...> on Sat, 22 Feb 2003 23:24:22
> On Sat, 22 Feb 2003 20:04:01, "Ken Goff" <macessen@a...>
said:
> $neweventdate = "$y-$m-$d";
> $datelimit = DATE_ADD("$y-$m-$d",INTERVAL 14 DAY);
[...]
> The $datelimit line is tagged as having a "parse error" and I've not been
> able to discover the correct syntax for this to work.

DATE_ADD is a MySQL function; not a PHP function

try this:
  $newdate = mktime(0, 0, 0, $m, $d, $y);
  $datelimit = $newdate + (14 * 24 * 3600); // 14 days (a day is 24
  hours)
                                            // (one hour is 3600 seconds)


Thanks. I needed to format $datelimit further in order to work. It was not
in the format needed for a query on the date field "EventDate".

$neweventdate = "$y-$m-$d";
$newdate = mktime(0, 0, 0, $m, $d, $y) + (30 * 24 * 3600);
$datelimit = date("Y-m-d",$newdate);

Then my query works.

$result = mysql_query("SELECT EventId, DATE_FORMAT(EventDate, '%c-%e-%Y')
as FormattedEventDate, EventDate, Event, EventOrder FROM EventList WHERE
EventDate >= '$neweventdate' AND EventDate <= '$datelimit' ORDER BY
'EventDate','EventOrder'",$link_id);
Message #4 by "Pedro Graca" <bzzzt@f...> on Sun, 23 Feb 2003 00:02:16 +0000
On Sat, 22 Feb 2003 23:24:22, Ken Goff said:
[...]
> Thanks. I needed to format $datelimit further in order to work. It was
> not in the format needed for a query on the date field "EventDate".
> 
> $neweventdate = "$y-$m-$d";
> $newdate = mktime(0, 0, 0, $m, $d, $y) + (30 * 24 * 3600);
> $datelimit = date("Y-m-d",$newdate);
> 
> Then my query works.
> 
> $result = mysql_query("SELECT EventId, DATE_FORMAT(EventDate, '%c-%e-%Y')
> as FormattedEventDate, EventDate, Event, EventOrder FROM EventList WHERE
> EventDate >= '$neweventdate' AND EventDate <= '$datelimit' ORDER BY
> 'EventDate','EventOrder'",$link_id);

Glad you have it working :)

and I'm sorry for not considering your full message and fail to point you
the right direction for the SQL statement
-- 
                                            ()    ribbon campaign     ()
                                            /\   against HTML mail    /\
Message #5 by "Nikolai Devereaux" <yomama@u...> on Mon, 24 Feb 2003 11:54:37 -0800
I know the problem's already solved, but you could also choose to have MySQL
perform the function.

Just set your DATE_ADD variable as a string containing the function text
instead of the value.

For example:


$neweventdate = "$y-$m-$d";
$datelimit = "DATE_ADD(\"$neweventdate\", INTERVAL 14 DAY)";

$query = "SELECT EventId,
  DATE_FORMAT(EventDate, '%c-%e-%Y') as FormattedEventDate,
  EventDate, Event, EventOrder FROM EventList

WHERE EventDate >= '$neweventdate'
  AND EventDate <= $datelimit

ORDER BY 'EventDate','EventOrder'";

$result = mysql_query($query, $link_id);



Take care,

Nik


  Return to Index