need help Querying by Date

[afaaro]

Hi everyone 

 

Is there any way i can retrieve posts by date like:  if theirs post today list it and if not list yesterrday's post if not 2days ago's post

 

 

thank you.

Edited March 15, 2014 by afaaro

[ginerjm]

Only if you have a date in the records that have been posted.

[afaaro]

thank you for reply and Yes I have date so is it possible

[ginerjm]

If I read your question correctly, you need to do a query to select the records that match the max(date) of each user. It will probably a query having a sub-query which you will have to read up on.

[afaaro]

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']; 
}

[ginerjm]

What is "dbarray"?  Not a function I can find in the manual.

 

Your code only runs one of those queries.  Not surprising it only retrieves the ones for today.  It also only retrieves one field.

[afaaro]

mysql_fetch_array

[ginerjm]

huh?

[Ansego]

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";
?>

[afaaro]

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>";
		
	}
}

[kicken]

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
   )

[afaaro]

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 ?

[Psycho]

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

[afaaro]

Hi Psycho thank you for your reply

 

my date type is integer will it work

[Psycho]

Hi Psycho thank you for your reply

 

my date type is integer will it work

 

Don't know. Any fields that will contain 'dates' should be a type that was meant for storing . . . dates. Change the field type and store the dates correctly.

[afaaro]

post_created POSTTYPE is INT and I want to query only today's post and another query yesterdays posts

[Psycho]

post_created POSTTYPE is INT and I want to query only today's post and another query yesterdays posts

 

I've already provided a solution which you don't want to implement. Good luck.

[afaaro]

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 March 19, 2014 by afaaro

[Psycho]

 

Its not working

 

I'm not surprised. As I already stated you should store your date values as date types. Part of my solution requires that you fix your database.

[afaaro]

how can i change without losing the date data when changing to datetime

[Psycho]

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)

[afaaro]

It updated wrong date  2038-01-19 03:14:07

[Psycho]

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 March 19, 2014 by Psycho

[afaaro]

Hi Psycho Thank you for your support 

 

its working now using this way

SELECT * FROM ".DB_PREFIX."mediaPost WHERE post_created <= UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL -1 DAY))

[Psycho]

So you decided not to fix the problem and use a band-aid. Good for you. Just don't go asking for my help when you need to use some other DATE functions on that field in the future.