...

View Full Version : Sum(total) for filtered query results



alphaswerve
07-17-2007, 12:23 PM
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.

NancyJ
07-17-2007, 12:55 PM
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.

alphaswerve
07-17-2007, 01:45 PM
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.

NancyJ
07-17-2007, 01:49 PM
just use it in exactly the same way as you did in your first query.

alphaswerve
07-17-2007, 02:01 PM
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...

NancyJ
07-17-2007, 02:11 PM
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"));

alphaswerve
07-17-2007, 02:18 PM
Nancy - I LOVE YOU! :thumbsup: Now working perfectly, I'm so glad I joined this forum!! Cheers, Mike.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum