timestamp -mysql_fetch_assoc()

Question

I want my program to go through each row and check timestamp, if timestamp is older than two days, insert 5(number) in column (street) at current position, else echo "days, hours, minutes"..

UPDATE : Added Updated Code from OP's PasteBin URL :

<?php
//db conn............

 $lat= 7;
    $query='SELECT timestamp FROM users';
    $result = mysql_query($query);
    while ($row = mysql_fetch_assoc($result)) {

        $time = strtotime($row);
        $one_week_ago = strtotime('-1 day');
        $a=6;

        if( $time > $one_week_ago ) {    //not older than one day

           $p="UPDATE users SET  confirm ='$a'";
           if(mysql_query($p))
           {   
            echo "$a";
           }
           else{
               echo "wrong";
           }

         }
         else {  //older than one day
            $query="UPDATE users SET street= '$lat' WHERE confirm !=$a";

            if(mysql_query($query))
            {  
                echo "sucsessful <br>";
            }
            else {
                echo "unsuccessful <br>";
            }

         }

    }
?>

[Please, don't use `mysql_*` functions in new code](http://stackoverflow.com/questions/12859942/). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [*prepared statements*](https://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which one is best for you. — John Conde, Mar 20 '17 at 14:31
`UPDATE users SET street= '$lat'` you realize what this does, right? It updates your entire db when a `WHERE` clause is omitted. — Funk Forty Niner, Mar 20 '17 at 14:35
and was there a question here? besides your entire db being updated, what (real) errors are you getting? — Funk Forty Niner, Mar 20 '17 at 14:36
and what `mysql_fetch_assoc()` has to do here, as per what's part of the question's title. — Funk Forty Niner, Mar 20 '17 at 14:39
I do not get any error, but code doesn't work.. it wont even update my entire db..actually I get nothing. — mica1234, Mar 20 '17 at 14:47
Besides what @JayBlanchard said, your *"I do not get any error, but code doesn't work"* probably never made it there in the first place. You didn't get errors because you weren't looking for them, so there. How'd you like them apples? http://php.net/manual/en/function.mysql-error.php — Funk Forty Niner, Mar 20 '17 at 14:51
FROM was the problem, English is not my first language haha :) tnx Jay :) — mica1234, Mar 20 '17 at 14:55
*"I will change this too UPDATE users SET street= '$lat'"* - You're still going to be updating your entire db, and you'll get some unexpected results that will overwrite your entire db if there is more than one record. Unless that's what you want. — Funk Forty Niner, Mar 20 '17 at 14:58
hi, guys.. can you help me one more time.. my code is here http://pastebin.com/uXyxiFEu .. it wont go through if statement if( $time > $one_week_ago ) , but i have days older the 1, 2 and 3 days form now. — mica1234, Mar 20 '17 at 16:45
help you one more time? you can start by accepting the answer below if it solved your present question. — Funk Forty Niner, Apr 15 '17 at 13:20

score 1 · Answer 1 · answered Apr 13 '17 at 07:14

Specification : I assume that your table has structure as below :

+---------+--------------+------+-----+--------------------+----------------+
| Field   | Type         | Null | Key | Default            | Extra          |
+---------+--------------+------+-----+--------------------+----------------+
| user_id | mediumint(9) | NO   | PRI | NULL               | auto_increment |
| timestamp |  timestamp | NO   |     | CURRENT_TIMESTAMP  |                |
| street  | int(10)      | NO   |     | 0                  |                |
| confirm | int(10)      | NO   |     | 0                  |                |
+---------+--------------+------+-----+--------------------+----------------+

Solution : Just replace your code with my code as given below :

<?php
//db conn............
 $lat= 7;
    $query='SELECT user_id, timestamp FROM users';
    $result = mysql_query($query);
    while ($row = mysql_fetch_assoc($result)) {
        $time = $row['timestamp'];
        $one_week_ago = date('Y-m-d H:i:s', strtotime('-1 day'));
        $a=6;
        if( $time > $one_week_ago ) {    //not older than one day
           $p="UPDATE users SET  confirm ='$a' where user_id = ".$row['user_id'];
           if(mysql_query($p))
           {   
            echo "$a";
           }
           else{
               echo "wrong";
           }
         }
         else {  //older than one day
            $query="UPDATE users SET street= '$lat' WHERE confirm !=$a and user_id = ".$row['user_id'];
            if(mysql_query($query))
            {  
                echo "sucsessful <br>";
            }
            else {
                echo "unsuccessful <br>";
            }
         }
    }
?>

timestamp -mysql_fetch_assoc()

1 Answers1