PHP MYSQL - Check if any dates have passed.

timmylogue · April 9, 2015, 3:36pm

Hey guys,

Any help is appreciated!

I need a way to display from my database if any dates (see image below)

in column “registered” have passed 30 days (expired) from today’s date and If so, I will need it to email me the whole row that expired.

Crap… I hope that made sense! haha.

Thanks!

Tim

kingcoda · April 10, 2015, 6:06pm

timmylogue said
in column “registered” have passed 30 days (expired) from today’s date and If so, I will need it to email me the whole row that expired.

Crap… I hope that made sense! haha.

Thanks!

Tim

Yes made sense … This should work:


SELECT * from TABLE
WHERE registered NOT BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

Hope it helps

timmylogue · April 10, 2015, 6:40pm

Thank you!

codents · April 11, 2015, 10:15am

You can also do this with PHP.


$sql = "SELECT * FROM yourtable";
$query = mysqli_query($dbconnectvariable, $sql);
if($query){
  while($row = mysqli_fetch_assoc($query)){
  $date = $row['yourdatefield'];
  $datenow = date("Y-m-d");

     if($datenow > $date){

     DO SOMETHING HERE

     }

  }

}

Inside the WHILE function, you can use $row to take each of the fields and pass them into variables, then email them to whatever email you want.

timmylogue · April 11, 2015, 6:32pm

codents said
You can also do this with PHP.
$sql = "SELECT * FROM yourtable"; $query = mysqli_query($dbconnectvariable, $sql); if($query){ while($row = mysqli_fetch_assoc($query)){ $date = $row['yourdatefield'];
Inside the WHILE function, you can use $row to take each of the fields and pass them into variables, then email them to whatever email you want.

Thank you!!

timmylogue · April 13, 2015, 3:47pm

timmylogue said
codents said
You can also do this with PHP.
$sql = "SELECT * FROM yourtable"; $query = mysqli_query($dbconnectvariable, $sql);

Thank you!!

Hey!

If you could help me out with one more thing! How would I echo out the results for this? I guess I would use some loop too!

screenshot

I’m trying to display the “username(s)” of whoever has the value “expired” in their row. I have tired to Google some help before posting but I just can’t find out how to do this!

I was hoping this would work? I’m I close?

$result = mysql_query("SELECT * FROM users WHERE 'username'");
while ($row = mysql_fetch_array($result)) 
{
    $whoexpired = $row['trial_date_ended'];  
}

echo $whoexpired;

Thanks

aelia_co · April 13, 2015, 4:45pm

kingcoda said
timmylogue said
in column “registered” have passed 30 days (expired) from today’s date and If so, I will need it to email me the whole row that expired.

Yes made sense … This should work:
SELECT * from TABLE WHERE registered NOT BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

This query is inefficient and it won’t use indexes, if any. As a rule of thumb, one should never use “NOT”, or “different from” in the comparison clauses. A more efficient version is the following:


SELECT * from TABLE
WHERE registered < DATE_SUB(CURRENT_DATE(), INTERVAL 30 day)

kingcoda · April 14, 2015, 5:34am

aelia_co said >
This query is inefficient and it won’t use indexes, if any. As a rule of thumb, one should never use “NOT”, or “different from” in the comparison clauses. A more efficient version is the following:
SELECT * from TABLE WHERE registered < DATE_SUB(CURRENT_DATE(), INTERVAL 30 day)

First of all, it has nothing to do with indexes.

Besides, I know of famous frameworks which allows the use of NOT in comparison clause. Or <> as per SQL-92 standard

I did a test and benchmark gave negligible difference which is OK based on computational time.

Thank you for your inputs though. I will research on this.

EDIT:

NOT and NOT BETWEEN are different. Also, the results varied when I ran your query and my query on one of my busy websites, difference of 2 rows.

timmylogue · April 14, 2015, 5:41am

Hey KC, Thanks for all your help! Maybe you can help me out one last time? I’m having an issue with…

(My last post)

Any help is appreciated!

Thanks!

codents · April 14, 2015, 6:57am


$result = mysql_query("SELECT * FROM users");
while ($row = mysql_fetch_array($result)) {

    $whoexpired = $row['trial_date_ended'];  
    $user = $row['user'];
    $datenow = date("Y-m-d");

    if($datenow > $whoexpired){
        echo $user;
    }

}

This should do it, but you have to change your “trial_date"ended” field from DATETIME to DATE.

OR

If you need it as DATETIME, this should do it:


$result = mysql_query("SELECT * FROM users");
while ($row = mysql_fetch_array($result)) {

    $whoexpired = $row['trial_date_ended'];  
    $user = $row['user'];
    $datenow = date("Y-m-d H:i:s");

    if($datenow > $whoexpired){
        echo $user;
    }

}

Oops! Now I’ve seen that the “trial_date_ended” is “expired” or empty.

This should do it:

$result = mysql_query("SELECT * FROM users");
while ($row = mysql_fetch_array($result)) {

    $whoexpired = $row['trial_date_ended'];  
    $user = $row['user'];

    if($whoexpired == "expired"){
        echo $user;
    }

}

aelia_co · April 14, 2015, 8:02am

kingcoda said
aelia_co said >
This query is inefficient and it won’t use indexes, if any. As a rule of thumb, one should never use “NOT”, or “different from” in the comparison clauses. A more efficient version is the following:
SELECT * from TABLE WHERE registered < DATE_SUB(CURRENT_DATE(), INTERVAL 30 day)

First of all, it has nothing to do with indexes.

When you use SQL, it has always to do with indexes. Trust me on that, I’ve been a DBA for long enough.

kingcoda said
Besides, I know of famous frameworks which allows the use of NOT in comparison clause. Or <> as per SQL-92 standard

The fact that they allow it doesn’t mean that it’s an optimal way of doing things. The “<>” clause, in particular, is a performance killer and should always be replaced by a more efficient one. You will also find dozens of frameworks that compare dates using strings (e.g. SUBSTR(DATE(field), 4) = ‘20150414’), and that doesn’t make it a good practice (quite the opposite).

Another example is the method, that many people use even if they should not, of running a simple SELECT * FROM table and then using foreach in PHP to go through all the results and take the ones they want. That’s a terrible idea, especially when one nests queries in foreach loops (i.e. foreach…SELECT…foreach…SELECT). In the RDBMS world it’s called RBAR approach, and it’s frown upon (for a good reason).

kingcoda · April 14, 2015, 4:53pm

aelia_co said
kingcoda said
aelia_co said >
This query is inefficient and it won’t use indexes, if any. As a rule of thumb, one should never use “NOT”, or “different from” in the comparison clauses. A more efficient version is the following:
SELECT * from TABLE WHERE registered < DATE_SUB(CURRENT_DATE(), INTERVAL 30 day)

First of all, it has nothing to do with indexes.

When you use SQL, it has always to do with indexes. Trust me on that, I’ve been a DBA for long enough.

kingcoda said
Besides, I know of famous frameworks which allows the use of NOT in comparison clause. Or <> as per SQL-92 standard

The fact that they allow it doesn’t mean that it’s an optimal way of doing things. The “<>” clause, in particular, is a performance killer and should always be replaced by a more efficient one. You will also find dozens of frameworks that compare dates using strings (e.g. SUBSTR(DATE(field), 4) = ‘20150414’), and that doesn’t make it a good practice (quite the opposite).

Another example is the method, that many people use even if they should not, of running a simple SELECT * FROM table and then using foreach in PHP to go through all the results and take the ones they want. That’s a terrible idea, especially when one nests queries in foreach loops (i.e. foreach…SELECT…foreach…SELECT). In the RDBMS world it’s called RBAR approach, and it’s frown upon (for a good reason).

Ah Jeez! Alright alright… mate… I take my words back

Yes, pulling all and then doing via scripting language is the worst thing to do. However, since you are a DBA, may I have an opportunity to ask…
At times, we want to implement a search engine (myisam based) which returns the results grouped by - alphabet-match first… ordered alphabetically… however, if I want to push special characters at the bottom, (during ASC on the row)… I faced this issue last month and ran it past php to push the special characters based result at the bottom… For example, if ‘alpha’ is searched… it should return… alpha, alphabet, alpha-bet and not alpa,alpha-bet, alphabet.
how could we achieve that using sql-only approach?

timmylogue said
timmylogue said
codents said
You can also do this with PHP.
$sql = "SELECT * FROM yourtable"; $query = mysqli_query($dbconnectvariable, $sql);

Thank you!!

Hey!

If you could help me out with one more thing! How would I echo out the results for this? I guess I would use some loop too!

I’m trying to display the “username(s)” of whoever has the value “expired” in their row. I have tired to Google some help before posting but I just can’t find out how to do this!

I was hoping this would work? I’m I close?
$result = mysql_query("SELECT * FROM users WHERE 'username'");
while ($row = mysql_fetch_array($result)) 
{
    $whoexpired = $row['trial_date_ended'];  
}

echo $whoexpired;
Thanks

And you see only the last username
That is because you are ‘echo’-ing it outside the while loop, at each loop, $whoexpired is getting re-assigned based on the number of rows.

Make that:

$result = mysql_query("SELECT * FROM users WHERE 'username'");
while ($row = mysql_fetch_array($result)) 
{
    $whoexpired = $row['trial_date_ended'];  
    echo $whoexpired;
}
mysql_free_result($result);

Also, since I presume you are new and learning, dive into MySQLi rather mysql_ functions… mysql_ functions are deprecated as of php 5.5 and will be removed for good…

Happy learning

timmylogue · April 14, 2015, 7:11pm

codents said


$result = mysql_query("SELECT * FROM users");
while ($row = mysql_fetch_array($result)) {

    $whoexpired = $row['trial_date_ended'];  
    $user = $row['user'];
    $datenow = date("Y-m-d");

    if($datenow > $whoexpired){
        echo $user;
    }

}

This should do it, but you have to change your “trial_date"ended” field from DATETIME to DATE.

OR

If you need it as DATETIME, this should do it:


$result = mysql_query("SELECT * FROM users");
while ($row = mysql_fetch_array($result)) {

    $whoexpired = $row['trial_date_ended'];  
    $user = $row['user'];
    $datenow = date("Y-m-d H:i:s");

    if($datenow > $whoexpired){
        echo $user;
    }

}

Oops! Now I’ve seen that the “trial_date_ended” is “expired” or empty.

This should do it:

$result = mysql_query("SELECT * FROM users");
while ($row = mysql_fetch_array($result)) {

    $whoexpired = $row['trial_date_ended'];  
    $user = $row['user'];

    if($whoexpired == "expired"){
        echo $user;
    }

}

Thank you! Worked!

timmylogue · April 14, 2015, 7:12pm

kingcoda said
aelia_co said
kingcoda said
aelia_co said >
This query is inefficient and it won’t use indexes, if any. As a rule of thumb, one should never use “NOT”, or “different from” in the comparison clauses. A more efficient version is the following:
SELECT * from TABLE WHERE registered < DATE_SUB(CURRENT_DATE(), INTERVAL 30 day)

First of all, it has nothing to do with indexes.

When you use SQL, it has always to do with indexes. Trust me on that, I’ve been a DBA for long enough.

kingcoda said
Besides, I know of famous frameworks which allows the use of NOT in comparison clause. Or <> as per SQL-92 standard

The fact that they allow it doesn’t mean that it’s an optimal way of doing things. The “<>” clause, in particular, is a performance killer and should always be replaced by a more efficient one. You will also find dozens of frameworks that compare dates using strings (e.g. SUBSTR(DATE(field), 4) = ‘20150414’), and that doesn’t make it a good practice (quite the opposite).

Another example is the method, that many people use even if they should not, of running a simple SELECT * FROM table and then using foreach in PHP to go through all the results and take the ones they want. That’s a terrible idea, especially when one nests queries in foreach loops (i.e. foreach…SELECT…foreach…SELECT). In the RDBMS world it’s called RBAR approach, and it’s frown upon (for a good reason).

Ah Jeez! Alright alright… mate… I take my words back

Yes, pulling all and then doing via scripting language is the worst thing to do. However, since you are a DBA, may I have an opportunity to ask…
At times, we want to implement a search engine (myisam based) which returns the results grouped by - alphabet-match first… ordered alphabetically… however, if I want to push special characters at the bottom, (during ASC on the row)… I faced this issue last month and ran it past php to push the special characters based result at the bottom… For example, if ‘alpha’ is searched… it should return… alpha, alphabet, alpha-bet and not alpa,alpha-bet, alphabet.
how could we achieve that using sql-only approach?
timmylogue said
timmylogue said
codents said
You can also do this with PHP.
$sql = "SELECT * FROM yourtable"; $query = mysqli_query($dbconnectvariable, $sql);

Thank you!!

Hey!

If you could help me out with one more thing! How would I echo out the results for this? I guess I would use some loop too!

I’m trying to display the “username(s)” of whoever has the value “expired” in their row. I have tired to Google some help before posting but I just can’t find out how to do this!

I was hoping this would work? I’m I close?
$result = mysql_query("SELECT * FROM users WHERE 'username'");
while ($row = mysql_fetch_array($result)) 
{
    $whoexpired = $row['trial_date_ended'];  
}

echo $whoexpired;
Thanks
And you see only the last username
That is because you are ‘echo’-ing it outside the while loop, at each loop, $whoexpired is getting re-assigned based on the number of rows.

Make that:
$result = mysql_query("SELECT * FROM users WHERE 'username'");
while ($row = mysql_fetch_array($result)) 
{
    $whoexpired = $row['trial_date_ended'];  
    echo $whoexpired;
}
mysql_free_result($result);
Also, since I presume you are new and learning, dive into MySQLi rather mysql_ functions… mysql_ functions are deprecated as of php 5.5 and will be removed for good…

Happy learning

Great! Thank you for all your help!

kingcoda · April 15, 2015, 6:56am

U r welcome

Just ensure you dive into MySQLi rather just on mysql_* functions.

Cheers!

Topic		Replies	Views
Need help on upcoming events Theme Authors wordpress	3	277	March 17, 2020
how to get a expiration date like since date on visitor inside user page? Web and Code	0	187	January 3, 2020
All respect to reviewers but some things do not really make sense! CodeCanyon	5	322	June 17, 2019
No update on PHP script resubmission CodeCanyon	3	314	August 6, 2019
Footer copyright date, using php CodeCanyon	8	1691	March 1, 2011

PHP MYSQL - Check if any dates have passed.

Related topics