mysql data update attempt is unsuccessful

Question

I am working on a website that lets users interact with each other. When user logs in on my there is a code that sets a field login_status to online in my database. This is working fine.

The problem comes when user logs off I set the same field to offline and add the logout time in a field last_login. Below is the code for my logout.php

session_name("_user");session_start();
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$id = $_COOKIE['_d']; //this cookie has the user  id of the user

// set user offline     
$query = "UPDATE details SET login_status = 'offline' WHERE user_id = '$id";    
$update = mysqli_query($conn, $query); //not working

// set last login
$time = date("h:i:sa");
$date = date("d-m-Y");
$last = "on ". $date." at". $time;

$query = "UPDATE details SET last_login = '$last' WHERE user_id = '$id";    
$update = mysqli_query($conn, $query); // not working


// Unset all session values 
$_SESSION = array();

// get session parameters 
$params = session_get_cookie_params();

// Delete the actual cookie. 
setcookie(session_name(),'', time() - 42000, $params["path"], $params["domain"], $params["secure"], $params["httponly"]);

$past = time() - 100;

setcookie('_d', 'deleted', $past);
setcookie('_user', 'deleted', $past);
setcookie('uid', 'deleted', $past);
setcookie('_vip', 'deleted', $past);
setcookie('_status', 'deleted', $past);
setcookie('_edit', 'deleted', $past);

// Destroy session 
session_destroy();

header("Location: login?status=logout%20sucessful");

both the queries is not working and doesn't update my table details. what's the mistake I am making.

Just echo your `$query` and see what happens if you post the query in your phpmyadmin — Sulthan Allaudeen, Apr 09 '15 at 12:12
What do you mean by "not working"? Errors? Blank values? You're not doing any error checking, so you may find the problem in the logs. — Jay Blanchard, Apr 09 '15 at 12:14
`or die(mysqli_error($conn))` to `mysqli_query()`. *Mornin' Sam* @JayBlanchard — Funk Forty Niner, Apr 09 '15 at 12:15
Where does the cookie data come from? You should *always* use parameterized queries @Pushkar — Jay Blanchard, Apr 09 '15 at 12:23
@Pushkar, COOKIE is stored in client computer. People can send a fake COOKIE. It is beyond your control ^^ — invisal, Apr 09 '15 at 12:31

score 2 · Accepted Answer · answered Apr 09 '15 at 12:14

First of all, do not manually concatenate your SQL query with untrusted input. This is bad practice.

Let put this aside. I spot that your query is not valid.

UPDATE details SET login_status = 'offline' WHERE user_id = '$id
                                                            ^
                                                        there is opening single
                                                        quote without closing
                                                        quote.

score 2 · Answer 2 · answered Apr 09 '15 at 12:14

you have a typo error

$query = "UPDATE details SET login_status = 'offline' WHERE user_id = '$id";

in the above one , you missed the ' after $id. Change it as

 $query = "UPDATE details SET login_status = 'offline' WHERE user_id = '$id'";

in both queries

Jenis Patel · Answer 3 · 2015-04-09T12:21:21.343

1

Your query seems to have syntax error so modify your query as

$query = "UPDATE details SET login_status = 'offline' WHERE user_id = '$id'";  // modify single quote from $id

instead of

$query = "UPDATE details SET login_status = 'offline' WHERE user_id = '$id";

Same correction in your second update query.

edited Apr 09 '15 at 12:21

answered Apr 09 '15 at 12:16

Jenis Patel

1,617
1
13
20

*"`WHERE user_id = $id";`"* - If their user_id column and cookie are `int`, sure. Otherwise, it will error out. – Funk Forty Niner Apr 09 '15 at 12:19
you are right, I have made the required corrections. – Jenis Patel Apr 09 '15 at 12:22
1

You could have just made an edit under it stating it. Your original answer may have been correct, which is why it's always good to address both possible issues. – Funk Forty Niner Apr 09 '15 at 12:23

InTERpLAY · Answer 4 · 2015-04-09T12:21:43.780

0

I would use DATETIME datatype into database for storage lastlogin. There is no sense to save "on" and "at", just add it when print message

edited Apr 09 '15 at 12:21

answered Apr 09 '15 at 12:20

InTERpLAY

129
1
5

3

This doesn't address the problem; stick with the question's real problem. – Funk Forty Niner Apr 09 '15 at 12:21

mysql data update attempt is unsuccessful

4 Answers4