PHP MYSQL - Check if any dates have passed.


#1

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

#2
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 :smiley: … This should work:

SELECT * from TABLE

WHERE registered NOT BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

:stuck_out_tongue:

Hope it helps :slight_smile:


#3

Thank you! :slight_smile:


#4

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.


#5
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!!


#6
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 :slight_smile:


#7
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 :smiley: … 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)

#8
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. :slight_smile:

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. :slight_smile:

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.


#9

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!


#10

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

}


#11
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. :slight_smile:

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).


#12
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. :slight_smile:

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 :slight_smile:

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!

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 :slight_smile:

And you see only the last username :slight_smile:
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 :slight_smile:


#13
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! :slight_smile: Worked!


#14
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. :slight_smile:

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 :slight_smile:

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!

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 :slight_smile:

And you see only the last username :slight_smile:
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 :slight_smile:

Great! Thank you for all your help!


#15

U r welcome :slight_smile:

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

Cheers!