MySQL statement with ">" or "<" parameter

Question

Is there a way how to fetch all data from table where number is higher than something.

In my case I am using UNIX time in database to be able to easily compare data. Right now I would like to count every login which is higher then current UNIX time minus 24 hours.

Is there a way how I can write it? I was thinking about something like this but not sure how can I write SQL statement with < or >.

function getDailyOnline()
{
    return $this->db->table('users')
        ->where('last_login', $post->id);
}

I need SQL command, don't need to use Nette Framework as I do in my "example".

The ORM you are using probably provides a way, but since we don't know what that ORM is, we can't tell you what that way is. — Quentin, Jan 12 '15 at 16:44
this is Nette framework, don't care about that, I'm fine with basic SQL command — Andurit, Jan 12 '15 at 16:44

score 4 · Accepted Answer · answered Jan 12 '15 at 16:57

4

You can simply try this:

$this->db->table('users')->where('last_login > ', $post->id);

answered Jan 12 '15 at 16:57

Riad

3,822
5
28
39

Is this missing a `?` in the `where` clause? – tadman Jan 12 '15 at 17:17
It's nette database. It allows you to not write ?, it will append them automatically. – hrach Jan 12 '15 at 19:58
This is actually really good point, thanks for that. how ever at the end based on this and other questions i create my final answer: `return $this->db->fetchPairs('SELECT COUNT(*) last_login FROM users WHERE last_login > UNIX_TIMESTAMP() - (24 * 60 * 60)');` – Andurit Jan 13 '15 at 08:46

score 2 · Answer 2 · answered Jan 12 '15 at 17:01

2

SELECT COUNT(*) AS cnt
  FROM users
 WHERE last_login > (UNIX_TIMESTAMP() - (24 * 60 * 60));

answered Jan 12 '15 at 17:01

shmosel

49,289
6
73
138

please edit your answer and include some explanation about why this solves the OP problem. Code-only answers are sometimes good enough, but code+explanation answers are always better. – Barranka Jan 12 '15 at 17:28
@Barranka I don't see anything that would specifically require explanation; it's pretty basic SQL that directly responds to OP's question. If OP has any particular problems with my answer, I'd be happy to explain, or retract if necessary. – shmosel Jan 12 '15 at 17:44
Thumb up, your answer was usefull for me :) – Andurit Jan 13 '15 at 08:47

score 2 · Answer 3 · answered Jan 12 '15 at 17:06

2

you want to do something like this:

SELECT COUNT *
  FROM users
  WHERE last_login > (UNIX_TIMESTAMP() - (24 * (3600)));

This will select all the row with a last_login older than 24 hours(1day).

answered Jan 12 '15 at 17:06

william.eyidi

2,315
4
27
39

1

Thumb up, your answer was usefull for me :) – Andurit Jan 13 '15 at 08:47

score 0 · Answer 4 · answered Jan 16 '15 at 20:16

0

I just tried and you can go for this solution:

$this->db->table("users")
    ->where('last_login > ', $post->id);

cheers

answered Jan 16 '15 at 20:16

william.eyidi

2,315
4
27
39

MySQL statement with ">" or "<" parameter

4 Answers4