Strange issue with timestamp update in MYSQL

Question

I have a strange issue i have come accross on a few sites i have built, but i am unable to find the problem, it seems so simple, when a user logs in to the site i update the timestamp for the column "member_login" to NOW() which works great, the only issue is, it also updates the "member_joined" column to the exact same time as the "member_login" timestamp but NOWHERE in the code do i tell it to update the "member_joined" column! the entire login script is fairly simple:

login.php

<?php

session_start();
include('includes/db_connection.php');
include('includes/functions.php');

?>

<?php

if (isset($_POST['submitLogin'])) {

    $username = trim(strtolower($_POST['username']));
    $password = trim(strtolower($_POST['password']));

    if (empty($username) || empty($password)) {
        include('includes/header.php');
        include('includes/navbar.php');
        stderr('Please fill in <b>both</b> login fields.');
        include('includes/footer.php');
    }

    $memberInfo = DB::getInstance()->selectOne(
        '
        SELECT  DISTINCT member_username, member_fees_paid, member_fees_paid_on
        FROM    `membership`
        WHERE   `member_username` = :member_username
        AND     `member_password` = :member_password',
        [
            'member_username' => $username,
            'member_password' => $password
        ]
    );

    if (!count($memberInfo)) {

        include('includes/header.php');
        include('includes/navbar.php');
        stderr('Sorry, username <b>and/or</b> password combination not found.');
        include('includes/footer.php');

    } else {

        $_SESSION['member'] = $username;        

        if ($memberInfo['member_fees_paid'] === 'N') {
            $payedOn = new \DateTime($memberInfo['member_fees_paid_on']);
            $payedOn->setTime(0, 0, 0);
            $monthAgo = new \DateTime('28 days ago');
            $monthAgo->setTime(0, 0, 0);
            if ($monthAgo > $payedOn) {
                try {
                    DB::getInstance()->execute(
                        '
                        UPDATE  `membership`
                        SET     `member_fees_paid`= \'N\'
                        WHERE   `member_username` = :member_username
                        AND     `member_password` = :member_password',
                        [
                            'member_username' => $username,
                            'member_password' => $password
                        ]
                    );
                    header('Location: my-account.php');
                } catch (Exception $e) {

                    include('includes/header.php');
                    include('includes/navbar.php');
                    stderr($e->getMessage());
                    include('includes/footer.php');

                }
            }
        }

        try {

            DB::getInstance()->execute(
                '
                UPDATE  `membership`
                SET     `member_login` = NOW()
                WHERE   `member_username` = :member_username
                AND     `member_password` = :member_password',
                [
                    'member_username' => $username,
                    'member_password' => $password
                ]
            );
            header('Location: my-account.php');

        } catch (Exception $e) {

                include('includes/header.php');
                include('includes/navbar.php');
                stderr($e->getMessage());
                include('includes/footer.php');

        }
    }
}

?>

<?php

include('includes/header.php');
include('includes/navbar.php');

?>

    <div class="panel panel-primary">
        <div class="panel-heading">Login to your account.</div>
        <div class="panel-body">
            <form action="login.php" method="post" class="form-horizontal container-fluid" role="form">

                <div class="row form-group">
                    <div class="col-sm-6 text-right">
                        <label class="control-label">Username:</label>
                    </div>
                    <div class="col-sm-6">
                        <input type="text" name="username" class="form-control" size="40" required="required"/>
                    </div>
                </div>

                <div class="row form-group">
                    <div class="col-sm-6 text-right">
                        <label class="control-label">Password:</label>
                    </div>
                    <div class="col-sm-6">
                        <input type="password" name="password" class="form-control" size="40" required="required"/>
                    </div>
                </div>
                <div class="row form-group">
                    <div class="col-sm-6 text-right">
                        <label class="control-label" style="display: inline-block;">&nbsp;</label>
                    </div>
                    <div class="col-sm-6 text-right">
                        <button type="submit" name="submitLogin" class="btn btn-primary">Login</button>
                    </div>
                </div>
            </form>
        </div>
        <div class="panel-footer">Forgot <b>your password</b>? recover it <a href="recover-password.php">here</a>.</div>
    </div>

<?php

include('includes/footer.php');

On a successful login we just update the timestamp for the "member_login" and redirect to the "my-account.php" page, which works as it should, but it keeps updating the "member_joined" colums the same as the "member_login" i cannot figure out why it is doing this, any help would be appreciated!

Answer 1

If your column member_joined is type timestamp, you might want to check that on update CURRENT_TIMESTAMP isn't being used - you can check this in a database reader such as phpMyAdmin, SequalPro, Heidi etc.

This will automatically update the datestamp to the current date/time.