subject:"RE\: \[PHP\-DB\] dates in MYSQL"

Re: [PHP-DB] Dates in MYSQL

2002-04-18 Thread DL Neil


Hi Alex,

You seem to be struggling with both MySQL and PHP at the same time! Must
be generating high frustration levels. Have you got yourself a
tutorial/book to work from?

I'll go with Mike's suggestion, although I'd go for a 'one stop shop' in
MySQL - as long as we get the required result it's right!

The latest problem is a mix-up of quotation marks - originally you
enclosed the query within single quotes and the SQL/PHP parameters in
double quotes. Now you have both the same (to accommodate the PHP
variable $todaysdate). Change the DATE_FORMAT parameter quotes to
singles.

Other pieces of (unsolicited) advice: make sure that you have error
checking and debugging in place FIRST.
Surrounding this one query I would have a debug print of the query
string. (1) to show me what has been constructed by PHP (syntax
checking), and (2) so that I can copy-paste it out of my browser session
and into a MySQL admin package or command-line query (SQL logic
checking).
Also add a call/calls to check that MySQL returned a valid result to
PHP, how many rows were found, etc. (best to direct you to the online
manual than to repeat that load of info).

Keep on trucking,
=dn


 Mike,

 I have just tried it again (that was the first way I tried to do the
query)
 and get the error

 Unknown column '$todaysdate' in 'where clause'


 --
 Alex Francis
 Cameron Design
 35, Drumillan Hill
 Greenock PA16 0XD

 Tel 01475 798106
 [EMAIL PROTECTED]
 http://www.camerondesign.co.uk

 This message is sent in confidence for the addressee only. It may
contain
 legally privileged information.
 Unauthorised recipients are requested to preserve this confidentiality
and
 to advise the sender
 immediately of any error in transmission.
 Mike [EMAIL PROTECTED] wrote in message
 001b01c1e658$6f483c00$[EMAIL PROTECTED]">news:001b01c1e658$6f483c00$[EMAIL PROTECTED]...
  Alex,
 
   enterdate text   NOT NULL
   eventdate text   NOT NULL
 
  First, I think I would change the above to a date or datetime or one
of
 the
  other date  time fields used by mysql instead of text fields.
 
  Second  I don't think you really need to use TO DAYS and now. This
is what
 I
  do.
 
  use php date function to get the current date,
 
  $todaysdate = date(Ymd);
 
  then do the query,
 
  $query = SELECT id, eventheading, DATE_FORMAT(eventdate, %D %M
%Y) as
  evdt FROM notices WHERE eventdate = $todaysdate ORDER BY
  eventdate;
 
 
 
 
  Mike
  - Original Message -
  From: Alex Francis [EMAIL PROTECTED]
  To: [EMAIL PROTECTED]
  Sent: Wednesday, April 17, 2002 5:26 PM
  Subject: Re: [PHP-DB] Dates in MYSQL
 
 
   Tried the following as suggested - Still no dates showing
  
   $query = ' SELECT id, eventheading, DATE_FORMAT(eventdate, %D
%M
 %Y)as
   evdt FROM notices WHERE TO_DAYS(eventdate) = TO_DAYS(now())
order by
   eventdate';
  
  
   $result=mysql_db_query($dbname, $query, $link);
  
   if (!$result) { echo( mysql_error()); }
   else
  
  
   while ($row = mysql_fetch_array($result))
  
  
   $entername = $row[entername];
   $eventdate = $row[evdt];
   $eventheading = $row[eventheading];
   $id = $row[id];
  
   Database table notices as follows
   id int  NULL autoincrement
   entername text   NOT NULL
   enterdate text   NOT NULL
   eventdate text   NOT NULL
   eventheading text   NOT NULL
   eventbody text   NOT NULL
  
  
  
  
   --
   Alex Francis
   Cameron Design
   35, Drumillan Hill
   Greenock PA16 0XD
  
   Tel 01475 798106
   [EMAIL PROTECTED]
   http://www.camerondesign.co.uk
  
   This message is sent in confidence for the addressee only. It may
 contain
   legally privileged information.
   Unauthorised recipients are requested to preserve this
confidentiality
 and
   to advise the sender
   immediately of any error in transmission.
   Dl Neil [EMAIL PROTECTED] wrote in message
   0b8401c1e62e$03072ad0$0600a8c0@jrbrown">news:0b8401c1e62e$03072ad0$0600a8c0@jrbrown...
Hi Alex,
   
 Got my select statement to work as follows:
 $query = ' SELECT * FROM notices WHERE TO_DAYS(eventdate) =
TO_DAYS(now())
 order by eventdate';
   
