Chapter 18 Working With Last_Login

[knot22]

Hi,

 

I am attempting to do the Pursue point 6 at the end of chapter 18 and seem to be stuck. How I envision executing this is as follows (also see code snippet below which shows just the relevant lines of script 18.8 plus lines that were added):

1. in script 18.8 in the select statement add another column that gets NOW() AS NewLastLogin

2. put $_SESSION['LastLogin'] and $_SESSION['NewLastLogin'] into separate variables

3. calculate the time difference between the two date/time variables in step 2

4. display to the user the last login date/time as well as the time between that date and the "new last login" date

5. update the LastLogin column, replacing the existing value with the date/time stored in the variable from step 2 that contains the NewLastLogin date/time

 

The part that's not working is echo $interval. It needs to show the number of days, hours, minutes, and seconds since the last login. What is the correct format to apply to get it to display this information?

 

Thanks, knot22

if ($e && $p) { // If everything's OK.
// Query the database:
$q = "SELECT UserID, FirstName, UserLevel, LastLogin, NOW() as NewLastLogin FROM Users WHERE (Email='$e' AND Pass=SHA1('$p')) AND Active IS NULL";
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

if (@mysqli_num_rows($r) == 1) { // A match was made.

// Register the values:
$_SESSION = mysqli_fetch_array ($r, MYSQLI_ASSOC);
mysqli_free_result($r);
mysqli_close($dbc);

//calculate difference between last login and new last login
$o = strtotime($_SESSION['LastLogin']);
$n = strtotime($_SESSION['NewLastLogin']);
$old = date_create(date('Y-m-d', $o));
$new = date_create(date('Y-m-d', $n));
echo $o . "<br />\n" . $n . "<br />\n";
$interval = date_diff($old, $new);
echo $interval->format('%R%a days') . "<br />";

[Larry]

This is not the approach I would take. I would create a "last_login" column in the database and update it each time the user logs in. Then your MySQL SELECT query could select the interval and the last login date and time. In short: put more logic in your query.

[ericp]

Hi,

 

For me, I use this logic in the query:

 

// Set the last_login date and time:
            $q = "UPDATE users SET last_login = NOW() WHERE user_id={$_SESSION['user_id']} LIMIT 1";
            $r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
            // mysqli_free_result($r);
            mysqli_close($dbc); // Close the database connection.

 

right after the value register comment.

 

Note: I also commented the  mysqli_close($dbc); for the SELECT query.

 

Then, I block of code looks like this:

.

.

.

if (@mysqli_num_rows($r) == 1) { // A match was made.

            // Register the values:
            $_SESSION = mysqli_fetch_array ($r, MYSQLI_ASSOC);
            mysqli_free_result($r);
            //mysqli_close($dbc);
            
            // Set the last_login date and time:
            $q = "UPDATE users SET last_login = NOW() WHERE user_id={$_SESSION['user_id']} LIMIT 1";
            $r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
            mysqli_close($dbc); // Close the database connection.

.

.

.

Eric P

[HartleySan]

Larry, I never knew about the mysqli_free_result function until looking at ericp's code above, but I noticed that the PHP.net page (http://php.net/manual/en/mysqli-result.free.php) says the following about the function:

 

 

You should always free your result with mysqli_free_result(), when your result object is not needed anymore.

 

Pretty straightforward, I think, but I don't recall you using the function in the book (but I can't verify that now).

Any thoughts on whether it should actually always be used or not?

Thanks.

[Antonio Conte]

PHP does garbage collection pretty good, so you don't really need to call that. It does free up resources quicker though, so doing it dosen't hurt.

[HartleySan]

So I guess closing the MySQLi connection doesn't automatically free up the resources then?

[Antonio Conte]

I don't really know, Jon. If I had to take a guess, I would think not. (at least directly) Garbage collectors are generally based on call stacks and memory references/values. When references exists, garbage collectors will generally ignore cleaning up before script execution is done. As the MySQLi package has it's own classes for prepared statements and results (as they are other objects/resources) it would surprise me if the result was directly linked with the connection. At best, it would hold one of those references I talked about.

 

As I wrote this, I wanted to test it out. I wrote a small script that disconnects, sleeps 5 seconds, and utilizes the result. It worked perfectly fine:

// Open connection
$mysqli = new mysqli('localhost', 'root', '', 'conte');

// Run query and receive result resource
$result = $mysqli->query('SELECT * from test');

// Close connection
$mysqli->close();

// Let's sleep....
sleep(5);

// Try using result
while ( $row = $result->fetch_array(MYSQLI_ASSOC) )
{
	echo $row['data'] . '<br>';
}

// Free resources
$result->free();

Considering this, I don't think closing the connection removes the result from memory. That's all garbage collection, is my guess. If the MySQLi class was in charge of the result, all known references would be deleted. (leaving the $result variable link to a non-existing reference) This is not technical proof by any stretch of the imagination, so don't spread any of this a "a fact/the truth". I do believe this is how it works though.

 

However, I've never once bothered freeing a result set before this very day. I rarely also never free objects/arrays from memory, so I wouldn't really think too much about this. I don't close connections neither. Shame on me. :/

[HartleySan]

Thanks for testing that out, Antonio. It's interesting to know (even if it's not necessarily useful to know).

 

Also, while I try to close DB connections, I often forget myself (and I wonder how much it really matters in the end).

The way I see it, the entire script is executing within a split second and ending, at which point, all the resources are freed up anyway, so I don't think it really matters.

Perhaps on a really busy server it could make a difference, but really, I wonder.

 

Perhaps Larry or someone else with some more real-world experience can chime in with facts (as opposed to my random thoughts).

[ericp]

Hi all,

 

Yes, everybody knows that mysqli_free_result() function frees the memory associated with the result. The mysqli_free_result function expects a mysqli_result object as it's parameter. Only mysqli_query calls using SELECT, SHOW, DESCRIBE or EXPLAIN queries will return such an object. Other type of query (including UPDATE) will return a boolean result, which does not need, and should not be, to be freed up using it.

 

So, if you use mysqli_free_result() for your last_login update, you will see the error like this:

 

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in...

 

That's also the reason why Antonio's small script test worked perfectly fine.

 

Eric P

[Antonio Conte]

Correlation does not imply causation.

 

While you are obviously correct for many reasons, you could theoretically build something that would break by calling close(). It does not makes sense, but that was not the point of discussion here.

<?php

// Open connection
$db = new Db();

// Run query and receive result resource
$result = $db->query();

while ( $row = $result->fetch() )
{
	echo $row['data'] . '<br>';
}

// Close connection
$db->close();

while ( $row = $result->fetch() )
{
	echo $row['data'] . '<br>';
}

class Db {
	private $result;
	
	public function query()
	{
		// Mock result array
		$result = new Object(
			array(
				array('id' => 1, "data" => "Testing"),
				array('id' => 2, "data" => "this"),
			)
		);
		
		return $this->result =& $result;
	}
	
	public function close()
	{
		// Call free in object class
		$this->result->free();
		$this->result = null;
	}
}

class Object
{
	
    private $array;
    
    private $position = 0;
    
    public function __construct(array $result) 
    {
    	$this->array = $result;
        $this->position = 0;
    }
    
    public function fetch()
    {
    	if ( $this->valid() )
    	{
    		$value = $this->current();
    		$this->next();
    		
    		return $value;
    	}
    	else {
    		$this->rewind();
    	}
    	
    	return false;
    }

    public function free()
    {
    	$this->array = null;
    }

	protected function valid() { return isset($this->array[$this->position]); }
    protected function current() { return $this->array[$this->position]; }
    protected function next() { ++$this->position; }
	protected function rewind() { $this->position = 0; }
}

[Larry]

I do mention mysqli_free_result() in one of my books. It's very similar to mysqli_close(), in my opinion, in that it's something you could/should do but don't actually have to because PHP will take care of it, too. With both functions, I tend to be better about using them when:

 

A) I'm being more finicky about my programming and doing every little thing you absolutely should do (i.e., not relying upon PHP to do it for me).

B ) It's a particularly complex script with high demands, and the benefits of immediately freeing the resources and/or closing the database will matter.

 

It's the "immediately" that's the issue here. Whether you use both functions or not, the end result will be the same, it's just a matter of at what precise millisecond the memory is freed and the database connection is called.

[HartleySan]

Like you said before Antonio, it's really just a matter of pointer references to objects, and when there are no longer any references to an object, the garbage collector automatically deletes the object from memory.