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 5 of 5
  1. #1
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    341
    Thanks
    22
    Thanked 1 Time in 1 Post

    sum column total from db

    Hello:

    I have a mysql table that stores customer transaction information. I want to sum column name "total" --which has values like this: $253.20-- only where the value of another column is "mossa01".

    For this, I passed the variable to the code via url and trimmed it. I then used the following statement:

    PHP Code:
     $client = @$_GET["q"] ;
      
    $trimmed trim($client); //trim whitespace from the stored variable
      
    echo"$client";//equal mossa01
    $res=mysql_query('SELECT SUM(SUBSTRING(`total`,2)) AS `total` FROM `billofservice` where clientID like "$trimmed" ');

    $result=mysql_query($res);

    if(
    $result == false

       
    user_error("Query failed: " mysql_error() . "<br />\n$res"); 

    else
    if(
    mysql_num_rows($result) == 0

       echo 
    "<p>Sorry, no rows were returned by your query.</p>\n"


    //if(!$res){
      //  die("Error: ".mysql_error());
    //}

    $row mysql_fetch_assoc($res);
    echo 
    "Total income earned from this client to date is ";echo"$";echo($row['total']); 
    With this effort, I get the following error:

    Code:
    Notice: Query failed: 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 'Resource id #3' at line 1
    Resource id #3 in C:\Apache\htdocs\andy\invoicehistory.php on line 80
    May I request some assistance?
    Mossa

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You are calling mysql_query() twice. You should assign the text of the query to variable $res, and then call mysql_query($res).

    Though, I'm don't know that your SUM() is going to work on a string like that. Worth a try, but I'm thinking you'll need to cast the substring to an int or float to get the SUM() to work.

  • #3
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    341
    Thanks
    22
    Thanked 1 Time in 1 Post
    Thanks for the reply. I have corrected the multiple calling of mysql_query() and this resolved the resource id error.

    Now I'm a bit at lost with casting the substring as an int and incorporating the sum() thus displaying the total of column. I have the following:
    PHP Code:
    $res=mysql_query('SELECT SUM(CAST(SUBSTRING(`total`,2))) AS integer FROM `billofservice` where clientID like "%$trimmed%" '); 
    but need help completing this statement...
    Last edited by mbarandao; 12-09-2010 at 07:03 PM.

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Code:
    SELECT SUM(CAST(SUBSTRING(column_name,2) AS UNSIGNED INTEGER))

  • #5
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    341
    Thanks
    22
    Thanked 1 Time in 1 Post
    Fumigator, thank you for the assistance. I got the issue resolved with the following:

    PHP Code:
    $myString "SELECT SUM(SUBSTRING(total,2)) AS total FROM billofservice where clientID like '%$trimmed%' ";
    $res mysql_query($myString);

    if(
    $res == false

       
    user_error("Query failed: " mysql_error() . "<br />\n$res"); 

    else
    if(
    mysql_num_rows($res) == 0

       echo 
    "<p>Sorry, no rows were returned by your query.</p>\n"

    All is well!
    Again, thanks for the follow-ups.
    Mossa


  •  

    Posting Permissions

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