[SOLVED] Calculate (add) all integers from mysql rows

[keldorn]

Hello, okay I have lets just say I have 3 tables in a database one called "id" (auto-incrementing, Key thing-a-jig) "date", and one called "hits". 

 

Date is formatted as Year-month-day, hits is an integer.

 

I want to grab only the Rows that are from "Today" and then cacluate the "hits" to a total numbers.

 

So say you pull out of the database

 

id:1  date:2009-09-09 hits:200

id:2  date:2009-09-09 hits:100

id:3  date:2009-09-09 hits:100

= 400 hits.

 

 

How I can calculate all those 3 to a total number?

 

Here is my code thus far.

 

if(!$smarty->is_cached('index.tpl')) {
         $results = array();
         $link = mysql_connect($CONFIG['mysql_host'],$CONFIG['mysql_user'], $CONFIG['mysql_pass']) or die('Could not connect: ' . mysql_error());
         $db_selected = mysql_select_db($CONFIG['mysql_db'],$link) or die('Could not contact the database');

        //Get Todays Date in year-month-day
       	$date =  date("Y-m-d");
                // Only grab the rows where the date = today's $date.
	$Hits = mysql_query('SELECT * FROM links WHERE date=' . $date . ' ORDER BY id DESC LIMIT 0,500') or die('Could not connect: ' . mysql_error());

	while ($line = mysql_fetch_assoc($Hits)) {

	     $tmp[] = $line;
    }


                /// Begin Math problem to calculate the hits....?    


}

 

[mikesta707]

$count = 0;

while ($line = mysql_fetch_assoc($Hits)) {
     $count += $line['hits'];
  
}
echo $count;

 

?

 

[Alex]

There's no need for a loop.

 

$line = mysql_fetch_assoc($Hits);
$count = array_sum($line['hits']);

[mikesta707]

that won't work. mysql_fetch_assoc returns only 1 row at a time. that will try to add together all the entries in the array from that row. the array will look something like

$arr['id'] = 2;

$arr['date'] = some date;

$arr['hits'] = 240;

 

you're code probably won't even execute because it expects an array as the parameter and you are supplying an integer

[keldorn]

Yes the array_num was wrong. It returned (paraphrased): Error array_num expects Array

 

The while() code Mikesta didn't work exactly. I put exit; inside the while()  and it did not it get caught by it.

But I do already have a While() just above it for a another slighty unrelated msyql_query. Could that interfere with it?

 

Other then that

 

$count += $line['hits']; works in the other while() loop. It returns 35759 hits. (from all the rows.) But I just want only the ones from today's date.

[mikesta707]

post your updated code and let me see. Assuming the query was the same, and it worked out, it should calculate correctly. Also, why did you put an exit() inside the while?

[keldorn]

post your updated code and let me see. Assuming the query was the same, and it worked out, it should calculate correctly. Also, why did you put an exit() inside the while?

 

To debug of course. The while() loop was not executed as thought. PHP ignored it for some reason.

 

Here is the full code.

 

 

 

<?php

require 'libs/Smarty.class.php';
require 'config.php';
$smarty = new Smarty;
//$smarty->caching = 2;
//$smarty->cache_lifetime = 30;

    /* Remove caching for now if(!$smarty->is_cached('index.tpl')) { */
         $results = array();
         $link = mysql_connect($CONFIG['mysql_host'],$CONFIG['mysql_user'], $CONFIG['mysql_pass']) or die('Could not connect: ' . mysql_error());
         $db_selected = mysql_select_db($CONFIG['mysql_db'],$link) or die('Could not contact the database');         
$result = mysql_query('SELECT * FROM `links` ORDER BY id DESC LIMIT 0, 500')or die(mysql_error());
        while ($line = mysql_fetch_assoc($result)) {

             $total_links[] = $line;
    //$count += $line['hits'];  This here would return 35759 hits
        }

       //Get Todays Date in year-month-day
$date =  date("Y-m-d");
                // Only grab the rows where the date = today's $date.
$Hits = mysql_query('SELECT * FROM links WHERE date=' . $date . ' ORDER BY id DESC LIMIT 0,500') or die('Could not connect: ' . mysql_error());

        // PHP ignores this for some reason.
        while ($line = mysql_fetch_assoc($Hits)) {
             $count += $line['hits'];
  
        }






/* bracket from if caching } */


$total_rows = mysql_num_rows($result);
//Assign the results to smarty
$smarty->assign('result', $total_links);
$smarty->assign('result', $total_hits);
$smarty->assign('title',$title);
$smarty->assign('total', $total_rows); 
$smarty->compile_check = true;
$smarty->debugging = false;
$smarty->display('index.tpl');
?>

[mikesta707]

then you probably aren't returning any rows from that query. I'm going to assume that you indeed have rows that were added on this day. so make sure that you have the format of your date right, and its as you expect. Oh and one thing. I'm going to assume your date column is a varchar data type? Well since you don't surround it with quotes in your query, it may be assuming its an int (or rather it would be an equation, 2009 minus 9 minus 10)

 

try

 

$Hits = mysql_query("SELECT * FROM links WHERE date='" . $date . "' ORDER BY id DESC LIMIT 0,500") or die('Could not connect: ' . mysql_error());

 

and see if that helps

 

also echo your query before it executes to make sure its what you expect it to be

[keldorn]

It works now!  I actually thought of that earlier but didn't do it, I should of so tried that.  . $count now returns 99 hits. 

 

Thanks a bunch. 

 

then you probably aren't returning any rows from that query. I'm going to assume that you indeed have rows that were added on this day. so make sure that you have the format of your date right, and its as you expect. Oh and one thing. I'm going to assume your date column is a varchar data type? Well since you don't surround it with quotes in your query, it may be assuming its an int (or rather it would be an equation, 2009 minus 9 minus 10)

 

try

 

$Hits = mysql_query("SELECT * FROM links WHERE date='" . $date . "' ORDER BY id DESC LIMIT 0,500") or die('Could not connect: ' . mysql_error());

 

and see if that helps

 

also echo your query before it executes to make sure its what you expect it to be