Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Regular Coder
    Join Date
    Mar 2005
    Location
    Spokane, WA
    Posts
    148
    Thanks
    4
    Thanked 4 Times in 4 Posts

    Update SQL Query Not Working

    Ok I have this PHP code:

    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?

  • #2
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    721
    Thanks
    20
    Thanked 84 Times in 84 Posts
    what gets echoed out?

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

  • #3
    Regular Coder
    Join Date
    Mar 2005
    Location
    Spokane, WA
    Posts
    148
    Thanks
    4
    Thanked 4 Times in 4 Posts
    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.

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,537
    Thanks
    45
    Thanked 259 Times in 256 Posts
    And if you echo out the query string before trying to query it, what does it show? It looks correct? No MySQL errors?

  • #5
    Regular Coder
    Join Date
    Mar 2005
    Location
    Spokane, WA
    Posts
    148
    Thanks
    4
    Thanked 4 Times in 4 Posts
    It shows

    UPDATE `users` SET `sessionid` = 'fa022ff961c90815c1ad16322eec65afbcd1ffbf', `ip` = '127.0.0.1', `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.
    Last edited by RyanB88; 07-16-2012 at 04:59 PM.

  • #6
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,537
    Thanks
    45
    Thanked 259 Times in 256 Posts
    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?

  • #7
    Regular Coder
    Join Date
    Mar 2005
    Location
    Spokane, WA
    Posts
    148
    Thanks
    4
    Thanked 4 Times in 4 Posts
    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

    PHP Code:
    $result $mysqli->query("SELECT * FROM Users WHERE username = '" $username "' AND password = '" $password "' AND active = 1 LIMIT 1;");
    if (
    $result->num_rows == && $error == false && $blocked == false) { 
    Last edited by RyanB88; 07-16-2012 at 05:33 PM.

  • #8
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,537
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Yah, I've never needed a semicolon either, so that is very strange...

  • #9
    Regular Coder
    Join Date
    Mar 2005
    Location
    Spokane, WA
    Posts
    148
    Thanks
    4
    Thanked 4 Times in 4 Posts
    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.

  • #10
    Regular Coder
    Join Date
    Mar 2005
    Location
    Spokane, WA
    Posts
    148
    Thanks
    4
    Thanked 4 Times in 4 Posts
    So i did figure out how I got it to work

    This works:
    PHP Code:
    $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:
    PHP Code:
    $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.

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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:
    PHP Code:
    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.

    Edit:
    Given your reply you got in there above, show how you create the $sessionid variable as well.
    Last edited by Fou-Lu; 07-16-2012 at 05:49 PM.

  • #12
    Regular Coder
    Join Date
    Mar 2005
    Location
    Spokane, WA
    Posts
    148
    Thanks
    4
    Thanked 4 Times in 4 Posts
    I added your code, it outputs:

    Sessionid: 579c94985b7088ac62e68f10f8d893936217aaf2, ip: 127.0.0.1, username: user UPDATE `users` SET `sessionid` = '579c94985b7088ac62e68f10f8d893936217aaf2', `ip` = '127.0.0.1', `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` (
    `ID` tinyint(4) NOT NULL AUTO_INCREMENT,
    `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,
    `lastlogin` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `active` tinyint(1) NOT NULL,
    PRIMARY KEY (`ID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
    PHP Code:
    $sessionid sha1(time()); 

  • #13
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • #14
    Regular Coder
    Join Date
    Mar 2005
    Location
    Spokane, WA
    Posts
    148
    Thanks
    4
    Thanked 4 Times in 4 Posts
    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

    PHP Code:
    if (time() - strtotime($row['lastlogin']) > 1800) {
    timeout();

    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?

  • #15
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •