Re: [PHP-DB] Dates in MYSQL
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.
> > >
Re: [PHP-DB] Dates in MYSQL
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
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
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
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
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 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
[PHP-DB] dates in MYSQL
This may be a stupid question but I am a complete newbie. I am trying to automatically drop events from a noticeboard when the date has passed. I have stored the date of the event in the database in the form -MM-DD and wish to SELECT From the database WHERE date >= todays date. Is this possible or do I need to INSERT todays date into the database first. -- 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. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
