Welcome Guest, Not a member yet? Register   Sign In
Visit counter with TIMESTAMPDIFF
#1

[eluser]diegoalmesp[/eluser]
Hi guys, I'm trying to make a visit counter, everything is working except for 1 line of code, this is it:
Code:
$this->db->from('visitas');
  $this->db->where('ip_addr', $new_ip);
  $query = $this->db->get();
  $count = $query->num_rows();

  if ($count > 0) {
   $lastv = $query->row('last_visit');
   $currentv = new DateTime('NOW');

   $consulta = $this->db->query('SELECT TIMESTAMPDIFF(HOUR,"$currentv","$lastv" ) FROM visitas WHERE ip_addr = "$new_ip"',FALSE);

   if ($consulta->result() > 10) {
    $this->db->set('cant_visit', 'cant_visit+1', FALSE);
    $this->db->set('last_visit', 'NOW()', FALSE);
    $this->db->update('visitas');
   }

This line: if ($consulta->result() > 10) { it always return true, no matter how time passed, so, how can I fix that if?
The
Code:
$query->row('last_visit');
is a DATETIME.

Hope you can help me, thanks a lot!

Cheers

Diego
#2

[eluser]CroNiX[/eluser]
In your query, you never retrieve the results (using db::result(), db::result_array(), db::row(), db::row_array()). You also need to create an alias for your dimestampdiff calculation, or you won't be able to retrieve that specific field from the resultset.

Try
Code:
//create an alias for the timestampdiff calc, and retrieve a single ROW
$consulta = $this->db->query('SELECT TIMESTAMPDIFF(HOUR,"$currentv","$lastv" ) as timediff FROM visitas WHERE ip_addr = "$new_ip"',FALSE)->row();

if ($consulta->timediff > 10) { //check if the timestampdiff alias, timediff, is greater than 10
   //...
}
#3

[eluser]diegoalmesp[/eluser]
Thanks CroNix!, but now I'm getting this error:
Code:
Severity: Notice

Message: Trying to get property of non-object

Filename: models/visitas_model.php

Line Number: 33

Line 33 is: if ($consulta->timediff > 10) {

I also tried if ($consulta['timediff'] > 10) { but then I get:
Code:
Severity: Notice

Message: Undefined index: timediff

Filename: models/visitas_model.php

Line Number: 33

Thanks for your help!
#4

[eluser]diegoalmesp[/eluser]
Anyone else please? I still can't make this work, any help is gold!
#5

[eluser]noideawhattotypehere[/eluser]
Code:
var_dump($consulta);
what it says after the query?
#6

[eluser]diegoalmesp[/eluser]
Thanks!, this is the var_dump result:

Code:
array(0) { }

I have no idea why is returning 0.
This my new code with the changes above:

Code:
public function new_visit($ip)
{
  $new_ip = $ip; //getting this from the controller with $this->input->ip_address();

  $this->db->from('visitas');
  $this->db->where('ip_addr', $new_ip);
  $query = $this->db->get();
  $count = $query->num_rows();

  if ($count > 0) {
   $lastv = $query->row('last_visit');
   $currentv = new DateTime('NOW');

   $consulta = $this->db->query('SELECT TIMESTAMPDIFF(MINUTE,"$currentv","$lastv" ) as timediff FROM visitas WHERE ip_addr = "$new_ip"',FALSE)->row();

   return $consulta; //to check the var_dump result

   if ($consulta->timediff > 10) {
    $this->db->set('cant_visit', 'cant_visit+1', FALSE);
    $this->db->set('last_visit', 'NOW()', FALSE);
    $this->db->update('visitas');
   }

  } else {
   $data = array(
    'ip_addr'=>$new_ip
   );
   $this->db->set('cant_visit', 'cant_visit+1', FALSE);
   $this->db->set('first_visit', 'NOW()', FALSE);
   $this->db->insert('visitas', $data);
  }
}

and this is the SQL table code:

Code:
CREATE TABLE IF NOT EXISTS `visitas` (
  `ip_addr` varchar(20) NOT NULL,
  `first_visit` datetime NOT NULL,
  `last_visit` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `cant_visit` int(200) NOT NULL,
  PRIMARY KEY (`ip_addr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `visitas` (`ip_addr`, `first_visit`, `last_visit`, `cant_visit`) VALUES
('127.0.0.1', '2013-09-04 12:46:00', '2013-09-12 11:20:01', 138);

Thanks for your help!
#7

[eluser]CroNiX[/eluser]
What is this doing?
Code:
$lastv = $query->row('last_visit');
If you pass a parameter to row(), the first one would be the row number, not a column name.

When troubleshooting query problems, you should check what the actual sql you're executing by using echo $this->db->last_query(). In this case, just before you return $consulta;
#8

[eluser]CroNiX[/eluser]
This query is tested and works with a real table in my db. You can adjust as needed for your query. You still need to alias the result like in my example.
Code:
//execute the query
$q = $this->db->query("SELECT TIMESTAMPDIFF(HOUR, '2013-08-26 09:14:10', last_login ) as timediff FROM users WHERE last_ip = '123.456.789.123'");

//grab the row, and the aliased field from it
$time_diff = $q->row()->timediff;  
echo $time_diff;
The only difference here, which I didn't write the first time, is that you need to execute the query before getting the row(). That's why you got the error. I'm used to writing active record queries (doesn't use db::query()) and what I wrote would have worked for that style, but not a manual query.

It's probably not working for you due to what I mentioned in my previous post. You are doing things that aren't in the manual, so by the time you get to the query the variables you are using in your final query are not what you expect. Again, use db::last_query() to see the actual query you are executing, and var_dump your variables to make sure they are valid.
#9

[eluser]CroNiX[/eluser]
Here's how I would have written it using Active Records, which allows you to do it all with one statement. Tested and working.
Code:
$last_ip = '123.456.789.123';

$timediff = $this->db
->select("TIMESTAMPDIFF(HOUR, '2013-08-26 09:14:10', last_login ) as timediff", FALSE) //false here so it won't protect identifiers
->where('last_ip', $last_ip)
->get('users')
->row()
->timediff;

echo $timediff;
#10

[eluser]diegoalmesp[/eluser]
Thanks CroNiX!!
finally! it works like a charm! :cheese:
This is the final code I'm using:
Code:
public function new_visit($ip)
{
  $new_ip = $ip;

  $this->db->from('visitas');
  $this->db->where('ip_addr', $new_ip);
  $query = $this->db->get();
  $count = $query->num_rows();

  if ($count > 0) {
   $currentv = new DateTime('NOW');
   $currentv = $currentv->format('Y-m-d H:i:s'); // had to format this

   $q = $this->db->query("SELECT TIMESTAMPDIFF(HOUR, '$currentv', last_visit) as timediff FROM visitas WHERE ip_addr = '$new_ip'");

   $time_diff = $q->row()->timediff;  
   // return $time_diff; <-- just for testing

   if ($time_diff > 10) {
    $this->db->set('cant_visit', 'cant_visit+1', FALSE);
    $this->db->set('last_visit', 'NOW()', FALSE);
    $this->db->update('visitas');
   }

  } else {
   $data = array(
    'ip_addr'=>$new_ip
   );
   $this->db->set('cant_visit', 'cant_visit+1', FALSE);
   $this->db->set('first_visit', 'NOW()', FALSE);
   $this->db->insert('visitas', $data);
  }
}

Thanks to everyone for your help, I'm a newbie programmer so I really appreciate your time!

:coolsmile:




Theme © iAndrew 2016 - Forum software by © MyBB