0

I am trying to output the sum of turples from the a table. The SQL statement is ok and gives me results. My problem is printing the answer on a form. Below is my code:

<?php 
    $r=mysql_query("select sum(total_amount) from lbc_production where link_id='C741_Link01' and execution_date BETWEEN '2014-12-01' AND '2014-12-31'");
    $rows=mysql_fetch_assoc($r);
    echo $r;
    echo $rows['(total_amount)'];              
?>

It outputs Resource id #9 on a form but I want a figure.

John Conde
  • 217,595
  • 99
  • 455
  • 496
Ronald Nionzima
  • 11
  • 1
  • `echo $rows['sum(total_amount)'];` or use an alias for your value – Mark Baker Apr 24 '15 at 17:58
  • But don't echo `$r`, because it is a resource, which isn't a value that can be echoed – Mark Baker Apr 24 '15 at 17:58
  • what are those `()` doing in `(total_amount)`? `or die(mysql_error())` to `mysql_query()` and see why it's failing. Learn how to interpret syntax errors. – Funk Forty Niner Apr 24 '15 at 17:59
  • forget the `$r`...Try `echo $rows['total_amount'];` – Pedro Lobito Apr 24 '15 at 17:59
  • Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Apr 24 '15 at 18:24
  • @Pedro Lobito, When I echo $rows['sum(total_amount)']; it doesn't output anything – Ronald Nionzima Apr 24 '15 at 19:00

1 Answers1

2

You have two issues:

  1. $r contains the resource ID for your MySQL connection. You echo it out. That's why you see that. Stop doing that and it goes away.

  2. You are not using the right array key to access your sum value you seek so nothing is output. If you had error reporting turned out PHP would have told you this.

Here's improved code to resolve these issues:

<?php 
$r=mysql_query("select sum(total_amount) as total from lbc_production where link_id='C741_Link01' and execution_date BETWEEN '2014-12-01' AND '2014-12-31'");
$rows=mysql_fetch_assoc($r);
echo $rows['total'];              
?>

I removed the line where you echo out the MySQL resource and added an alias to sum(total_amount) which makes it easier to access via PHP. The alias is called total which I use as the key to access that value from the $row array.

FYI, you shouldn't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Zoe
  • 27,060
  • 21
  • 118
  • 148
John Conde
  • 217,595
  • 99
  • 455
  • 496