php insert, lookup, update with mysql

Question

I have been out of the PHP loop for a while and when I returned, yesterday, I quickly found that many old php functions to interact with mysql are being phased out. This is fine, but I am really struggling with the new pdo code. Lets say a user is going to log in, I want to see if his username/password combo is correct. Then I want to update his login count, move the date/time of his last login to 'previouslogin' and update 'lastlogin' with the current date/time. Problems I am having. I can't get the logincount to increment, not even the variable, I can't assign the lastlogin to the variable, which prevents me being able to update previouslogin. Can you show me what I am doing wrong?

    public function accountLogin()
{
    $sql = "SELECT UserID, UserName, PositionID, LoginCount, LastLogin
            FROM users
            WHERE UserName=:user
            AND Password=:pass
            LIMIT 1";
    try
    {
        $stmt = $this->_db->prepare($sql);
        $stmt->bindParam(':user', $_POST['username'], PDO::PARAM_STR);
        $stmt->bindParam(':pass', $_POST['password'], PDO::PARAM_STR);
        $stmt->execute();
        if($stmt->rowCount()==1)
        {
            $_SESSION['Username'] = htmlentities($_POST['username'], ENT_QUOTES);
            $_SESSION['LoggedIn'] = 1;
            $_SESSION['UserID']= $stmt->fetch()[0];
            $_SESSION['PositionID']= $stmt->fetch()[2];                                
            $logincount= $stmt->fetch()[3]; 
            $lastlogin= $stmt->fetch()[4];
            $sql = "UPDATE users SET LoginCount = ". $logincount + 1 . ", 
            PreviousLogin = " . $lastlogin ."
            WHERE UserID = " . $_SESSION['UserID'];

            $stmt = $this->_db->exec($sql);

            return TRUE;

Your code will only work with php 5.4 what do you have installed? — Cody Covey
– Cody Covey, Commented Jan 12, 2013 at 21:32
php 5.4 installed. It works halfway, but I can not set PreviousLogin to lastlogin, and my mysql table is not updating lastlogin to current timestamp, even though login count is incrementing. — riley3131
– riley3131, Commented Jan 14, 2013 at 15:50

lobostome · Accepted Answer · 2013-01-12 21:54:37Z

To increment a MySQL field by one, you can do LoginCount = LoginCount + 1

public function accountLogin()
{
    $sql = "SELECT UserID, UserName, PositionID, LoginCount, LastLogin
        FROM users
        WHERE UserName=:user
        AND Password=:pass
        LIMIT 1";
try
{
    $stmt = $this->_db->prepare($sql);
    $stmt->bindParam(':user', $_POST['username'], PDO::PARAM_STR);
    $stmt->bindParam(':pass', $_POST['password'], PDO::PARAM_STR);
    $stmt->execute();
    if($stmt->rowCount()==1)
    {
        $row = $stmt->fetch( PDO::FETCH_OB );
        $_SESSION['Username'] = htmlentities($row->UserName, ENT_QUOTES, "UTF-8");
        $_SESSION['LoggedIn'] = 1;
        $_SESSION['UserID']= $row->UserID;
        $_SESSION['PositionID']= $row->PositionID;                                
        $lastlogin= $row->LastLogin;
        $sql = "UPDATE users SET LoginCount = LoginCount + 1, 
        PreviousLogin = " . $lastlogin ."
        WHERE UserID = " . $_SESSION['UserID'];

        $stmt = $this->_db->exec($sql);

        return TRUE;

Collectives™ on Stack Overflow

php insert, lookup, update with mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related