0

Good day all:

I need to search my db table for all services called "oil change" which where inserted 85 days ago (based on an inserted date column in the table) and display those results. Here's what I have:

Mysql Table

id (auto)
servicearea
carid
customerid
dateofservice

An example of a row and its content is:

id=1
servicearea=oil change
carid=dg5632
customerid=mb6524
dateofservice=2011-01-15

Currently, I'm using a smilar code (see below) to pull and display from this table a count down to an end of a number of days (say, 90 days) left to a future date based on that specified number of days.

So essentially, if my date of service is 2011-01-15 and my $limit value is 90 days, the code performs a search in the table for records which have a servicearea value of "oil change" and using the date of service and the $limit to thus determines how many days are left --as of today, until those 90 days expire.

The code for the above described task is as follows:

$get_swork = "select servicearea, date, customerid, clientID from servicesrendered where servicearea=' Oil Change ' and clientID = '$trimmed' order by id desc limit 1";//$trimmed is valued as the a passed carid as a variable to the code
 $get_swork_res = mysql_query($get_swork); 

  if (mysql_num_rows($get_swork_res) > 0) 
  { 
     while ( $swork_info = mysql_fetch_array($get_swork_res)) 
    { 
      $servicearea = $swork_info['servicearea']; 
      $date = $swork_info['date']; 
      $customerid = $swork_info['customerid']; 
      $clientID = $swork_info['clientID'];
     $space = " ";
     //$lastservicearea = $servicearea.$space.' on '.$date;
//echo $date;
//echo $servicearea;
//echo $customerid;
}
	}
	else
{
      echo " No history of Oil Changes"; 
}
$timestamp = strtotime($date);
$startDate=$timestamp; 
$limit=90;

$z=floor((time() - $startDate)/86400);
$days=$limit-abs($z);
//echo $z;
if ($days > 0) {
echo "<font size=\"4\" color=\"red\">";
echo $days;
echo "</font> days until the next oil change</p>";
}

else
{
$strippeddays=substr($days, 1);// removing the negative symbol from number of days passed
echo "Oil Change is <font size=\"4\" color=\"red\">";
echo $strippeddays;
echo "</font><u> day(s) overdue!!!</u></p>";
}
}

The code above works based on a specified condition in the $get_swork statement (first statement). There is a where clause which provide the statement with a specified variable --a specified clientid (or carid).

For my new task --and the topic of this new thread, I want to search the entire table and only display those records (which could be more than one) that will become due in 5 days from today --basically, displaying on the 85th day from the date of service.

Essentially for this new task, if my date of service is 2011-01-15 and my $limit value is 90 days, I want perform a search in the table for records which have a servicearea value of "oil change" and using the date of service and the limit of 90 days, I want to display all records which 90 days will come to an end in 5 days from the today's date.

My apologizes, as I may have not done a good job explaining --but I hope I did.

Any thoughts on this!

Best
Mossa

3
Contributors
3
Replies
6
Views
6 Years
Discussion Span
Last Post by mbarandao
0

Don’t make simple things complex (our job is the opposite). What data type is dateofservice of your table?

0

So you're basically just getting all records within a certain period of your 'dateofservice' column. Can be done using sql only - something along the lines of

SELECT `servicearea`, `date`, `customerid`, `clientID`, DATEDIFF(dateofservice, NOW()) As countDaysDiff 
FROM `servicesrendered`
WHERE countDaysDiff = '80' AND `servicearea` = 'Oil Change'
ORDER BY `id` DESC
0

Thanks for the reply!

I got it partially figured out with the following code; however, I'm still experiencing difficulties with performing the query on the last inserted id of the group of clientID --further explanation below.

echo"
<table cellpadding='5'>
<tr><td colspan='4'><b>OIL CHANGES DUE IN FIVE DAYS</b></td></tr>
<tr>
<td>Customer ID</td>
<td>Client ID</td>
<td>date</td>
<td>Service Area</td>
<td>Days Remaining</td>
<td>Status</td>
</tr>
";

$get_swork = "SELECT MAX(customerid), servicearea, date, clientID FROM servicesrendered WHERE servicearea=' Oil Change ' GROUP BY ClientID";

$get_swork_res = mysql_query($get_swork); 

  if (mysql_num_rows($get_swork_res) > 0) 
  { 
     while ( $swork_info = mysql_fetch_array($get_swork_res)) 
    { 
      $servicearea = $swork_info['servicearea']; 
      $date = $swork_info['date']; 
      $customerid = $swork_info['customerid']; 
      $clientID = $swork_info['clientID'];
      $space = " ";

        $timestamp = strtotime($date);
        $startDate=$timestamp; 
        $limit=90;//INITIALLY 5 Newly modified
        $z=floor((time() - $startDate)/86400);
        $days=$limit-abs($z);

        if ($days >= 0 && $days <= 5) //ONLY SHOW THE LINE IF IT'S 5 DAYS AND LESS
        {
        echo"
        <tr>
        <td>$customerid</td>
        <td>$clientID</td>
        <td>$date</td>
        <td>$servicearea</td>
        <td >$days left!</td>
        <td>5 DAY WARNING</td>
        </tr>
        ";
        }
        if ($days < 0) //ONLY SHOW THESE LINES IF IT'S PAST DUE 
        {
        $strippeddays=substr($days, 1);// removing the negative symbol from number of days passed
        echo"
        <tr>
        <td style='color:#FF0000;'>$customerid</td>
        <td style='color:#FF0000;'>$clientID</td>
        <td style='color:#FF0000;'>$date</td>
        <td style='color:#FF0000;'>$servicearea</td>
        <td style='color:#FF0000;'>$strippeddays OVERDUE!</td>
        <td style='color:#FF0000;'>PAST DUE</td>
        </tr>
        ";
        }
    }
  }

I'm missing a critical element; whereas, If there are multiple rows containing the same clientID, but each row inserted at different time(thus having a different id), I need to perform the query assessment based on the last inserted id of that grouped clientID or perhaps perform based on the date of the same group.

DB table content sample for clientID 34PO3

ID       SERVICEAREA    CLIENTID CUSTOMERID DATEOFINSERT
 5       Oil Change	34PO3	 SB3332	     2010-11-19
 6	 Exhaust	34PO3	 SB3332	     2011-02-15
 7	 Oil Change	34PO3	 SB3332	     2011-01-13

When the query assessment is done, it should be perform in the group "34PO3" and the row with id 7 (which is when the last time an oil change was performed).

Instead, it is performed based on the first occurrence of the oil change and the output is incorrect as in below

Car plate #	Last Oil Change Date	Days Overdue/Remaining	Status
34PO3	2010-11-19	8 Days OVERDUE!	PAST DUE
MD66894	2010-11-15	12 Days OVERDUE!	PAST DUE
43sFC	2010-11-29	2 Remaining	ACTION REQUIRED

I can surely use some assistance with this

Thanks,
Mossa--

Edited by mbarandao: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.