Jump to content

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


keldorn

Recommended Posts

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....?    


}

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. :)

Link to comment
Share on other sites

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');
?>

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

 

Thanks a bunch.  :D

 

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.