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 3 of 3
  1. #1
    Regular Coder
    Join Date
    Aug 2006
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    fetch the date from timestamp

    hi,


    i have a mysql table with 3 fields

    1.id 2.time 3.approved

    values

    1 1181237691 0
    2 1181237703 0
    3 1181237711 0

    i want to fetch the distinct dates from this timestamp values using select statement.
    i want to display like this
    1 2007-06-07


    second thing i want to update the approved value=1 where these distinct dates are equal to the above timestamps

    any of your help will be surely appreciated.
    thanks
    mrjameer

  • #2
    Regular Coder Coastal Web's Avatar
    Join Date
    Oct 2004
    Posts
    225
    Thanks
    12
    Thanked 3 Times in 3 Posts
    Greetings mrjameer,

    Though l couldn't test it (because l don't have a copy of your database; and l didn't really want to spend the time to set one up just for the sake of testing this.. but something like this should work. (see comments)

    If you get an error message, post the error and l will correct it.
    But in a nutshell this is basically what you're asking, no?

    PHP Code:
    <?php
    /*connect to database*/
    $Host "localhost";
    $dbname "DB_NAME";
    $User "DB_USERNAME";
    $Password "DB_PASSWORD";

    $link mysql_connect($Host$User$Password);
    mysql_db_query($dbname$sql$link);


    $date_to_approve "2007-06-07"// date to approve the entries...

    define("TABLE_NAME""table_name"true); // edit with name of your table...

    // first we go through all of the rows in the database, and stick them into an array...
    //if you only want "unapproved" rows, you'd add "WHERE `approved` = '0'" to the select statment obviously..
    $sql "SELECT `id` FROM `" TABLE_NAME "` ORDER BY `id` ASC LIMIT 10000";
    $result mysql_query($sql) or die('Queryproblem: '.mysql_error ());
    while(
    $row mysql_fetch_array($result)){
    $tableArray $row[id];
    }

    //next we loop through the array and do your bidding...
    $i=0;
    while(
    $i<count($tableArray)){

    $sql "SELECT `time`, `approved` FROM `" TABLE_NAME "` WHERE `id` = '" $tableArray[$i] . "' LIMIT 1";
    $result mysql_query($sql) or die('Queryproblem: '.mysql_error ());
    $row mysql_fetch_array($result);

    //now that we have the time stamp, lets format it...
    $time $row[time];
    $timeFormat date(Y-m-j$row[time]);
    $approved $row[approved];

    //if this entry isn't approved, lets go ahead and approve it..
    if(!$approved && $timeFormat == $date_to_approve){
    $sql "UPDATE `" TABLE_NAME "` SET `approved` = '1' WHERE `id` = '" $tableArray[$i] . " LIMIT 1;";
    $result mysql_query($sql) or die('Queryproblem: '.mysql_error ());
    }

    //echo the results per your request..
    echo $i".) " $timeFormat " <br />";

    $i++;
    }

    ?>
    Last edited by Coastal Web; 06-07-2007 at 10:54 PM.

  • #3
    Regular Coder
    Join Date
    Aug 2006
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hi


    PHP Code:
    <?php 
    /*connect to database*/ 
    $Host "localhost"
    $dbname "DB_NAME"
    $User "DB_USERNAME"
    $Password "DB_PASSWORD"

    $link mysql_connect($Host$User$Password); 
    mysql_db_query($dbname$sql$link); 


    $date_to_approve "2007-06-07"// date to approve the entries... 

    define("TABLE_NAME""table_name"true); // edit with name of your table... 

    // first we go through all of the rows in the database, and stick them into an array... 
    //if you only want "unapproved" rows, you'd add "WHERE `approved` = '0'" to the select statment obviously.. 
    $sql "SELECT `id` FROM `" TABLE_NAME "` ORDER BY `id` ASC LIMIT 10000"
    $result mysql_query($sql) or die('Queryproblem: '.mysql_error ()); 
    while(
    $row mysql_fetch_array($result)){ 
    $tableArray $row[id]; 


    //next we loop through the array and do your bidding... 
    $i=0
    while(
    $i<count($tableArray)){ 

    $sql "SELECT `time`, `approved` FROM `" TABLE_NAME "` WHERE `id` = '" $tableArray[$i] . "' LIMIT 1"
    $result mysql_query($sql) or die('Queryproblem: '.mysql_error ()); 
    $row mysql_fetch_array($result); 

    //now that we have the time stamp, lets format it... 
    $time $row[time]; 
    $timeFormat date(Y-m-j$row[time]); 
    $approved $row[approved];
    after the above code i want to display the distinct dates.once i display the distinct dates which are fetched from timestamps,then only i can upadte them.

    thanks
    mrjameer


  •  

    Posting Permissions

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