How to get total value result from a column

[shmideo]

I get all the results displayed but how can I echo the total value for for all rows found for under column 'duration' which only contains numbers (seconds).

 

$query = "SELECT * FROM asterisk_cdr WHERE calldate LIKE '%$calldate%' AND channel LIKE '%$channel%' AND duration";

$result = mysqli_query($dbcon, $query) or die('Error getting data');
$num_rows = mysqli_num_rows($result);

[ginerjm]

"AND duration"  ???  What is this?

 

And - where is the rest of the code?

 

If you just want a total of duration, add that to the query.  Or, if you are going to browse thru the results in a loop, you could just accumulate the value as you go and then output it at the end of the loop (after).

[shmideo]

"duration" is one of the columns in the table which contains number of seconds and it displays each row found. I just want to to get a total count for that column and echo the result.

[ginerjm]

My question pertained to the fact that your query, as written and posted, would be broken.  I know it's a column.  Do you know that it's a bad query statement?

 

Do you have any more code to show us or did you look up how to make a query calculate a sum for you?

[shmideo]

The query works, but yes I would prefer to know the proper way. This is the code I've been working on (with help from the guys on this forum) for for the past few days!

error_reporting(E_ALL | E_NOTICE);
        ini_set('display_errors', '1');  

if (isset($_POST['submitted'])) {
	
include('connect1.php');

	if (!empty($_POST['calldate'])) {
		$calldate = mysqli_real_escape_string ($dbcon, $_POST['calldate']);
	} else {
		$calldate = FALSE;
		echo '<p class="error">ha ha, you have not entered a date!</p>';
	}
	
$channel = $_POST['channel'];
$submitted = $_POST['submitted'];
$duration = 'duration';

If($calldate){
$query = "SELECT * FROM asterisk_cdr WHERE calldate LIKE '%$calldate%' AND channel LIKE '%$channel%' AND duration";

$result = mysqli_query($dbcon, $query) or die('Error getting data');
$num_rows = mysqli_num_rows($result);

echo " '$num_rows' calls found for '$calldate'";
echo "<br/>";

echo "<table width='300px'>";
echo "<tr> <th>Time of Call: Call Duration</th>";

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
	
	echo "<table width='400px'>";
	echo "<tr><td>";	
		
	echo $row['calldate']. "   (". $row['duration'] . " sec)" ;
	
	echo "</td></table>";
	
		echo "</td>";


}

[ginerjm]

Your query ends with " and duration".  The only thing I see there is that it evaluates duration to 'TRUE' and basically anything with a non-zero duration is selected.  What are you trying to do with that clause tother than confuse others?

 

As for your output - you are generating a table inside of a table for EVERY row of the results.  Why?

 

To get your total, set a var to 0 and then in the loop accumulate the duration into that var each time thru the loop.  When the loop ends, output that var .

Edited November 14, 2014 by ginerjm

[shmideo]

Well I'm still inexperienced with PHP. Yes every row in that column has a number > 0 which is what I wanted.

I don't have a clue how to accumulate the duration, that's why I posted.

[Barand]

$tot = 0;

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {

    ....

    ....

    $tot += $row['duration'];   // accumulate duration

}


echo "Total duration: $tot";

[shmideo]

Wow, thanks, this works great and gives a running total. Is there a way to supress all but the last row? Meaning just give a total for all rows found. I did try playing around with the $tot but maybe it is somethig different.

[shmideo]

Sorry Barand, ignore that, mistake on my part. Works perfctly! Thank you