#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    166
    Rep Power
    15
    This query works fine in MySQL and returns 13 rows:

    SELECT article.id,article.title,article.metadescription,article.private,article.expiry,article.ts,category. expandedname,category.description FROM article,category,categorymapping WHERE category.name = 'articles' AND categorymapping.cid = category.id AND categorymapping.id = article.id AND article.private='0' AND expiry>NOW()

    In a PHP script this same query does not return any results:

    $result = mysql_query ("SELECT article.id,article.title,article.metadescription,article.private,article.expiry,article.ts,category. expandedname,category.description FROM article,category,categorymapping WHERE category.name = 'articles' AND categorymapping.cid = category.id AND categorymapping.id = article.id AND article.private='0' AND expiry>NOW()");

    if I remove the last bit " AND expiry>NOW() " it throws up 15 results (including the expired articles)

    Date in database is in format YYYYMMDD

    Help!

    Thanks
    David
  2. #2
  3. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,642
    Rep Power
    4492
    why do you have the now/(/)??

    just use now(), i've never had any problem with that.

    What is your field for storing your date? It's better to use a timestamp column because it's automatically updated to the current time when you do an insert.

    timestamp(16) is yyyymmddhhmmss
    timestamp(8) is yyyymmdd

    to change the column use

    ALTER TABLE your_table CHANGE COLUMN expiry expiry TIMESTAMP(8);

    Yes...you put expiry twice. The first one is current name, the second is new name. Leave them both the same to keep it the same.

    ---John Holmes...

    ------------------
    *************************************************************
    * The manual can probably answer 90% of your questions...
    *
    * PHP Manual. www.php.net/manual
    * MySQL Manual: www.mysql.com/documentation/mysql/bychapter
    *************************************************************
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    166
    Rep Power
    15
    >>>why do you have the now/(/)??

    I thought PHP needed the "(" and ")" escaping but you are right it works. Thank you!

    >>>What is your field for storing your date?

    It is not "NOW" but a YYYYMMDD date I set when the article will expire and move into archives

    So, timestamp in this case is not the one to use.


    David

    [This message has been edited by forefront (edited October 13, 2000).]

Similar Threads

  1. odd recordset problem after sql query
    By unclefu in forum ASP Programming
    Replies: 5
    Last Post: February 5th, 2004, 04:58 PM
  2. Overwriting mysql query with the query results
    By onerandomday in forum PHP Development
    Replies: 1
    Last Post: January 14th, 2004, 04:45 AM
  3. Syntax error in INSERT INTO query
    By Lee Miles in forum Visual Basic Programming
    Replies: 6
    Last Post: December 19th, 2003, 07:37 AM
  4. nested query and MySQL query
    By lucipher in forum MySQL Help
    Replies: 1
    Last Post: November 19th, 2003, 10:49 AM
  5. extremely inconsistent query times
    By mtnman in forum MySQL Help
    Replies: 0
    Last Post: October 23rd, 2003, 12:29 AM

IMN logo majestic logo threadwatch logo seochat tools logo