MYSQL PDO update datetime does nothing

Question

Tried for an hour already. Help pls. Basically i want to update lastlogin column(MYSQL datetime) every time a user login. There is no error message and no update is happening. What is going on?

db.php

<?php 



    // These variables define the connection information for your MySQL database 

    $username = "root"; 

    $password = "root"; 

    $host = "localhost"; 

    $dbname = "rofl"; 



    $options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'); 

    try { $db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options); } 

    catch(PDOException $ex){ die("Failed to connect to the database: " . $ex->getMessage());} 

    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 

    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); 

    header('Content-Type: text/html; charset=utf-8'); 

    session_start();        

?>

main.php

<?php 
    include("DB/db.php"); 


        if(!empty($_POST)){   
            $query = " 
                SELECT 
                    userID, 
                    password, 
                    salt, 
                    email 
                FROM user 
                WHERE 
                    userID = :username 
            "; 
            $query_params = array( 
                ':username' => $_POST['useridforlogin'] 
            ); 

            try{ 
                $stmt = $db->prepare($query); 
                $result = $stmt->execute($query_params); 
            } 
            catch(PDOException $ex){ die("Failed to run query: " . $ex->getMessage()); } 
            $login_ok = false; 
            $row = $stmt->fetch();

            if($row){ 
                $check_password = hash('sha256', $_POST['passwordforlogin'] . $row['salt']); 
                for($round = 0; $round < 65536; $round++){
                    $check_password = hash('sha256', $check_password . $row['salt']);
                } 
                if($check_password === $row['password']){
                    $login_ok = true;
                } 
            } 
            if($login_ok){ 
                unset($row['salt']); 
                unset($row['password']); 
                $_SESSION['user'] = $row;  

             date_default_timezone_set('Asia/xxxxxx');
    $timestamp = date('Y/m/d h:i:s a', time()); 
    $query2 = "UPDATE user SET lastlogin= ':lastlogin' where userID =  ':username'";
            $query_params2 = array(  
                ':lastlogin' => $timestamp,
               ':username' => $_POST['useridforlogin']
               ); 
              try {  
                $stmt2 = $db->prepare($query2); 
                $result2 = $stmt2->execute($query_params2); 
            }  catch(PDOException $ex){ die("Failed to run query: " . $ex->getMessage()); }

You're using PDO placeholders, which is great, but surrounding them in quotes (`':id'`) is incorrect. Placeholders should be left bare. — tadman, Nov 19 '16 at 08:28

score 0 · Answer 1 · answered Nov 19 '16 at 08:30

0

I think the problem comes from your date format.

Try replacing 'Y/m/d h:i:s a' by 'Y/m/d h:i:s' (removed the a)

answered Nov 19 '16 at 08:30

FLX

2,626
4
26
57

doesnt work :( sad me – Roger Nov 19 '16 at 08:36
And 'Y-m-d h:i:s' ? (didn't saw these slashes) – FLX Nov 19 '16 at 08:37
nope! doesnt work as well. its not updating upon logging in. – Roger Nov 19 '16 at 08:38
right now - 2016-11-19 04:39:22. i tried manually insert into sql it would work. PDO statement issue? – Roger Nov 19 '16 at 08:39
Have you tried without single quotes as tadmad said ? – FLX Nov 19 '16 at 08:40
UPDATE user SET lastlogin= '2016-11-19 04:39:22' where userID = 'xxxxx' and it would work. must be pdo statement i think – Roger Nov 19 '16 at 08:41
"UPDATE user SET lastlogin= :lastlogin where userID = :username" ? Quotes are need when using the real date, not when using statements. – FLX Nov 19 '16 at 08:41
got it already. Damn quotes – Roger Nov 19 '16 at 08:42

MYSQL PDO update datetime does nothing

1 Answers1