Topic awaiting preservation: PHP related table question (Page 1 of 1)

Hi all!

Been a long time since I posted last...been busy eeking out a living. Anyway I need a bit of help and/or suggestion on how to handle some math after I run a php query of a mysql db. I spit the data into a table. The data consists basically of dates and how many unique visitors a website has received. I would like to add a column that adds each dates unique hit count to a culmination of all the previous days hits combined. See example...

+-----------+---------+-----------------+
| date | unique | cumulative |
+-----------+---------+-----------------+
|20050606 | 20 | 121 |
|20050605 | 23 | 101 |
|20050604 | 50 | 78 |
|20050603 | 20 | 28 |
|20050602 | 6 | 8 |
|20050601 | 2 | 2 |
+-----------+---------+-----------------+

Thanks in advance for any and all help!!!

I would parse it by line, and use $c as the cumulative result. I would use preg_match_all with something like /[0-9]+/, I would intval() the resultant value I wanted, add it to $c, and append it and the delimiter to the end, output the resultant line to an output buffer. If empty($matches), ignore the line (rather, just echo it unchanged).

Best of luck,
DC

(Edited by divinechaos on 06-07-2006 09:17)

$strQuery = "SELECT date, unique, cumulative FROM TABLE ORDER BY date ASC"
$hRes = mysql_query ( $strQuery );
$iCum = 0;
while ( $arrRow = mysql_fetch_array ( $hRes ) )
{
   $iCum += $arrRow['unique'];
    $strUpdateQuery = "UPDATE table SET cumulative = {$iCum} WHERE date = '{$arrRow['date']}'";
   mysql_query ( $strUpdateQuery );
}

Slow, and probably creates a lot of queries... but hey, for how many years are you gonna run this anyhow?

Do you need to store it in the database table, you might as well calculate it when you output the table results to html or whatever as you print each line. Kind of redundant storing it in the database, depending on what you are doing with it of course.

I have no need to store the data in the db. This is one of those, "Hey can you do this with the data" things that my boss asked for.

Here is what I have so far...

http://php.pastebin.com/765084

i figured it out with some help from a buddy...thanks!

Hahaha. I hope it's obvious I didn't think you had access to the db beyond doing the table dump; otherwise, using SQL vs regex was a rather blatantly obvious choice.

Cheers,
DC

Regex was a thought but I was concerned that it would cause excessive stress on the server for a huge query like this.