=well done!
However the TO-DAYS calls do seem a bit OTT.
Please post the schema for tbl:notices - specifically the
datatype for
eventdate.
   
 Now trying to get the date more user friendly and tried:

 $query = ' SELECT id, eventheading, DATE_FORMAT(eventdate,
%D %M
%Y)FROM
 notices WHERE TO_DAYS(eventdate) = TO_DAYS(now()) order by
eventdate';
...
 When I echo ($eventdate) I get nothing. Not even an error.
   
   
The problem is that
   
$eventdate = $row[eventdate];
   
(which doesn't need the  around the whole of the RHS (some
would put
them around eventdate), BTW)
doesn't tie up with:
   
DATE_FORMAT(eventdate, %D %M %Y)
   
Recommend you change them to:
   
DATE_FORMAT(eventdate, %D %M %Y) AS evdt
and
$eventdate = $row[evdt];

RE: [PHP-DB] dates in MYSQL

2002-04-17 Thread Ruprecht Helms



Hi Alex Francis,

 
 SELECT From the database WHERE date = todays date.
^ somethink like now() or date()

an other method you put the date in a variable that you get by the
datecommand and make

SELECT From table WHERE date = datevar (variable with current date)

Regards,
Ruprecht


--
E-Mail: Ruprecht Helms [EMAIL PROTECTED]
Date: 17-Apr-02
Time: 11:37:25

to be informed - http://www.rheyn.de -

This message was sent by XFMail
--

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Dates in MYSQL

2002-04-17 Thread DL Neil


Hi Alex,

 Got my select statement to work as follows:
 $query = ' SELECT * FROM notices WHERE TO_DAYS(eventdate) =
TO_DAYS(now())
 order by eventdate';

=well done!
However the TO-DAYS calls do seem a bit OTT.
Please post the schema for tbl:notices - specifically the datatype for
eventdate.

 Now trying to get the date more user friendly and tried:

 $query = ' SELECT id, eventheading, DATE_FORMAT(eventdate, %D %M
%Y)FROM
 notices WHERE TO_DAYS(eventdate) = TO_DAYS(now()) order by
eventdate';
...
 When I echo ($eventdate) I get nothing. Not even an error.


The problem is that

$eventdate = $row[eventdate];

(which doesn't need the  around the whole of the RHS (some would put
them around eventdate), BTW)
doesn't tie up with:

DATE_FORMAT(eventdate, %D %M %Y)

Recommend you change them to:

DATE_FORMAT(eventdate, %D %M %Y) AS evdt
and
$eventdate = $row[evdt];

See how the SQL and PHP tie together?
Now you need to reconsider the other $row[] assignments.

=Keep it coming!
=dn


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Dates in MYSQL

2002-04-17 Thread Alex Francis


Tried the following as suggested - Still no dates showing

$query = ' SELECT id, eventheading, DATE_FORMAT(eventdate, %D %M %Y)as
evdt FROM notices WHERE TO_DAYS(eventdate) = TO_DAYS(now()) order by
eventdate';


$result=mysql_db_query($dbname, $query, $link);

if (!$result) { echo( mysql_error()); }
else


while ($row = mysql_fetch_array($result))


$entername = $row[entername];
$eventdate = $row[evdt];
$eventheading = $row[eventheading];
$id = $row[id];

Database table notices as follows
id int  NULL autoincrement
entername text   NOT NULL
enterdate text   NOT NULL
eventdate text   NOT NULL
eventheading text   NOT NULL
eventbody text   NOT NULL




--
Alex Francis
Cameron Design
35, Drumillan Hill
Greenock PA16 0XD

Tel 01475 798106
[EMAIL PROTECTED]
http://www.camerondesign.co.uk

This message is sent in confidence for the addressee only. It may contain
legally privileged information.
Unauthorised recipients are requested to preserve this confidentiality and
to advise the sender
immediately of any error in transmission.
Dl Neil [EMAIL PROTECTED] wrote in message
0b8401c1e62e$03072ad0$0600a8c0@jrbrown">news:0b8401c1e62e$03072ad0$0600a8c0@jrbrown...
 Hi Alex,

  Got my select statement to work as follows:
  $query = ' SELECT * FROM notices WHERE TO_DAYS(eventdate) =
 TO_DAYS(now())
  order by eventdate';

 =well done!
 However the TO-DAYS calls do seem a bit OTT.
 Please post the schema for tbl:notices - specifically the datatype for
 eventdate.

  Now trying to get the date more user friendly and tried:
 
  $query = ' SELECT id, eventheading, DATE_FORMAT(eventdate, %D %M
 %Y)FROM
  notices WHERE TO_DAYS(eventdate) = TO_DAYS(now()) order by
 eventdate';
 ...
  When I echo ($eventdate) I get nothing. Not even an error.


 The problem is that

 $eventdate = $row[eventdate];

 (which doesn't need the  around the whole of the RHS (some would put
 them around eventdate), BTW)
 doesn't tie up with:

 DATE_FORMAT(eventdate, %D %M %Y)

 Recommend you change them to:

 DATE_FORMAT(eventdate, %D %M %Y) AS evdt
 and
 $eventdate = $row[evdt];

 See how the SQL and PHP tie together?
 Now you need to reconsider the other $row[] assignments.

 =Keep it coming!
 =dn




