MySQLi update query issue

Question

My page sets signin_time sets it to NOW(), sets logged_in to 1 when user successfully signs into page. What I want to do is to set signout_time to NOW() when user signs out too. For this purpose I'm using following query

$stmt = $db->prepare("UPDATE `ulog` SET `logged_in`=0, `signout_time`=NOW(),`ckey`= '', `ctime`= '' WHERE user_id=? AND logged_in=1") or die($db->error);

I tried to execute this query. It didn't return any error message, but I haven't noticed any change in db table too, also php error log doesn't show anything.

Maybe I have syntax error (I'm setting logged_in=0 where logged_in=1). I have no other idea how to do that. Maybe I should search for rows where signout_time=0? Any suggestions?

Update

Here is whole function:

function logout() {
    global $db, $wsurl;
    if (isset($_SESSION['user_id'])) {
        $userid = $_SESSION['user_id'];
        $stmt = $db->prepare("UPDATE `ulog` SET `logged_in`=0, `signout_time`=NOW() WHERE user_id=? AND logged_in=1") or die($db->error);
    } else {
        $userid = $_COOKIE['user_id'];
        $stmt = $db->prepare("UPDATE `ulog` SET `logged_in`=0, `signout_time`=NOW(),`ckey`= '', `ctime`= '' WHERE user_id=? AND logged_in=1") or die($db->error);
    }

    $stmt->bind_param("i", $userid) or die($stmt->error);
    $stmt->execute() or die($stmt->error);
    $stmt->close();
    $_SESSION = array(); //destroy all of the session variables}
    session_destroy();
    foreach ($_COOKIE as $c_id => $c_value) {
        setcookie($c_id, '', 1, "/");
    }

    header("Location: " . $wsurl);
}

Did you try wrapping it in a try/catch? Maybe your `$userid` isn't getting set.. Try `var_dump($userid)` and let me know what you get. — Yes Barry, Nov 16 '11 at 01:37
Have you double checked the value in $userid, before you bind it? The query itself seems fine. — BudwiseЯ, Nov 16 '11 at 01:37

Yes Barry · Accepted Answer · 2011-11-16T01:41:13.907

1

You have to execute the query.

$userId = 1; // some number
$stmt->execute(array($userId));

Or you could combine them into one using method chaining:

 $db->prepare(
     "UPDATE `ulog` SET `logged_in`=0, `signout_time`=NOW(),
     `ckey`= '', `ctime`= '' WHERE user_id=? AND logged_in=1"
 )->execute(array($userId));

Also, make sure your $userid is being set. Do a var_dump() on it and see what you get.

edited Nov 16 '11 at 01:41

answered Nov 16 '11 at 01:30

Yes Barry

9,514
5
50
69

UPDATED question. Pls, take a look. Of course I'm binding userid then – Tural Ali Nov 16 '11 at 01:32
I echoed user id it gave me true number. WHy array? it's 1 and only number – Tural Ali Nov 16 '11 at 01:58

no. · Answer 2 · 2011-11-16T01:50:33.610

Have you tried different methods of preparing/executing, it seems I do mine slightly different to yours... not say it will definitely work but it's worth a try I guess

function logout() {

    global $db, $wsurl;

    if (isset($_SESSION['user_id'])) {

        $userid = $_SESSION['user_id'];
        $stmt = $db->prepare("UPDATE `ulog`
                              SET `logged_in` = 0,
                                  `signout_time` = NOW()
                              WHERE `user_id` = :userid
                              AND `logged_in` = 1") or die($db->error);

    } else {

        $userid = $_COOKIE['user_id'];
        $stmt = $db->prepare("UPDATE `ulog`
                              SET `logged_in` = 0,
                                  `signout_time` = NOW(),
                                  `ckey` = '',
                                  `ctime` = ''
                              WHERE `user_id` = :userid
                              AND `logged_in` = 1") or die($db->error);

    }

    $dataArray = array(':userid' => $userid);

    $stmt->execute($dataArray);
    $stmt->close();

    $_SESSION = array();
    session_destroy();

    foreach ($_COOKIE as $c_id => $c_value) {
        setcookie($c_id, '', 1, "/");
    }

    header("Location: " . $wsurl);

}

I don't use `bind_param` and I execute through an array instead. Like I said, it's only slightly different. I was just throwing it out there as a way you could try just in case it did for some reason work, sorry. — no., Nov 16 '11 at 02:01

MySQLi update query issue

Update

2 Answers2

Linked