View Full Version : Update SQL Query Not Working

07-16-2012, 06:24 AM
Ok I have this PHP code:

$mysqli->query("UPDATE `users` SET `sessionid` = '" . $sessionid . "', `ip` = '" . $ip . "', `lastlogin` = CURRENT_TIMESTAMP WHERE `username` = '" . $username . "' LIMIT 1");

$sessionid was set, and I can echo it back out and it has a value. When the code runs though everything gets updated except sessionid always comes up as a null string.

Any ideas what is causing this?

07-16-2012, 11:57 AM
what gets echoed out?

and what field type in the database table is sessionid set to?

07-16-2012, 05:46 PM
Sessionid is is just a random 40 character string generated using sha1(time());. The field is a varchar field. I can insert a value inside PhpMyAdmin without problems.

07-16-2012, 05:50 PM
And if you echo out the query string before trying to query it, what does it show? It looks correct? No MySQL errors?

07-16-2012, 05:56 PM
It shows

UPDATE `users` SET `sessionid` = 'fa022ff961c90815c1ad16322eec65afbcd1ffbf', `ip` = '', `lastlogin` = CURRENT_TIMESTAMP WHERE `username` = 'user' LIMIT 1

Which executes no problem if I use PhpMyAdmin's query tool. But for some reason PHP's mysqli query session id is always added a null. If i manually set a sessionid in PhpMyAdmin then run my code it nulls out the sessionid in the db.

07-16-2012, 05:58 PM
And other queries on that page are working? If you remove portions of that query, does it start working? A select written just above it?

07-16-2012, 06:01 PM
I change where it says LIMIT 1 to LIMIT 1; and all the sudden it works.

UPDATE: Well I only got it to work once now its back to sessionid being inserted as null. Everything gets updated but sessionid is always being set as null.

Any ideas what is causing this?

Immediately above it I have

$result = $mysqli->query("SELECT * FROM Users WHERE username = '" . $username . "' AND password = '" . $password . "' AND active = 1 LIMIT 1;");
if ($result->num_rows == 1 && $error == false && $blocked == false) {

07-16-2012, 06:30 PM
Yah, I've never needed a semicolon either, so that is very strange...

07-16-2012, 06:38 PM
I just updated the previous post while u were posting. I got that to work once...Now its back to insterting sessionid as a null string again for some reason.

07-16-2012, 06:45 PM
So i did figure out how I got it to work

This works:

$mysqli->query("UPDATE `users` SET `sessionid` = '" . $sessionid . "', `ip` = '" . $ip . "', `lastlogin` = CURRENT_TIMESTAMP WHERE `username` = '" . $username . "' LIMIT 1;");
$mysqli->query("INSERT INTO `loginattempts` (`ID`, `username`, `IP`, `Successful`, `Timestamp`) VALUES ('', '" . $username . "', '" . $ip . "', '1', CURRENT_TIMESTAMP);");
//setcookie("Session", $username . ":" . $sessionid);
//header("Location: list.php");

This does not:

$mysqli->query("UPDATE `users` SET `sessionid` = '" . $sessionid . "', `ip` = '" . $ip . "', `lastlogin` = CURRENT_TIMESTAMP WHERE `username` = '" . $username . "' LIMIT 1;");
$mysqli->query("INSERT INTO `loginattempts` (`ID`, `username`, `IP`, `Successful`, `Timestamp`) VALUES ('', '" . $username . "', '" . $ip . "', '1', CURRENT_TIMESTAMP);");
setcookie("Session", $username . ":" . $sessionid);
header("Location: list.php");

What is is about uncommenting the setcookie and header lines that all the sudden break an sql query that happened before those lines??

Now the other query, the insert query which logs the login as successful works in both of these examples.

07-16-2012, 06:47 PM
Semi-colon is only useful if you have multiple similar queries. PHP rejects different types of DMS statements for security.

Do you have more than one matching `user` type? If not, drop the limit completely. If so, you may have a structural anomaly.

Evaluate the variables first; so far given one of your other posts it looks ok though:

printf('Sessionid: %s, ip: %s, username: %s' . PHP_EOL, $sessionid, $ip, $username;
$sQry = "UPDATE `users` SET `sessionid` = '" . $sessionid . "', `ip` = '" . $ip . "', `lastlogin` = CURRENT_TIMESTAMP WHERE `username` = '" . $username . "'";
print $sQry;

Also run a SHOW CREATE TABLE users and post that.

Given your reply you got in there above, show how you create the $sessionid variable as well.

07-16-2012, 06:55 PM
I added your code, it outputs:

Sessionid: 579c94985b7088ac62e68f10f8d893936217aaf2, ip:, username: user UPDATE `users` SET `sessionid` = '579c94985b7088ac62e68f10f8d893936217aaf2', `ip` = '', `lastlogin` = CURRENT_TIMESTAMP WHERE `username` = 'user'

as expected. I did drop the LIMIT 1 from the query but its still not working for me.

CREATE TABLE `users` (
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`sessionid` varchar(255) NOT NULL,
`ip` varchar(255) NOT NULL,
`active` tinyint(1) NOT NULL,

$sessionid = sha1(time());

07-16-2012, 07:36 PM
None of this makes any sense, not by itself. If you copy that query out and run it, does it update all the columns properly as expected?
Methinks that this isn't to do with this query, rather another query that's not properly running.

07-16-2012, 08:31 PM
Ok now this is making more sense.

The sessionid is getting put in but is removed once i goto the next page because that checks the timestamps to see if the session expired. THe timestamp is in the MySQL db as yyyy-mm-dd hh:mm:ss but to easily see if too much time has passed i want to compare it to time().

So I do this

if (time() - strtotime($row['lastlogin']) > 1800) {

Heres the problem a timestamp like 2012-07-16 11:28:23 is showing up as 1342430903. I minute later if I echo time() i get 1342463406. Which is way more than a 60 second diffrence.

Not sure why that is, any ideas?

07-16-2012, 10:00 PM
Mkay, now that makes more sense.
Methinks just a timezone issue at hand.
How did you get these two numbers? Did you use the same functionality using CURRENT_TIMESTAMP, or are these generated by time()?

If I had to guess, the SQL configuration is using a different timestamp, perhaps UTC. PHP is using either an explicit or guessed timezone for you based on server settings, which result in a 5 hour difference (5? Sounds about right).

I'd check the settings for your date_default_timezone_set() in PHP. You can check in to see on the SQL side where it's setting at, which could be at UTC.

07-16-2012, 10:51 PM
There lies the problem. I was lazy and relied on mysql to set the time stamps which do match my system time Pacific Time. I check my phpinfo though, it was set to Europe/Berlin.

Thats what I get for being lazy and letting mysql handle timestamps.

I will update my php.ini but I think I will also redo my code to use use php for all my timestamps for consistancy.

Thanks for all your help.