Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Jul 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

  • #2
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,169
    Thanks
    19
    Thanked 65 Times in 64 Posts
    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.

  • #3
    New to the CF scene
    Join Date
    Jul 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #4
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,169
    Thanks
    19
    Thanked 65 Times in 64 Posts
    just use it in exactly the same way as you did in your first query.

  • #5
    New to the CF scene
    Join Date
    Jul 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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...

  • #6
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,169
    Thanks
    19
    Thanked 65 Times in 64 Posts
    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
    PHP Code:
    $sql sprintf("SELECT Sum(price) As total FROM bookings01 WHERE newdate between %s"GetSQLValueString($colname_bookings"-1")); 

  • #7
    New to the CF scene
    Join Date
    Jul 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Nancy - I LOVE YOU! Now working perfectly, I'm so glad I joined this forum!! Cheers, Mike.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •