-
tp45
Junior Member
-
Posts: 36
Threads: 9
Joined: Aug 2019
Reputation:
0
Hello
Am trying to count today's posts it returns 0 every time
Here is my code:
PHP Code: public function today_post(){ $datebytimezone = new DateTime("now", new DateTimeZone('UTC')); $date = $datebytimezone->format('Y-m-d'); $query = $this->db->get_where('post', array('posted_at' => $date, 'post_closed' => 0)); return $query->num_rows(); }
Thank in advance.
-
tp45
Junior Member
-
Posts: 36
Threads: 9
Joined: Aug 2019
Reputation:
0
09-17-2019, 01:05 PM
(This post was last modified: 09-17-2019, 01:09 PM by tp45.)
(09-17-2019, 11:53 AM)jreklund Wrote: And how does your database look? Code: GO
/****** Object: Table [dbo].[post] Script Date: 2019/09/17 22:00:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[post](
[post_id] [bigint] IDENTITY(1,11) NOT NULL,
[user_id] [bigint] NOT NULL,
[post] [nvarchar](max) NULL,
[posted_at] [datetime] NOT NULL,
[comment_count] [int] NOT NULL,
[like_count] [int] NOT NULL,
[privacy] [varchar](30) NOT NULL,
[post_closed] [bit] NOT NULL,
[image] [varchar](max) NULL,
CONSTRAINT [PK_post] PRIMARY KEY CLUSTERED
(
[post_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[post] WITH CHECK ADD CONSTRAINT [FK_post_user] FOREIGN KEY([user_id])
REFERENCES [dbo].[users] ([user_id])
GO
ALTER TABLE [dbo].[post] CHECK CONSTRAINT [FK_post_user]
GO
dumped data is like this
post_id user_id post posted_at comment_count like_count privacy post_closed image
1 5 test 2019-07-26 20:23:08.000 0 0 public 1
12 5 test 2 #me @nthethe5933 2019-07-26 21:42:12.000 0 0 public 1 assets/posts/nadine-shaabana-804126-unsplash.jpg
23 7 first last 2019-07-26 23:17:53.000 0 0 public 1
34 5 test to delete 2019-07-31 00:03:28.000 0 0 public 1
45 5 ok deleting is fun 2019-07-31 00:22:53.000 0 0 public 1
56 5 Hello world 2019-07-31 00:41:50.000 0 0 public 1
67 7 ðŸÂ Hey son be healthy 🎠2019-07-31 00:46:21.000 0 0 public 0 apple_drops_spray_shadow_112225_1280x720.jpg
78 5 test del agn 2019-07-31 00:49:20.000 0 0 public 1
89 5 2019-08-07 07:31:48.000 0 0 public 1 assets/posts/eye-for-ebony-399310-unsplash.jpg
100 5 Test this 2019-08-07 07:33:20.000 0 0 public 1
111 5 Hello world 2019-08-07 07:41:17.000 0 0 public 1
122 5 #FightForAfrica @nthethe5933 2019-08-07 07:43:52.000 0 0 public 0 kal-visuals-644747-unsplash.jpg
133 5 2019-08-07 08:45:37.000 0 0 public 1 amanda-jones-1277189-unsplash.jpg
166 5 ????
Stuck them
?????
??? 2019-08-08 16:07:10.000 0 0 public 1
177 5 ðŸŒworld 2019-08-08 16:40:12.000 0 0 public 1 382694-large-sniper-wallpaper-1920x1080-windows-xp.jpg
188 5 ðŸ‡ðŸ’ðŸ“ðŸŽðŸÂ #Healthy is good.. @nthethe5933 2019-08-08 18:01:31.000 0 3 public 0 elena-g-L50azdkALCA-unsplash.jpg
199 5 ????Hooooohaaaa
Ooo lala? 2019-08-08 18:12:13.000 0 0 public 1
210 7 ❤️?? Happy coding 2019-08-08 19:29:05.000 0 1 public 1
221 5 ? 2019-08-08 20:41:59.000 0 0 public 1
232 7 ???????Well it works 2019-08-09 02:15:48.000 0 0 public 1
243 7 #itsworking 😄😂😂🎉 so cool.. 2019-08-09 02:26:38.000 1 1 public 0 IMG_20150322_00152218.jpg
254 5 ♈️♻️♊️?? 2019-08-09 06:38:32.000 0 0 public 1
265 5 😂😂😂😂😂 😅 2019-08-11 01:16:09.000 0 1 public 0 cover-image.jpg
276 5 ?? 2019-08-12 02:08:28.000 0 0 public 1 assets/posts/download.png
287 5 ?❤️ #tag @topollo57 2019-08-15 05:52:13.000 0 0 friendsonly 1 assets/posts/mink-mingle-1381133-unsplash.jpg
298 11 🎉My first post ðŸ˜ðŸ‘ŒðŸ˜‹â˜€ï¸ 2019-08-20 08:47:42.000 0 0 public 0 smoke_shroud_clot_126551_1280x720.jpg
309 11 🎉🎊🎋🎈Fun fun fun 2019-08-20 08:49:09.000 2 0 public 0
320 13 #protect @siphe4573 Happy time 😂👠2019-08-20 08:57:23.000 0 0 public 0
331 13 #coolcat 2019-08-20 08:58:21.000 0 0 public 1 cat_black_blueeyed_130470_1280x720.jpg
342 15 #️⃣??♻️?☔️
??????
?????? 2019-08-20 09:04:46.000 0 0 public 0
353 15 ?????? 2019-08-20 09:14:21.000 0 0 public 0 brooke-lark-08bOYnH_r_E-unsplash.jpg
364 15 2019-08-20 09:15:29.000 0 0 public 0 kwang-mathurosemontri-fY1ECB1RCd0-unsplash.jpg
375 5 Good test for me life 😂😠great 2019-08-20 11:02:41.000 0 0 public 0 0_TFmFug4zhzfbKsd5.jpg
452 20021 🙊🙈 2019-09-09 02:23:49.000 0 1 public 0 brooke-lark-08bOYnH_r_E-unsplash.jpg
496 5 😘😡â¤ï¸â˜”ï¸ðŸ»ðŸš˜ðŸ»
🎉 🎊🎋ðŸŽðŸ’ðŸðŸ˜
2019-09-10 09:10:08.000 0 1 public 0 0
507 5 ðŸ™hello 2019-09-10 10:47:40.000 0 0 public 1 0
518 20021 i can upload text and images now 🙊🔥 2019-09-10 11:04:00.000 1 0 public 0 eye-for-ebony-399310-unsplash.jpg
529 20021 😨😱 2019-09-10 11:08:56.000 2 1 public 0 281836-gaming-wallpaper-1920x1080-macbook.jpg
540 20021 2019-09-10 21:22:55.000 0 0 public 1 382682-most-popular-sniper-wallpaper-1920x1200.jpg
595 11 2019-09-14 13:46:10.000 0 0 public 1 9cf65c5b223ef81141a9dc61b2d2e398.jpg
606 13 test today posted posts 2019-09-17 15:15:22.000 0 0 public 0 0
on the datetime field i inset a date and time for uct timezone like
PHP Code: $datebytimezone = new DateTime("now", new DateTimeZone('UTC') );
$date = $datebytimezone->format('Y-m-d H:i:s');
-
demyr
Senior Member
-
Posts: 313
Threads: 17
Joined: Sep 2018
Reputation:
6
Doesn't TimeStampDiff approach help?
PHP Code: ->where('TIMESTAMPDIFF(DAY, start_date, now()) <= 180')
This example shows items which were published in the last 180 days = 6 months here.
I use this for membership expirations. If member's period is more than 6 months, that member cannot see videos or any content etc.
-
tp45
Junior Member
-
Posts: 36
Threads: 9
Joined: Aug 2019
Reputation:
0
(09-17-2019, 01:25 PM)demyr Wrote: Doesn't TimeStampDiff approach help?
PHP Code: ->where('TIMESTAMPDIFF(DAY, start_date, now()) <= 180')
Thanks it works.
Is it possible to change the 180 so that it shows posts for today only ?
i changed 180 to 24 it shows me many posts than expect, i just want today's posts and since i'm using sql server i changed TIMESTAMPDIFF to DATEDIFF
-
demyr
Senior Member
-
Posts: 313
Threads: 17
Joined: Sep 2018
Reputation:
6
As I arranged it for Days probably you got results for the last 24 days .
Change it to 1, and check the output please.
( another option: for Hour you must write Hour, mine was for DAY.)
-
tp45
Junior Member
-
Posts: 36
Threads: 9
Joined: Aug 2019
Reputation:
0
09-17-2019, 04:33 PM
(This post was last modified: 09-17-2019, 04:39 PM by tp45.)
(09-17-2019, 02:16 PM)demyr Wrote: As I arranged it for Days probably you got results for the last 24 days .
Change it to 1, and check the output please.
(another option: for Hour you must write Hour, mine was for DAY.) sql server doesn't have HOUR they only have MONTH, DAY etc
but i think DAY is correct( https://docs.microsoft.com/en-us/sql/t-s...erver-2017) but i have to fix it correctly let me try.
-
tp45
Junior Member
-
Posts: 36
Threads: 9
Joined: Aug 2019
Reputation:
0
09-18-2019, 10:40 AM
(This post was last modified: 09-18-2019, 10:47 AM by tp45.)
(09-18-2019, 10:19 AM)jreklund Wrote: Here are a couple of ways you can write that SQL. If you can, you should write them as plain SQL, it's faster.
PHP Code: ok thanks @jreklund let me try
(09-18-2019, 10:40 AM)tp45 Wrote: (09-18-2019, 10:19 AM)jreklund Wrote: Here are a couple of ways you can write that SQL. If you can, you should write them as plain SQL, it's faster.
public function today_post(){
$datebytimezone = new DateTime("now", new DateTimeZone('UTC'));
$date = $datebytimezone->format('Y-m-d');
$query = $this->db->get_where('post',
array(
'posted_at >=' => "{$date} 00:00:00",
'post_closed' => 0
)
);
return $query->num_rows();
}
This one works the others are have CURDATE() in which i get an error like curdate() not recofnized as an intenally or inbuld function.
Thank you so much @ jreklund as always, you a legend.
|