Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Jan 2006
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts

    PHP / Mysql's Date Interval

    I normally enter a php date string into mysql when needed. However this time I am using Mysql automatic date. I am trying to retrieve records that are no more then 30days old, and delete those that are.

    I can not figure out why my queries are not working. Even though no errors are being generated. The code is as follows:

    Code:
    Retrieve
    $conn->query("SELECT * from statistics WHERE 'date_visited' > DATE_SUB(CURDATE(),INTERVAL 30 DAY)";

    Code:
    And Delete Query
    $conn->query("DELETE FROM statistics WHERE 'date_visited' <= DATE_SUB(NOW(),INTERVAL 30 DAY)";

    I have been changing the dates through phpmyadmin and comming back to the script to see if any changes have occured. But nothing.

    Please any help would be much appreciated. Thank You.

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,861
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    What is this $conn? where is you mysql_query() ? Find it and have a try by changing
    Code:
    mysql_query() or die(mysql_error());
    Last edited by abduraooft; 02-24-2008 at 12:16 PM.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Quote Originally Posted by david07 View Post
    Code:
    Retrieve
    $conn->query("SELECT * from statistics WHERE 'date_visited' > DATE_SUB(CURDATE(),INTERVAL 30 DAY)";

    Code:
    And Delete Query
    $conn->query("DELETE FROM statistics WHERE 'date_visited' <= DATE_SUB(NOW(),INTERVAL 30 DAY)";
    Did you notice you used CURDATE() in the select and NOW() in the delete query? Make sure you have error checking for your queries.
    Quote Originally Posted by abduraooft View Post
    What id this $conn? where is you mysql_query() ? Find it and have a try by changing
    Code:
    mysql_query() or die(mysql_error());
    It seems to be a connection class.

  • #4
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,861
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Quote Originally Posted by Inigoesdr View Post
    It seems to be a connection class.
    Ya, I thought so, but seems he is unaware of the code structure to debug.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #5
    New Coder
    Join Date
    Jan 2006
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts
    No i do know the code structure to debug. But im not getting any errors.
    $conn is a connection class. I am using php 5 OO. EG.


    //Connect to the database. (host,username,password,database)
    $db = new mysqli("localhost", "user", "password", "database");
    // Check for errors connecting to database.
    if (mysqli_connect_errno()) {
    die('Unable to connect to database.');
    }
    // All queries and commands go here.
    $query = $db->query("SELECT id,name FROM `tb_classList`");
    while ($data = $query->fetch_assoc()) {
    echo '<p>'.$data['name'].'</p>';
    }
    $numResults = $query->num_rows;
    echo '<p>'.$numResults.'</p>';
    $query->free_result();
    // Close $db connection
    $db->close();
    I use the following for debugging

    // display sql errors
    if ($conn->error) {
    printf("<div class='error'><br/><br/>Errormessage: %s\n", $conn->error ."</div>");
    }
    I find this much more efficient then using the old "mysql_query() or die(mysql_error());"

  • #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,066
    Thanks
    2
    Thanked 319 Times in 311 Posts
    You have got single-quotes around your column name - 'date_visited' This makes it a literal quoted string "date_visited" and then that string is being compared with the results of the DATE_SUB().

    Remove the single-quotes around the column names.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #7
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    The call to the error method should be on the db object:
    PHP Code:
    if (!$query)
    {
         
    printf("<div class='error'><br/><br/>Errormessage: %s\n"$db->error ."</div>");



  •  

    Posting Permissions

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