Topic: PHP related table question (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=28043" title="Pages that link to Topic: PHP related table question (Page 1 of 1)" rel="nofollow" >Topic: PHP related table question <span class="small">(Page 1 of 1)</span>\

 
Boudga
Maniac (V) Mad Scientist

From: Jacks raging bile duct....
Insane since: Mar 2000

IP logged posted posted 06-07-2006 06:56 Edit Quote

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!!!

divinechaos
Nervous Wreck (II) Inmate

From:
Insane since: Dec 2001

IP logged posted posted 06-07-2006 09:16 Edit Quote

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)

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

IP logged posted posted 06-07-2006 10:45 Edit Quote
code:
$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?

H][RO
Bipolar (III) Inmate

From: Australia
Insane since: Oct 2002

IP logged posted posted 06-07-2006 13:50 Edit Quote

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.

Boudga
Maniac (V) Mad Scientist

From: Jacks raging bile duct....
Insane since: Mar 2000

IP logged posted posted 06-07-2006 15:03 Edit Quote

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

Boudga
Maniac (V) Mad Scientist

From: Jacks raging bile duct....
Insane since: Mar 2000

IP logged posted posted 06-07-2006 16:50 Edit Quote

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

divinechaos
Nervous Wreck (II) Inmate

From:
Insane since: Dec 2001

IP logged posted posted 06-08-2006 07:44 Edit Quote

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

Boudga
Maniac (V) Mad Scientist

From: Jacks raging bile duct....
Insane since: Mar 2000

IP logged posted posted 06-08-2006 17:05 Edit Quote

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



Post Reply
 
Your User Name:
Your Password:
Login Options: Remember Me On This Computer
 
Your Text:
Loading...
Options: Show Signature
Enable Slimies
Enable Linkwords

« BackwardsOnwards »

Show Forum Drop Down Menu