SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist Fleeters's Avatar
    Join Date
    Jul 2003
    Location
    Dumpsville
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    comparing date string vs mysql datetime

    I am trying to compare a string that i send a script to a datetime column type.. here is my script

    PHP Code:
    <?php
    require("config.php");
      
    $catId $_REQUEST['catId'];
      
    //make date to get
      
    $date $_REQUEST['date'];
      
    $month substr($date,0,2);
      
    $year substr($date,2,4);
      
    $getDate $year "-" $month "-00 00:00:00";
      echo 
    $getDate;
      
    //connect to db
      
    $link_id db_connect();
      
    //query db for article
      
    $query "
      SELECT wp_posts.ID as postId,
      unix_timestamp(wp_posts.post_date) as postUnixDate,
      date_format(wp_posts.post_date,'%Y %M %D') as postDate,
      wp_posts.post_title as postTitle,
      wp_categories.cat_name as categoryName,
      wp_categories.cat_ID as categoryId
      FROM wp_posts 
      INNER JOIN wp_post2cat ON wp_posts.ID = wp_post2cat.post_id
      INNER JOIN wp_categories ON wp_post2cat.category_id = wp_categories.cat_id 

    //compare dates
      AND wp_posts.post_date < unix_timestamp('
    $getDate')
    //------

      AND wp_categories.cat_ID = '
    $catId'
      ORDER BY wp_posts.post_date DESC"
    ;
      
    $result mysql_query($query);
      
    $count 0;
      while(
    $thisPost mysql_fetch_array($result)){
        
    $values .= "&pId$count=" $thisPost['postId'] . "&date$count=" $thisPost['postDate'] . 
        
    "&cId$count=" $thisPost['categoryId'] . "&cName$count=" $thisPost['categoryName'] . 
        
    "&t$count=" $thisPost['postTitle'];
        
    $count++;
      }
      
    $returnValue "&count=$count$values;
      echo 
    $returnValue;
    ?>
    so as you can see.. i pass the script a var with a value of six chars. ex: 032005.. then i break it up into month and year.. and convert that to : 2005-03-00 00:00:00 . and i want to compare that to the post_date column in the db.. i was attempting to use the unix_timestamp function but not any luck. so anyway that i can compare dates would be great

    can someone help??

    thankx all
    Aaron Smith
    smithaaronlee.net

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    have you tried

    Code:
      AND wp_posts.post_date < '$getDate'
    ?
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  3. #3
    SitePoint Evangelist Fleeters's Avatar
    Join Date
    Jul 2003
    Location
    Dumpsville
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah.. funny right after i posted this i tried that and it worked.. thanks..
    Aaron Smith
    smithaaronlee.net

  4. #4
    SitePoint Evangelist Fleeters's Avatar
    Join Date
    Jul 2003
    Location
    Dumpsville
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had to change the $getDate string to just "2005-03-00" and no seconds and it worked.. otherwise it wouldnt work
    Aaron Smith
    smithaaronlee.net


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •