Grouping and PHP with mysql

[dvacole]

Hi

 

I'm fairly noobish with both php and mysql and learning as I go. However I am running into trouble with a report I want to render.

 

My plan is to have a user select the date range that they would like the report to be for and then have then grouped by week for that date range

 

For example let's say the users wants the report for 01/01/2008 to 03/01/2008. I'm just fine pulling up the entire list with an array and displaying the whole range, but I don't know how to have it grouped by week in PHP using a while statement or if that is even the correct way of doing it.

 

I would like the end result to look somthing like this:

 

 

Title	title	title
date	total	total
date	Subtotals
date	total

and so one for the date range.

[Nhoj]

You might want to look into the group by function, I think it is what you're looking for:

 

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

[GingerRobot]

I think what you wanted is something like the following, where you order by one field in the query, cycle through the results and only ouput that field if the value is different from last time the loop ran.

 

$sql ="SELECT field1,field2,field3 FROM yourtable ORDER BY field1";
$result = mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);
$prev = '';
while($row = mysql_fetch_assoc($result)){
if($row['field1'] != $prev){//the value has changed since last time the loop ran
	echo $row['field1'].'<br />';
	$prev = $row['field1'];
}
echo $row['field2'].$row['field3'].'<br />';
}

 

Bit difficult to give you anything more than that without more information.

[dvacole]

Thanks I'll try that out.

[dvacole]

Okay, I tried that and I'm getting an invalid supplied argument.

 

This is what I have:

 

$week1 = "SELECT week((date(shift.sdate))),UNIX_TIMESTAMP(date(shift.sdate)), shift.sdate, sum(fast.level1), sum(fast.level2), sum(fast.level3), sum(fast.level4), sum(fast.sales), sum(fast.redeemed) from `fast`, `shift` Where sdate between $startdate and $enddate GROUP BY date(shift.sdate)";

$result=mysql_query($week1);

while ($row = mysql_fetch_array($result))

{

$prev=$row{'week(date(shift.sdate))'};

if ($row{'week(date(shift.sdate))'}=$prev){;

        and formating info blah....//this should hopefully format the individul days that match the same week as the other//

}

else {

;

$weektotal="SELECT week((date(shift.sdate))), sum(fast.level1), sum(fast.level2), sum(fast.level3), sum(fast.level4), sum(fast.sales), sum(fast.redeemed) from `fast`, `shift` Where week(sdate) = $weeknumber group by week(sdate) LIMIT 0, 30 ";

$weektotallookup=mysql_query($weektotal);

while ($test=mysql_fetch_array($weektotallookup));{

// and this should inclide a row that has the sums fot the week.

 

 

However I'm getting an invalid arugement for $test.

 

I'm totally lost.

I've inlcuded a link to the entire code if it would help as a text file.

www.dvacole.com/famonthlyreport.txt

 

 

 

 

[GingerRobot]

You need to debug your queries. Basic idea:

 

$sql = "SELECT * FROM..."
$result = mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);
if(mysql_num_rows($result) > 0){
    while($row = mysql_fetch_assoc($result)){
        //loop through results
    }
}else{
    echo 'No results found';
}

 

Also, please try to use

tags when you post.

[dvacole]

Great! got it solved! I the query was dying. Thanks for all the help!