-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Dates in MYSQL

2002-04-17 Thread Mike


Alex,

 enterdate text   NOT NULL
 eventdate text   NOT NULL

First, I think I would change the above to a date or datetime or one of the
other date  time fields used by mysql instead of text fields.

Second  I don't think you really need to use TO DAYS and now. This is what I
do.

use php date function to get the current date,

$todaysdate = date(Ymd);

then do the query,

$query = SELECT id, eventheading, DATE_FORMAT(eventdate, %D %M %Y) as
evdt FROM notices WHERE eventdate = $todaysdate ORDER BY
eventdate;




Mike
- Original Message -
From: Alex Francis [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, April 17, 2002 5:26 PM
Subject: Re: [PHP-DB] Dates in MYSQL


 Tried the following as suggested - Still no dates showing

 $query = ' SELECT id, eventheading, DATE_FORMAT(eventdate, %D %M %Y)as
 evdt FROM notices WHERE TO_DAYS(eventdate) = TO_DAYS(now()) order by
 eventdate';


 $result=mysql_db_query($dbname, $query, $link);

 if (!$result) { echo( mysql_error()); }
 else


 while ($row = mysql_fetch_array($result))


 $entername = $row[entername];
 $eventdate = $row[evdt];
 $eventheading = $row[eventheading];
 $id = $row[id];

 Database table notices as follows
 id int  NULL autoincrement
 entername text   NOT NULL
 enterdate text   NOT NULL
 eventdate text   NOT NULL
 eventheading text   NOT NULL
 eventbody text   NOT NULL




 --
 Alex Francis
 Cameron Design
 35, Drumillan Hill
 Greenock PA16 0XD

 Tel 01475 798106
 [EMAIL PROTECTED]
 http://www.camerondesign.co.uk

 This message is sent in confidence for the addressee only. It may contain
 legally privileged information.
 Unauthorised recipients are requested to preserve this confidentiality and
 to advise the sender
 immediately of any error in transmission.
 Dl Neil [EMAIL PROTECTED] wrote in message
 0b8401c1e62e$03072ad0$0600a8c0@jrbrown">news:0b8401c1e62e$03072ad0$0600a8c0@jrbrown...
  Hi Alex,
 
   Got my select statement to work as follows:
   $query = ' SELECT * FROM notices WHERE TO_DAYS(eventdate) =
  TO_DAYS(now())
   order by eventdate';
 
  =well done!
  However the TO-DAYS calls do seem a bit OTT.
  Please post the schema for tbl:notices - specifically the datatype for
  eventdate.
 
   Now trying to get the date more user friendly and tried:
  
   $query = ' SELECT id, eventheading, DATE_FORMAT(eventdate, %D %M
  %Y)FROM
   notices WHERE TO_DAYS(eventdate) = TO_DAYS(now()) order by
  eventdate';
  ...
   When I echo ($eventdate) I get nothing. Not even an error.
 
 
  The problem is that
 
  $eventdate = $row[eventdate];
 
  (which doesn't need the  around the whole of the RHS (some would put
  them around eventdate), BTW)
  doesn't tie up with:
 
  DATE_FORMAT(eventdate, %D %M %Y)
 
  Recommend you change them to:
 
  DATE_FORMAT(eventdate, %D %M %Y) AS evdt
  and
  $eventdate = $row[evdt];
 
  See how the SQL and PHP tie together?
  Now you need to reconsider the other $row[] assignments.
 
  =Keep it coming!
  =dn
 



 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.330 / Virus Database: 184 - Release Date: 2/28/02


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Dates in MYSQL

2002-04-17 Thread Alex Francis


Mike,

I have just tried it again (that was the first way I tried to do the query)
and get the error

Unknown column '$todaysdate' in 'where clause'


--
Alex Francis
Cameron Design
35, Drumillan Hill
Greenock PA16 0XD

Tel 01475 798106
[EMAIL PROTECTED]
http://www.camerondesign.co.uk

