Count SQL Rows based on Date Query

Question

I'm building a really simple PHP app where they just want to list out their current orders (from an SQL database) and make a few calls based on when the due date of the orders are.

I'm trying to count all the orders in the table that have due dates less than 6 days from now... and I am STRUGGLING!

I've got it displaying all the orders in a table, but I have no idea how to approach counting the rows based on their due date.

The order_duedate is in date format.

Does anyone have any suggestions??

I'm pretty new to pulling info from databases so this could be quite simple - Here is the code I am using:

<?php

ob_start();
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'aaa');
define('DB_PASSWORD', 'aaa');
define('DB_DATABASE', 'aaa');
$connection = mysql_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD) or die(mysql_error());
$database = mysql_select_db(DB_DATABASE) or die(mysql_error());

// TABLE ROWS AS FOLLOWS
// order_no / order_client / order_duedate / order_status


$table_all = "SELECT * FROM orders";
$table_all_data = mysql_query($table_all);


// COUNT ORDERS THAT HAVE ORDER_DUEDATE < 6 DAYS FROM NOW


?>

<p>TOTAL OVERDUE ORDERS</p>
<p><?php echo $total_overdue ?></p>



<?php
// DISPLAY COMPLETE TABLE OF DATA
while($row = mysql_fetch_array($table_all_data)){ 
    // CHANGE FORMAT OF DATE
    $reformat_duedate = $row['order_duedate'];
    $phpdate = strtotime( $reformat_duedate );
    $newdate = date( 'd/m/Y', $phpdate );

?>

<tr>
    <td><a href=""><?php echo $row['order_no'] ?></a></td>
    <td class="alignleft"><?php echo $row['order_client'] ?></td>
    <td><span class="date open"><?php echo $newdate ?></span></td>
    <td class="alignleft"><?php echo $row['order_status'] ?></td>
    <td>2</td>
    <td><a href=""><i class="fa fa-pencil" aria-hidden="true"></i></a>&nbsp;&nbsp;&nbsp;<a href=""><i class="fa fa-times" aria-hidden="true"></i></a></td>
</tr>


<?php } 

mysql_close(); ?>

Thank you so much!

stackoverflow.com/questions/7341658/…

Kevin
– Kevin

2016-07-19 04:56:48 +00:00
Commented Jul 19, 2016 at 4:56 — Kevin
– Kevin, Commented Jul 19, 2016 at 4:56

SIDU · Accepted Answer · 2016-07-19 05:01:59Z

1

SELECT *
FROM orders
WHERE date_add(order_duedate, INTERVAL -6 DAY) < now()

answered Jul 19, 2016 at 5:01

SIDU

2,2781 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

areke · Accepted Answer · 2016-07-19 05:03:32Z

1

You could try SELECT * FROM orders WHERE order_duedate <= NOW() + INTERVAL 6 DAY

answered Jul 19, 2016 at 5:03

areke

1261 silver badge8 bronze badges

Comments

Will · Accepted Answer · 2016-07-19 05:04:18Z

0

Run another query:

select count(*) from orders where order_duedate < CURDATE() + INTERVAL 6 DAY;

answered Jul 19, 2016 at 5:04

Will

2,1911 gold badge24 silver badges24 bronze badges

Collectives™ on Stack Overflow

Count SQL Rows based on Date Query

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related