php and mysql code question?

[shams]

hi,
i have mysql table treatment, each patient should come every month to collect his medicens exact in the same days of months, at least for 8 months, i need a php script that show me the patients didn't collect their medicens after one month, these are called defaulters the output of the script will be like this:
john is defaulter for 30 day from 2006-4-11.

this is the incomplete php script for help please any one can complete this one:

<?php
// Make a MySQL Connection
$var =$_POST['var'];
mysql_connect("localhost","root") or die(mysql_error());
mysql_select_db("mydb") or die(mysql_error());
$query = "SELECT name,fahterN,MAX(date) FROM treatment GROUP BY name,fatherN";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)) {
if(MAX(date)+ add 2 months = DATE) {
echo $row['name']." ".$row['fathernN']." "."is defaulter for".here shou be days." "."from"." ".$row['MAX(date)'];
echo "<br />";
} else { echo "No one is defaulter"; }
}
?>

[AndreJul]

If you use Date data type, try converting it first using strtotime() then add the result with

M*d*h*m*s

Where:
-M is month
-d is days
-h is hour ....

So strtotime_result + 2*30(assuming 1 month equal to 30 days)*24*60*60 would do the trick for 2 months skip

P.S.:Actually i haven't tried this myself but i've read about it somewhere in the forum, so please tell me if it works

[eelixduppy]

try something like this:

while($row = mysql_fetch_array($result)) {
list($year, $month, $day) = explode('-', $row[date]);
if($month > '10')
{
$month_plus_two = $month-10;
}
else
{
$month_plus_two = $month+2;
}

if($month_plus_two == date("m"))
{
$date1 = mktime(0,0,0,$month,$day,$year);
$date2 = mktime(0,0,0,date('m'),date('d'),date('Y'));
$elapsed_time = round((($date2 - $date1)/86400),0);

echo $row['name']." ".$row['fathernN']." "."is defaulter for".$elapsed_time." from ".$row['MAX(date)'];
echo "<br />";
} else { echo "No one is defaulter"; }
}

Not sure if this is going to work given that i didn't try it and im tired. Hope this helps though!

[siMKin]

Doing these things on a database level is way easier than trying to do it in PHP, not to mention that it's a lot more efficient and faster to directly select the records you're interested in.

something like this is probably what you need:

SELECT 
name, fahterN, MAX(date) AS LastDate 
FROM treatment 
GROUP BY name, fahterN HAVING TO_DAYS(NOW()) - TO_DAYS(LastDate) > 30

This will select everyone of which the MAX(date) is more then 30 days ago

Btw, is date really the name you use? If so, I would strongly recommend to start using another name as it is a reserved word

[shams]

thanks for replies, i read about strtotime() but didn't undrestand how to use in this script, the output of code written by eelixduppy is no defaulters which is not correct because there are defaulters, the last mysql query by siMKin is working fine but can any one put this query in a php script this well solve my porblem.

[siMKin]

<?php 
// Make a MySQL Connection 
$var = $_POST['var']; 
mysql_connect("localhost","root") or die(mysql_error()); 
mysql_select_db("mydb") or die(mysql_error()); 
$query = " 
  SELECT  
   name, fahterN, MAX(date) AS MaxDate, TO_DAYS(NOW()) - TO_DAYS(MAX(date)) AS DateDiff 
  FROM treatment 
  GROUP BY name, fahterN HAVING DateDiff > 30"; 
$result = mysql_query($query) or die(mysql_error()); 
if (mysql_num_rows($result) == 0) 
{ 
   echo "No one is defaulter"; 
} 
else 
{ 
  while($row = mysql_fetch_array($result)) 
  { 
   echo $row['name']." ".$row['fathernN']." "."is defaulter for ".$row['DateDiff']." from ".$row['MaxDate']."<br />\n"; 
  } 
} 
?>

p.s.: this is, of course, not tested

[shams]

thanks siMKin for the efforts the script is working fine.