Jump to content

need help Querying by Date


afaaro
Go to solution Solved by afaaro,

Recommended Posts

I have tried this one it only works for today 

if($data = dbarray(dbquery("SELECT post_created FROM ".POST." WHERE post_created >= ".strtotime('today')." ORDER BY post_created DESC"))){
echo $data['post_name']; 

}elseif($data = dbarray(dbquery("SELECT post_created FROM ".POST." WHERE post_created <= date_sub(CURDATE(),interval -1 day) ORDER BY post_created DESC"))){
echo $data['post_name']; 
}
Link to comment
Share on other sites

This any good to you? Just change it to how you need it.

 

Reference: http://au1.php.net/time

<?php
$nextWeek = time() + (7 * 24 * 60 * 60);
                   // 7 days; 24 hours; 60 mins; 60secs
echo 'Now:       '. date('Y-m-d') ."\n";
echo 'Next Week: '. date('Y-m-d', $nextWeek) ."\n";
// or using strtotime():
echo 'Next Week: '. date('Y-m-d', strtotime('+1 week')) ."\n";
?>
Link to comment
Share on other sites

Hi Ansego sorry about late reply

 

I have tried but it isn't working and this is the code I want to display only last 2 days ago posts

if ($result = GetList("SELECT * FROM ".DB_PREFIX."mediaPost WHERE post_category=$cid AND post_enabled=1 AND post_created >= '".date('Y-m-d', strtotime('-2 day'))."' ORDER BY post_created DESC LIMIT 20")) {
	foreach ($result as $row) {
		echo "<br />";
		echo "- ".$row['post_name']." - ".showdate('%d %B %Y',$row['post_created'])."<br>";
		
	}
}
Link to comment
Share on other sites

If you want all the posts from the last two days, then you would use this query:

SELECT * 
FROM mediaPost
WHERE
   post_category=$cid
   AND post_enabled=1
   AND post_created >= DATE_SUB(NOW(), INTERVAL 2 DAY)
The post_created column needs to be of a DATE, DATETIME, or TIMESTAMP type for this to work.

 

If no posts were made in the last two days, then that query would return nothing. If you want to return all posts from 2 days prior to the most recent post (which is what your original post hints at) then you need to use a sub-query to determine the most recent post first. For example:

SELECT * 
FROM mediaPost
WHERE
   post_category=$cid
   AND post_enabled=1
   AND post_created >= (
      SELECT DATE_SUB(MAX(post_created), INTERVAL 2 DAY)
      FROM mediaPost
      WHERE
          post_category=$cid
          AND post_enabled=1
   )
Link to comment
Share on other sites

Hi Kicken, Thank you for your reply as you mentioned The post_created column needs to be of a DATE, DATETIME, or TIMESTAMP type for this to work 

 

Mine is integer does it still work or ?

 

No it will not. Your first post that showed a QUERY you said worked only for the last day appeared to indicate that the post_created field was some type of date field. If not, you should change it to one.

 

Although, I don't think Kicken's last post will give you what you want. It will find the most recent post across all users and then only select posts that were created on that day. I think you were stating that you want the posts for the most recent day - for each user. So, if bob's most recent post was yesterday, you want all of his posts from yesterday. But, if dave's most recent post was from two days ago you want all of dave's posts from two days ago.

 

So, if I am understanding your request correctly, I think this would work. Note: I don't know the correct field name to identify the user, so I guessed. Also, I assumed the post_created will be a timestamp type field.

 

SELECT *.source
FROM mediaPost AS source
JOIN (
  SELECT user_id, MAX(DATE(post_created)) AS post_date
  FROM mediaPost
  WHERE post_category = $cid
    AND post_enabled = 1
  GROUP BY user
) as last
  ON source.user_id = last.user_id AND DATE(source.post_created) = last.post_date

WHERE post_category = $cid
  AND post_enabled = 1
Link to comment
Share on other sites

Its not working

 

SELECT *.source
FROM fusion38t7q_mediapost AS source
JOIN (
  SELECT MAX(DATE(post_created)) AS post_date
  FROM fusion38t7q_mediapost
  WHERE post_enabled = 1
) as last
  ON DATE(source.post_created) = last.post_date
 
WHERE post_enabled = 1
Edited by afaaro
Link to comment
Share on other sites

I would add a new field (e.g. post_created_temp) then run a query to populate the new field with appropriate dates. Finally, remove the original field and rename the new one to "post_created". That will cover the transition process, but you'll also need to modify any operations that currently read/write to/from that field to use the correct date format.

 

 

I don't know what you are storing in that field currently to say how you can populate the value for the new field. But, if you are using a PHP Timestamp you can run a single query to populate the new field. Example

 

UPDATE fusion38t7q_mediapost
SET post_created_temp = FROM_UNIXTIME(post_created)
Link to comment
Share on other sites

As I said, I have no clue what you are currently storing in that field. I only made an assumption that you may be using a PHP timestamp. I ran a quick test where one INT column contained PHP timestamp values such as '1395254736'. Then I ran a query just like the one above to populate another column that was a date type and I got the expected results.

 

Here is what I had before I ran the UPDATE query

post_created | post_created_temp
---------------------------------
1388601010      NULL
1359830410      NULL
1331232610      NULL
1395254736      NULL
1297109410      NULL

I then ran this query

UPDATE test_table
SET post_created_temp = FROM_UNIXTIME(post_created)

The results after running that query were

post_created | post_created_temp
---------------------------------
1388601010      2014-01-01 12:30:10
1359830410      2013-02-02 12:40:10
1331232610      2012-03-08 12:50:10
1395254736      2014-03-19 13:45:36
1297109410      2011-02-07 14:10:10

Those are the correct date/times that I used to create the numerical timestamps.

Edited by Psycho
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.