Page 1 of 1

Using a MySQL result set with PHP

Posted: Mon Feb 20, 2006 6:19 am
by BigJonMX
Hello everyone,
i'm stuck on how to complete the following. Can any of you knowlegable programmers could help me out...

I select a bunch of records from a table:
r_id=0 r_year=2004 r_month=6 r_value=20
r_id=1 r_year=2004 r_month=8 r_value=10
r_id=2 r_year=2005 r_month=3 r_value=35
r_id=3 r_year=2006 r_month=1 r_value=18
These are end of month values, but i need the total from first record to now
My problem is that the missing months will have the same value as the last record
So the total will be... Tot=20+20+10+10+10+10+10+10+10+35+35...
(2004, 6) 20
(2004, 7) 20
(2004, 8) 10
(2004, 9) 10
(2004,10) 10
(2004,11) 10
(2004,12) 10
(2005, 1) 10
(2005, 2) 10
(2005, 3) 35
(2005, 4) 35
(2005, 5) 35
etc...
Is this clear? i hope so.

Code: Select all

$rH=mysql_query($qH, $conDB) or die(mysql_error()." Q=".$qH); 
$rowsH=mysql_num_rows($rH);
// i've tried looping through the records, but its very difficult (for me)
$r=0; while ($rowH=mysql_fetch_assoc($rH)): extract($rowH);
if ($i=0): $tot=$r_value; endif;
if ($r>0): $tot=$tot+$r_value;  endif; // i guess here i need some code to multiply the value by the number of months...aaahhhh....
$i=$i+1;
endwhile;
Any help please
Jon

Inefficient Solution

Posted: Mon Feb 20, 2006 7:38 am
by BigJonMX
ok. heres my solution.
i get the first record (the oldest one)
loop from this date to present day
for each date request a record
if it exists use new values
---------
this appears to work, but i dont like the numerous MySQL SELECTs that are needed.
Any comments ?????
---------

Code: Select all

// Todays Date Info
$Hy=date('Y'); $Hm=date('m');
// Get Previous Monthly Totals - First Date
$qH="SELECT * FROM mytable WHERE (yy<".$Hy." OR (yy=".$Hy." AND mm<".$Hm.")) ORDER BY yy ASC, mm ASC Limit 0,1";
$rH=mysql_query($qH, $conDB) or die(mysql_error()." Q=".$qH); $rowsH=mysql_num_rows($rH);
if ($rowsH>0): $calctot='yes'; else: $calctot='no'; endif;
$rowH=mysql_fetch_assoc($rH); extract($rowH); $this_yy=$yy; $this_mm=$mm; $tot=$cost;
// Rest of records
while (($this_yy<$Hy) OR (($this_yy==$Hy) AND ($this_mm<$Hm))):
	echo 'this_yy='.$this_yy.' this_mm='.$this_mm.'<br />';
	$this_mm=$this_mm+1; if ($this_mm==13): $this_yy=$this_yy+1; $this_mm=1; endif;
	$qONE="SELECT * FROM mytable WHERE yy=".$this_yy." AND mm=".$this_mm;
	$rONE=mysql_query($qONE, $conDB) or die(mysql_error()." Q=".$qH); $rowsONE=mysql_num_rows($rONE);
	if ($rowsONE==1): $rowONE=mysql_fetch_assoc($rONE); extract($rowONE); $this_yy=$yy; $this_mm=$mm; endif;
	$tot=$tot+$cost;
	echo $cost.' tot='.$tot.'<br />';
endwhile;