Sum(total) for filtered query results

Hi - This is my first thread as a newbie, so please be patient if I'm not making much sense!

I've searched the net for the last week without success...

I've streamlined a bookings administration system (using PHP & MySQL)whereby displayed results are filtered using a simple drop down menu for each year. The drop down options are for individual years (i.e. 2006, 2007) and the drop down menu values are of a date range, i.e. menu option 2006 has an associated menu value of 20060101 and 20061231. This relates to the "between" value shown below:

mysql_select_db($database_dBconnect, $dBconnect);
$query_bookings = sprintf("SELECT *, date_format(date, '%%e %%b %%Y') as newdate FROM `bookings01` WHERE date between %s ORDER BY `date` ASC", GetSQLValueString($colname_bookings, "-1"));
$query_limit_bookings = sprintf("%s LIMIT %d, %d", $query_bookings, $startRow_bookings, $maxRows_bookings);
$bookings = mysql_query($query_limit_bookings, $dBconnect) or die(mysql_error());
$row_bookings = mysql_fetch_assoc($bookings);

I am attempting to display a total value for the filtered results, i.e. if option 2006 selected, only for the relevant date range described above. However when I attempt to incorporate the same "between" value code into the Sum calculation (assuming %s is the drop down menu value) I receive the error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s' at line 1" - My code I have used to try and display the filtered results total value is shown below:

<?php $sql = 'SELECT Sum(price) As total '.' FROM bookings01 WHERE newdate between %s';
$result = mysql_query($sql) or die(mysql_error());
$i = mysql_fetch_array($result);?>
<?php echo $i['total'];?>

Can anybody possibly provide a solution? Many thanks in advance.

The difference is the sprintf, you dont have it on your second query so the query is exactly as you have typed it whereas in your first query the %s is replaced with the actual value.

Hi Nancy - Thank you for your reply, I understand sprintf is basically a print string command, is there any way of incorporating this within my second query? or am I already on te wrong lines? Cheers.

just use it in exactly the same way as you did in your first query.

wow Nancy that was a quick response! OK, here is what my second query now looks like:

<?php $sql = sprintf("SELECT *, date_format(date, '%%e %%b %%Y') as newdate FROM `bookings01` WHERE date between %s ", GetSQLValueString($colname_bookings, "-1"));
$result = mysql_query($sql) or die(mysql_error());
$i = mysql_fetch_array($result);?>
<?php echo $i['total'];?>

I no longer receive an error code, however it is displaying a blank value, despite filtering 4 results - have I employed the code in the correct context? I have tried removing the GetSQLValueString but an error reports too few arguments...

The first parameter of the sprintf should still be "SELECT Sum(price) As total FROM bookings01 WHERE newdate between %s"
else you're just running the same query as before.

so your code should be

$sql = sprintf("SELECT Sum(price) As total FROM bookings01 WHERE newdate between %s", GetSQLValueString($colname_bookings, "-1"));

Nancy - I LOVE YOU! :thumbsup: Now working perfectly, I'm so glad I joined this forum!! Cheers, Mike.