I'm using PHP with SQLSRV for MSSQL 2008 r2.
What I want to be able do, is to input two dates into two input boxes, and show only records between desired dates, with total count of orders and total value example:
I would like to show how many orders were placed between 24/05/13 and 29/05/13, so I would get an output along the lines of 2 orders were placed, total value = 50.
Maybe this is easier than I think. I'm new to PHP and SQLSRV. What is the best way to implement this?
**Orders Table**
OrderId CustomerID OrderDate OrderValue
1 1 2013-05-29 23.00
2 2 2013-05-26 23.00
3 2 2013-05-26 27.00
4 3 2013-05-24 23.00
*********EDIT*****************
Okay, thanks to Shawn, I have a starting point. I've put his query into the following demo script, however it's not executing, where am I going wrong?
Form.php
<form action="action.php" method="post" >
<input name="StartDate" type="text" id="StartDate" value="start"/>
<input name="StartDate" type="text" id="EndDate" value="end"/>
<input type="submit" name="submit" >
</form>
Action.php This page returns the SQL calculation
<?php
include("connect.php");
?>
<?php
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @CustomerID int
SET @StartDate = '05/24/2013' /* FORM.StartDate */
SET @EndDate = '05/29/2013' /* FORM.EndDate */
SET @CustomerID = 2 /* FORM.CustomerID */
/* Get the TotalOrderValue for All CustomerIDs */
$sql ="SELECT CustomerID, sum(OrderValue) AS TotalOrderValue
FROM Orders
WHERE OrderDate >= @StartDate
AND OrderDate < dateAdd(d,1,@EndDate)
GROUP BY CustomerID
ORDER BY CustomerID";
$stmt = sqlsrv_query( $conn, $sql);
if( $stmt === false ) {
die( print_r( sqlsrv_errors(), true));
}
$CustomerID = sqlsrv_get_field( $stmt, 0);
echo "$TotalOrderValue: ";
?>