This message is sent in confidence for the addressee only. It may contain
legally privileged information.
Unauthorised recipients are requested to preserve this confidentiality and
to advise the sender
immediately of any error in transmission.
Mike [EMAIL PROTECTED] wrote in message
001b01c1e658$6f483c00$[EMAIL PROTECTED]">news:001b01c1e658$6f483c00$[EMAIL PROTECTED]...
 Alex,

  enterdate text   NOT NULL
  eventdate text   NOT NULL

 First, I think I would change the above to a date or datetime or one of
the
 other date  time fields used by mysql instead of text fields.

 Second  I don't think you really need to use TO DAYS and now. This is what
I
 do.

 use php date function to get the current date,

 $todaysdate = date(Ymd);

 then do the query,

 $query = SELECT id, eventheading, DATE_FORMAT(eventdate, %D %M %Y) as
 evdt FROM notices WHERE eventdate = $todaysdate ORDER BY
 eventdate;




 Mike
 - Original Message -
 From: Alex Francis [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Wednesday, April 17, 2002 5:26 PM
 Subject: Re: [PHP-DB] Dates in MYSQL


  Tried the following as suggested - Still no dates showing
 
  $query = ' SELECT id, eventheading, DATE_FORMAT(eventdate, %D %M
%Y)as
  evdt FROM notices WHERE TO_DAYS(eventdate) = TO_DAYS(now()) order by
  eventdate';
 
 
  $result=mysql_db_query($dbname, $query, $link);
 
  if (!$result) { echo( mysql_error()); }
  else
 
 
  while ($row = mysql_fetch_array($result))
 
 
  $entername = $row[entername];
  $eventdate = $row[evdt];
  $eventheading = $row[eventheading];
  $id = $row[id];
 
  Database table notices as follows
  id int  NULL autoincrement
  entername text   NOT NULL
  enterdate text   NOT NULL
  eventdate text   NOT NULL
  eventheading text   NOT NULL
  eventbody text   NOT NULL
 
 
 
 
  --
  Alex Francis
  Cameron Design
  35, Drumillan Hill
  Greenock PA16 0XD
 
  Tel 01475 798106
  [EMAIL PROTECTED]
  http://www.camerondesign.co.uk
 
  This message is sent in confidence for the addressee only. It may
contain
  legally privileged information.
  Unauthorised recipients are requested to preserve this confidentiality
and
  to advise the sender
  immediately of any error in transmission.
  Dl Neil [EMAIL PROTECTED] wrote in message
  0b8401c1e62e$03072ad0$0600a8c0@jrbrown">news:0b8401c1e62e$03072ad0$0600a8c0@jrbrown...
   Hi Alex,
  
Got my select statement to work as follows:
$query = ' SELECT * FROM notices WHERE TO_DAYS(eventdate) =
   TO_DAYS(now())
order by eventdate';
  
   =well done!
   However the TO-DAYS calls do seem a bit OTT.
   Please post the schema for tbl:notices - specifically the datatype for
   eventdate.
  
Now trying to get the date more user friendly and tried:
   
$query = ' SELECT id, eventheading, DATE_FORMAT(eventdate, %D %M
   %Y)FROM
notices WHERE TO_DAYS(eventdate) = TO_DAYS(now()) order by
   eventdate';
   ...
When I echo ($eventdate) I get nothing. Not even an error.
  
  
   The problem is that
  
   $eventdate = $row[eventdate];
  
   (which doesn't need the  around the whole of the RHS (some would put
   them around eventdate), BTW)
   doesn't tie up with:
  
   DATE_FORMAT(eventdate, %D %M %Y)
  
   Recommend you change them to:
  
   DATE_FORMAT(eventdate, %D %M %Y) AS evdt
   and
   $eventdate = $row[evdt];
  
   See how the SQL and PHP tie together?
   Now you need to reconsider the other $row[] assignments.
  
   =Keep it coming!
   =dn
  
 
 
 
  --
  PHP Database Mailing List (http://www.php.net/)
  To unsubscribe, visit: http://www.php.net/unsub.php


 ---
 Outgoing mail is certified Virus Free.
 Checked by AVG anti-virus system (http://www.grisoft.com).
 Version: 6.0.330 / Virus Database: 184 - Release Date: 2/28/02




-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Dates in MYSQL

RE: [PHP-DB] dates in MYSQL

Re: [PHP-DB] Dates in MYSQL

Re: [PHP-DB] Dates in MYSQL

Re: [PHP-DB] Dates in MYSQL

Re: [PHP-DB] Dates in MYSQL

6 matches

Site Navigation

Mail list logo

Footer information