Page 1 of 2

PHP query that counts all records in the past month

Posted: Tue Feb 21, 2017 4:22 pm
by diseman
Finding a lot of examples for MySQL query, but I'm looking for a PHP solution.

I want to count all the rows in a table where groop is = '2' and the encounter_date I've stored is less than one month old.

I have a table column called: encounter_date

Date format = YYYY-MM-DD

I prefer a solution that could use that format although I'm open to changing to Unix time if I have to.

Based on this strtotime example below, I'll have to switch to Unix time, but would prefer to not.

Anyways, I could really use some help with this if anyone knows how to do this.

Code: Select all


$query = "SELECT encounter_date FROM crisis WHERE groop = '2'  ";
$result = mysqli_query($con, $query) or die (mysqli_error($con));

	while ($row = mysqli_fetch_object($result)) {

		$encounter_date = $row->encounter_date;

		     if(strtotime($encounter_date) < strtotime('1 month ago')) {

				$count = mysqli_num_rows($result);
	}
	}

	echo $count;

Thank you in advance for any help you can provide.

Re: PHP query that counts all records in the past month

Posted: Tue Feb 21, 2017 5:17 pm
by Celauran

Code: Select all

SELECT COUNT(*) FROM crisis WHERE groop = '2' AND encounter_date <= NOW() - INTERVAL 1 MONTH

Re: PHP query that counts all records in the past month

Posted: Tue Feb 21, 2017 7:08 pm
by diseman
Hello Celauran ... old friend. LOL Can always count on you for help.

A little confused because I was looking for a PHP solution and this looks like a MySQL query solution. Am I wrong?

I assumed I was suppose to change my date format in DB to Unix time. Was that wrong?

Finally, because of my confusion with your answer, all I came up with was the following:

Code: Select all


$query = "SELECT COUNT(*) FROM crisis WHERE groop = '2' AND encounter_date <= NOW() - INTERVAL 1 MONTH  ";
$result = mysqli_query($con, $query) or die (mysqli_error($con));

echo $count;

I'm getting 2 records returned. I do have two records, but I should only be getting one. One record I made Unix time of today. The 2nd record I made Unix time of more than a month ago.

Today: 1487653200
More than a month ago: 1485047258

Sorry to ask you to spell it out for me, but this task is completely alien to me as I've never seen it or done it before.

Thanks again for any help...

Re: PHP query that counts all records in the past month

Posted: Wed Feb 22, 2017 5:29 am
by Celauran
Huh. I had assumed you were storing your dates as dates.

Code: Select all

mysql> SELECT NOW() AS today, NOW() - INTERVAL 1 MONTH AS last_month;
+---------------------+---------------------+
| today               | last_month          |
+---------------------+---------------------+
| 2017-02-22 06:25:49 | 2017-01-22 06:25:49 |
+---------------------+---------------------+
1 row in set (0.01 sec)
I suppose you could wrap in UNIX_TIMESTAMP. Why aren't you storing dates as dates?

Re: PHP query that counts all records in the past month

Posted: Wed Feb 22, 2017 9:06 am
by diseman
Thank you very much Celauran.

I am using date in the db as 2017-02-22.

It seems the problem was with the <=, which should have been >=

Here's the final code for someone else looking for help:

Code: Select all

$query = "SELECT encounter_date FROM crisis WHERE groop = '".$row['groop']."' AND encounter_date >= NOW() - INTERVAL 1 MONTH  ";
	
$result = mysqli_query($con, $query) or die (mysqli_error($con));

$records = mysqli_num_rows($result);
Until next time ... :)

P.S. Yes, I know groop is group, but 'group' is a reserved word/name and I really wanted to use that word. I still have to test if I could have simply created the column using `group` and if that would have allowed me to do it.

Re: PHP query that counts all records in the past month

Posted: Wed Feb 22, 2017 9:14 am
by Celauran
¯\_(ツ)_/¯
I'd have used `grouping`

Re: PHP query that counts all records in the past month

Posted: Wed Feb 22, 2017 9:17 am
by diseman
Sure, but when it comes to PHP, you're a lot smarter than us simple folk. ;)

Re: PHP query that counts all records in the past month

Posted: Tue Mar 07, 2017 12:42 pm
by diseman
Hi Celauran,

So, I've moved on from this topic for a while, but am back to it now 'cause I want to do something else with this query...

Code: Select all

$query = "SELECT date, username, provider FROM assessments WHERE date <= CURDATE() - INTERVAL 6 MONTH";
This works fine...

However, I think I need to do a query within this query, but unsure how and if that's actually true, so I wanted to run it by you before doing research and spending a lot of time on this.

Basically, I want to:

1. query a table to find entries where `date` is greater than six months.
2. within all results that are older than 6 months, group all the records that belong to an individual username and only use the newest record, which probably requires doing a ORDER BY `date` DESC LIMIT 1 -> I believe.

To be clear in #2, there will be many records for each username (email address) in this table. Imagine 50 usernames each with 5 records; 1 record inserted each 6 months. That means in 2 year's time, each username will have 4 records that are greater than 6 months old. I do NOT want to find each record more than 6 months old; only that last record entered by a username that is more than 6 months old. Obviously, as time goes on they will all be more than 6 months old... that's why I only want the most recent one that just turned 6 months old assuming this was a daily cron job.

Can you show me how that query might look?

Thank you.

Re: PHP query that counts all records in the past month

