WAMFT1 Posted May 11, 2020 Share Posted May 11, 2020 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted May 11, 2020 Share Posted May 11, 2020 (edited) 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 May 11, 2020 by Barand Quote Link to comment Share on other sites More sharing options...
WAMFT1 Posted May 11, 2020 Author Share Posted May 11, 2020 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); ?> Quote Link to comment Share on other sites More sharing options...
Barand Posted May 12, 2020 Share Posted May 12, 2020 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 | +---------------+---------------+-----------------+---------------+ Quote Link to comment Share on other sites More sharing options...
WAMFT1 Posted May 22, 2020 Author Share Posted May 22, 2020 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;"); Quote Link to comment Share on other sites More sharing options...
WAMFT1 Posted May 22, 2020 Author Share Posted May 22, 2020 Ignore my last, I have managed to work it out. Finally. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.