Date Difference using PHP date() and Stored MySQL Date

[Landslyde]

Hello Forum:

 

I smoked down the Google servers on this one and didn't come up with what I'm looking for. I have a MySQL table that stores a couple fields, one of them being a date field (set by CURDATE()) when it was written to the table. I'm trying to use the PHP date() and the sql field to find the number of days that's elapse, but I can't seem to get that to work for me. Here's what I'm working with:

$stmt = $db->prepare('SELECT a.attendeeid, s.attendeeid, fname, lname, suspend, reason, sdate, a.memberid, s.memberid 
FROM attendees AS a 
JOIN suspended AS s ON a.attendeeid = s.attendeeid 
WHERE a.memberid = :memberid 
AND suspend = "Y"');
$stmt->bindValue(':memberid', $memberid, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(); ?> 
 
<div class="row"> 
<div class="table-repsonsive">
<table class="table table-bordered table-hover table-striped" style="margin-top:30px">
   <thead>
      <tr>
         <th style="text-transform: uppercase">First</th>
         <th style="text-transform: uppercase">Last</th>
         <th style="text-transform: uppercase">Suspended</th>
         <th style="text-transform: uppercase">Suspension Date</th>
         <th style="text-transform: uppercase">Days on suspension</th>
         <th style="text-transform: uppercase">Reason</th>
      </tr>
   </thead>
 
<?php foreach($result as $row ) { $i++; ?>
 
<tbody>
    <?php echo '<tr style="'.getbgc($i). '">' ?>
       <td style="width:auto;"><?php echo $row[2] ?></td>
       <td style="width:auto;"><?php echo $row[3] ?></td>
       <td style="width:auto;"><?php echo $row[4] ?></td>
       <td style="width:auto;"><?php echo $row[6] ?></td>
       <td style="width:auto;"><?php echo strtodate(date('Y-m-d')) - $row[6] ?></td> // This is not working
       <td style="width:auto;"><?php echo $row[5] ?></td>

$row[6] holds sdate from the query. Does anyone know how I can do this? Thanks.

Edited September 21, 2015 by Landslyde

[Jacques1]

Why not do it in MySQL itself? You already know CURDATE(), and for date differences, there's DATEDIFF().

 

This has the additional benefit that you'll never run into time synchronization problems in case your PHP and your database server are running on two different machines.

[Landslyde]

In my searches, I saw that a few times. I didn't know how to use it though. It simply adds a new field. Thanks for letting me know how to use it, Jacques1. Much appreciated. I'm new at this and am learning a lot from people like you. Thanks.