Group By Day

[Danny620]

I have a database like

Column Type Null Default Comments status_id int(15) No status_message text No attachment varchar(40) No post_on datetime No sent tinyint(1) No 2 1 = sent, 2 = not sent user_id int(15) No comp_id int(15) No social_network int(1) No 1 1 = Facebook, 2 = Twitter, 3 = Linkedin success tinyint(1)

 

my mysql select looks like

 

// Make the query:
$q = "SELECT status_id, status_message, attachment, DATE_FORMAT(post_on, '%M %d, %Y %H:%i') AS dr, sent, success FROM social_calendar WHERE comp_id = '{$_SESSION['comp_id']}' ORDER BY post_on DESC LIMIT $start, $display";
$r = @mysqli_query($dbc, $q); // Run the query.
$all_status = array();
while($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){
   $all_status[] = $row;
}

 

what i want to be able to archive is the status grouped by day

 

<table class="table table-striped">
   <thead>
<tr>
<th>Image</th>
	    <th>Sent</th>
	    <th>Success</th>
<th>Status Message</th>
<th>Publish</th>
<th>Actions</th>
</tr>
</thead>
   <tbody>


  <?php foreach($all_status as $status){ ?>
   <tr>
<th colspan="6" style="text-align:center; background-color:#CBF09E">November 28, 2012</th>
   </tr>

  <?php $status_message = substr(strip_tags($status['status_message'], ''), 0, 44) . '...'; ?>

   <tr>
   <td>...</td>
   <td><?php echo ($status['sent'] == '1') ? '<i class="icon-ok" title="Successfully Sent"></i>' : '<i class="icon-time" title="Not Yet Posted"></i>' ?></td>
   <td><?php echo ($status['success'] == '1') ? '<i class="icon-ok" title="Successfully Posted"></i>' : '<i class="icon-remove" title="Failed To Post"></i>' ?></td>
   <td><?php echo $status_message; ?></td>
   <td><?php echo $status['dr']; ?></td>
   <td>
   <a data-keyboard="true" data-backdrop="true" class="btn small edit" href="#">Edit</a>
   <a class="btn btn-danger" data-keyboard="true" data-backdrop="true" href="<?php echo BASE_LOGGEDIN_URL . 'delete.php?item=3&id=' . $status['status_id'] . ''; ?>">Delete</a></td>
   </tr>

   <?php } ?>

   </tbody>
   </table>

[Muddy_Funster]

have you tried using another date_format to pull out only the day and use group by on it ?

[Danny620]

have you tried using another date_format to pull out only the day and use group by on it ?

 

sorry im lost

[Muddy_Funster]

adding in DATE_FORMAT(post_on, '%d') as day_to_group_by and then have a GROUP BY day_to_group_by at the end of the query.