DevStud DevStud - 6 months ago 12
MySQL Question

trending tag in a week like twitter wont work

I am trying to make a hit list like twitter trending hashtags in one week. The following code working for just

1 DAY
but i want to change it
1 WEEK
how can i do that. I still tryed to change it
INTERVAL 1 WEEK
but didn't worked. Anyone can help me here ?

$query = mysqli_query($this->db,"SELECT * FROM posts WHERE FROM_UNIXTIME(created) > CURRENT_DATE AND FROM_UNIXTIME(created) < CURRENT_DATE + INTERVAL 1 DAY AND Tag != ''") or die(mysqli_error($this->db));

Answer

I think you are select the interval in wrong way ) if you want a week for tomorrow and a week use (week o 7 day

  SELECT * 
  FROM posts 
  WHERE FROM_UNIXTIME(created) > CURRENT_DATE 
  AND FROM_UNIXTIME(created) < (CURRENT_DATE + INTERVAL 1 WEEK ) 
  AND Tag != ''

  SELECT * 
  FROM posts 
  WHERE FROM_UNIXTIME(created) > CURRENT_DATE 
  AND FROM_UNIXTIME(created) < (CURRENT_DATE + INTERVAL 7 DAY ) 
  AND Tag != ''

Otherwise if you want select a week before use

  SELECT * 
  FROM posts 
  WHERE FROM_UNIXTIME(created) > (DATE_SUB(NOW(), INTERVAL 1 WEEK) 
  AND   FROM_UNIXTIME(created) <= CURRENT_DATE 
  AND Tag != ''

But if the date is already in datetime format use

SELECT * 
FROM posts 
WHERE created > CURRENT_DATE 
AND created < (CURRENT_DATE + INTERVAL 7 DAY ) 
AND Tag != ''
Comments