using optgroup with loops

richei · March 31, 2012, 5:19pm

I’ve got the strangest problem. Basically, all my sales scripts are identical in code between the backend and public. The strange part is catagory code works on the public side, but not the admin/backend. The only difference is that the public has a WHERE clause the query.

query:
[php]
$qry_a = mysql_query(
“SELECT start_date, end_date
FROM venzo_itunes_sales
GROUP BY start_date, end_date
ORDER BY start_date”
) or die(mysql_error());
[/php]

diplay code
[php]

Select the Date Range:

<?php $sub = array(); $sub[] = "";

while($r = mysql_fetch_array($qry_a)) {
		
	$tm = explode('/', $r['start_date']);
	$yr = $tm[2];
		
	$dte = $r['start_date']. " - " .$r['end_date'];
		
	if($prev_yr != $yr) {
		$sub[] = "<optgroup label='$yr'>";
		$prev_yr = $yr;
	}
	$sub[] .= "<option value='$dte'>$dte</option>";
			
	if($prev_yr != $yr) {
		$sub[] = "</optgroup>";
		$prev_yr = $yr;
	}
}
$sub[] = "</select>";
	
for($k = 0; $k < count($sub); $k++) {
	echo $sub[$k];
}

?>

[/php]

I’m trying to sort it by the year, so it’ll look like

2012
range 1
range 2
2011
range 1
range 2
2010
range 1
range 2
2009
range 1
range 2

and so on.

I’ve never had to use optgroup within a loop before, actually i’ve never used it all. All the examples that i can find on the net are object based, and i don’t understand any of that and don’t have time to completely rewrite the scripts.

If anyone can point me in the right direction or can see what i’m doing wrong, i’d grealty appreciate it.

richei · April 1, 2012, 1:22am

well, after doing some experimentation, it looks like the culprit is the query. If i remove the GROUP BY and ORDER BY, it kinda works, it just doesn’t do any sorting, and none of the array sorting functions seem to make any difference.

The display code has changed to:
[php]

<?php $ar = ""; while($r = mysql_fetch_array($qry_a)) { $tm = explode('/', $r['start_date']); if($tm[2] != $ar ) { if($ar != "") { echo "\n"; } echo "\n"; $ar = $tm[2]; } echo "$r[start_date] - $r[end_date]\n"; } echo "\n"; ?>

[/php]

richei · April 3, 2012, 5:21am

So no one has any ideas on how to make this work?

Laffin · April 3, 2012, 7:11am

I wudda just removed the group by
since you are handling with php code

richei · April 3, 2012, 3:07pm

I tried that, didnt work

richei · May 18, 2012, 6:51am

Well, i figured something out on this yesterday. As long as there’s no common months between the years, everything is fine, but once there is, it breaks. This is essentially what i get

2012 common date 2011 common date 2012 non common date 1 non common date 2 non common date 3 2011 non common date 1 non common date 2 non common date 3

Anyone have any suggestions? i really need to get this done so i can get paid and i can’t do another part until i get this figured out.