Jump to content

Date Diff on Array


WAMFT1

Recommended Posts

Hi Everyone

I have an older coded side that I am looking at for a friend, I need to add in a date diff colum into their existing index page. I can add using the following code onto the indivudually record page can't get it to work on the index. Any help is appreciated.

Code on Individual page which works:::

<HEAD> 
  <?
if($test['settlement_date']=="0000-00-00"){
$date1 = new DateTime($date_received);
$date2 = new DateTime("now");
$interval = $date1->diff($date2);
} else {
$date1 = new DateTime($date_received);
$date2 = new DateTime($settlement_date);	
$interval = $date1->diff($date2);
}
?>
</HEAD>

<Body>
  <?php echo "".$interval->days."";?>
  </BODY>

I was trying to add something similar but as it is a list I can't work out what I need to do.

INDEX::: 

while($test = mysql_fetch_array($result))
			{
				$complaint_id = $test['complaint_id'];	
				echo"<tr>";
				echo"<td class='standard_left'>".$test['complaint_id']."</td>";
				echo"<td class='standard_left'>".$test['status']."</td>";	
				echo"<td class='standard_left'>".$test['LastName'].", ".$test['FirstName']."</td>";	
				echo"<td class='standard_left'><a href ='complaint_view.php?complaint_id=$complaint_id'>".$test['complainant']."</td>";	
				echo"<td class='standard_center'>".$test['date_received']."</td>";	
				echo"<td class='standard_center'>".$test['settlement_date']."</td>";	
				echo"<td class='standard_center'>".$test['interval->days']."</td>";	
				echo "</tr>";

I know it is old redundent code but if I can get this one colum working I would be happy.

Link to comment
Share on other sites

Do it in the query.

SELECT date_received
     , settlement_date
     , DATEDIFF(settlement_date, date_received) as interval_days
. . .

edit: Almost forgot - you need to switch from mysql_ functions (obsolete) to mysqli or PDO (recommended)

Edited by Barand
Link to comment
Share on other sites

13 hours ago, Barand said:

SELECT date_received , settlement_date , DATEDIFF(settlement_date, date_received) as interval_days

I don't understand how the above works the 'if' query with date. I've tried so much and I am now just getting frustrated. Going around in circles. 

What I have is as below, I don't know how is should be. and yes, i know i am using obsolete code but it is a temporary fix.

<?php 
		include("../edb.php");
			$result=mysql_query("SELECT * FROM `compliance_complaints` INNER JOIN eusers ON eusers.id = compliance_complaints.adviser  WHERE status='Closed' order by complaint_id DESC ");

if($test['settlement_date']=="0000-00-00"){
$date1 = new DateTime($date_received);
$date2 = new DateTime("now");
$interval = $date1->diff($date2);
} else {
$date1 = new DateTime($date_received);
$date2 = new DateTime($settlement_date);	
$interval = $date1->diff($date2);
}		
while($test = mysql_fetch_array($result))
			{

				$complaint_id = $test['complaint_id'];	
				echo"<tr>";
				echo"<td class='standard_left'>".$test['complaint_id']."</td>";
				echo"<td class='standard_left'>".$test['status']."</td>";	
				echo"<td class='standard_left'>".$test['LastName'].", ".$test['FirstName']."</td>";	
				echo"<td class='standard_left'><a href ='complaint_view.php?complaint_id=$complaint_id'>".$test['complainant']."</td>";	
				echo"<td class='standard_center'>".$test['date_received']."</td>";	
				echo"<td class='standard_center'>".$test['settlement_date']."</td>";	
				echo"<td class='standard_center'>".$test['interval']."</td>";	
				echo "</tr>";
			}
			mysql_close($conn);
			?>

 

Link to comment
Share on other sites

7 hours ago, WAMFT1 said:

I don't understand how the above works the 'if' query with date.

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_datediff

Example

SELECT * FROM test.compliance_complaints;
+---------------+---------------+-----------------+
| compliance_id | date_received | settlement_date |
+---------------+---------------+-----------------+
|             1 | 2020-04-01    | 2020-04-21      |
|             2 | 2020-05-01    | NULL            |
|             3 | 2020-01-10    | 2020-05-01      |
+---------------+---------------+-----------------+

SELECT compliance_id
     , date_received
     , settlement_date
     , DATEDIFF(COALESCE(settlement_date, CURDATE()), date_received) as interval_days
FROM compliance_complaints;
+---------------+---------------+-----------------+---------------+
| compliance_id | date_received | settlement_date | interval_days |
+---------------+---------------+-----------------+---------------+
|             1 | 2020-04-01    | 2020-04-21      |            20 |
|             2 | 2020-05-01    | NULL            |            11 |
|             3 | 2020-01-10    | 2020-05-01      |           112 |
+---------------+---------------+-----------------+---------------+

 

Link to comment
Share on other sites

  • 2 weeks later...

I managed to get the DateDiff working, now my Inner Join part doesn;t work.  Wish I knew more about coding. Can someone please help? I can't see what is wrong.

("SELECT adviser, status, complainant, complaint_id, date_received, settlement_date, DATEDIFF(COALESCE(settlement_date, CURDATE()), date_received) as interval_days FROM `compliance_complaints` INNER JOIN eusers ON eusers.id = compliance_complaints.adviser WHERE status='Open' order by complaint_id DESC;"); 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.