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]; > > > > > > > > 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 > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
