sum column total from db

[mbarandao]

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:

$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($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"; 
} 


//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:

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

[pritaeas]

like "$trimmed" is wrong. You should use single quotes instead of double quotes.

[mbarandao]

Thanks for the response! Single quotes produces an error...and I have however used similar statement in other areas with double quotes without any problems.

This is how the statement looks with your suggestion --again an error is produced

$res=mysql_query('SELECT SUM(SUBSTRING(`total`,2)) AS `total` FROM `billofservice` where clientID like '$trimmed' ');

.

and this is the error

Parse error: syntax error, unexpected T_VARIABLE in C:\Apache\htdocs\andy\invoicehistory.php on line 79

Any other possible reasons as to the initial failure of the code?

[mbarandao]

I got the issue resolved with the following:

$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!
Mossa