Posted: Tue Mar 07, 2017 9:07 pm
by diseman
and here is another way of explaining it:

I have a table `assessments` as shown below

[text]
id date username provider
-------------------------------------------------------------------
1 2015-09-15 me1@me.com provider_1_name@me.com
2 2016-03-15 me1@me.com provider_1_name@me.com
3 2016-09-07 me1@me.com provider_1_name@me.com
4 2015-09-07 me2@me.com provider_2_name@me.com
5 2016-03-07 me2@me.com provider_2_name@me.com
6 2016-09-07 me2@me.com provider_2_name@me.com
[/text]

So, imagine provider 1 & 2 are high school counselors who need to get an email to be reminded to counsel a student 6 months after the last time the student was counseled.

So, what I want/need to figure out is how can I query all the records for dates that are beyond 6 months old? I did it like this:

Code: Select all


$query = "SELECT date, username, provider FROM assessments WHERE date <= CURDATE() - INTERVAL 6 MONTH";
$results = mysqli_query($con, $query) or die (mysqli_error($con));
     
     while ($row = mysqli_fetch_array($results)) {

     $provider = $row['provider'];  // NOW I HAVE COUNSELOR'S EMAIL FOR OTHER QUERY
     $students = $row['username'];  // NOW I HAVE STUDENT'S EMAIL FOR OTHER QUERY
Works great!

However, over time every record will be older than 6 months and a reminder would have been sent at the appropriate time, so I only want to get the last record for EACH username, so I can send an email to the counselor letting them know it's time for another assessment just as it hits 6 months. In the table above that means I only want ID 3 and ID 6.

I can get the last record like this:

Code: Select all

$query = "SELECT DISTINCT username FROM assessments WHERE username = '".$students."'
ORDER BY date DESC
LIMIT 1";

$results = mysqli_query($con, $query) or die (mysqli_error($con));
Problem is... I don't know how to put these two queries together. I need the first query to get the last record for each username that is older than 6 months and THEN the second query to get the username and provider for each of the results from the first query. Can anyone help me with this?

Re: PHP query that counts all records in the past month

Posted: Wed Mar 08, 2017 6:12 am
by Celauran
Something like this, perhaps?

Code: Select all

SELECT a.*
FROM assessments AS a
INNER JOIN (
	SELECT MAX(`date`) AS latest, username, provider
	FROM assessments
	WHERE `date` <= CURDATE() - INTERVAL 6 MONTH
	GROUP BY username
) AS b
ON a.`date` = b.latest AND a.username = b.username AND a.provider = b.provider

Re: PHP query that counts all records in the past month

Posted: Wed Mar 08, 2017 8:11 am
by diseman
As always... thank you again for your help Celauran.

I'm a little baffled by the WHILE statement at the bottom.

I'm expecting the WHILE statement will get the first $row provider & username and then go to the include file to send the email.

Then, it would loop and get the next $row's provider & username and then again send an email.

However, that's not happening.

If I keep the INCLUDE inside the WHILE statement, I get one student name, and if I move it just outside the WHILE statement, I get the other student name.

I need to assign both username and provider to variables, so I can use the names in the email. I think I'm doing that, but not looping properly.

Code: Select all

$query = "SELECT a.*
			FROM assessments AS a
			INNER JOIN (
				SELECT MAX(`date`) AS latest, username, provider
				FROM assessments
				WHERE `date` <= CURDATE() - INTERVAL 6 MONTH
				GROUP BY username
			) AS b
			ON a.`date` = b.latest AND a.username = b.username AND a.provider = b.provider";

	       $results = mysqli_query($con, $query) or die (mysqli_error($con));

		    while ($row = mysqli_fetch_array($results))	{

			    $provider = $row['provider']; // I NEED THIS FOR THE EMAIL
			    $students = $row['username']; // I NEED THIS FOR THE EMAIL

			    include("../_includes/email/cron_assessment.php"); // THIS SHOULD RUN FOR EACH $ROW
		    }
I've been spending the last 1.5 hours looking at possibly using FOREACH, but not finding a method that works.

Would be grateful if you could tweak this for me, so I can see how it's done.

Thank you in advance,

Michael

Re: PHP query that counts all records in the past month

Posted: Wed Mar 08, 2017 8:19 am
by Celauran
Have you examined the results of the query? Are they correct? If not, what is the expected vs actual result set (anonymize whatever you need to, of course)? If the results are correct, what's going on inside that include?

Re: PHP query that counts all records in the past month

Posted: Wed Mar 08, 2017 8:24 am
by diseman
I believe the results are correct.

The include file is just the email code that is taking the username and provider and using it in a query to get the actual names from other tables.

As far as I can tell it's working properly EXCEPT for the fact it's only sending one email, when in fact there are two records.

Re: PHP query that counts all records in the past month

Posted: Wed Mar 08, 2017 8:28 am
by Celauran
diseman wrote:I believe the results are correct.
Confirm. Print out the results as you iterate over them if need be.
diseman wrote:The include file is just the email code that is taking the username and provider and using it in a query to get the actual names from other tables.

As far as I can tell it's working properly EXCEPT for the fact it's only sending one email, when in fact there are two records.
Should that really even be an include file? Sounds like a mail function that can be called would do better. Included files will be included once for each iteration, so it should be running repeatedly.

Re: PHP query that counts all records in the past month

Posted: Wed Mar 08, 2017 8:31 am
by diseman
Ok, that's what I thought. Let me look at this some more before coming back to you again. Thank you