Displaying all results, but in groups

[BlackAce]

I need some assistance getting my results to display the way I intend. Perhaps this belongs in the PHP forum--if so, let me know.

 

Basically, I am creating a page that lists all of the facilities that have open positions in a particular specialty area. I want them to display as follows:

 

<h1>State Name</h1>
<h3>Facility Name #1</h3>
<p>Facility City, Facility State</p>
<h3>Facility Name #2</h3>
<p> Facility City, Facility State</p>
<h1>State Name #2</h1>
<h3>Facility Name #3</h3>
<p>Facility City, Facility State</p>

 

The PHP code I've included below does this, but repeats the state name for each facility. I want this to show the state name once, then all the facilities in that state below it.

 

You'll notice that I have commented out the "GROUP BY facility_state" part of the query, which does group them by state, but only displays the first facility in that state, not all of them.

 

Please pardon the newbness, I'm just starting at all this!    Thanks!

 

 

$query = "SELECT facility_info.facility_ID as fID, facility_name, facility_city, facility_state, open_positions.position, open_positions.status FROM facility_info, open_positions WHERE facility_info.facility_ID=open_positions.facility_ID AND open_positions.status!='NULL' ORDER BY facility_state, facility_name"; //GROUP BY facility_state 

$results = mysql_query($query) or die(mysql_error());

$prev_id=0;
$num=mysql_num_rows($results);

for($i=0;$i<$num;$i++){  // loop for each open position  
$f=mysql_fetch_array($results);
if($prev_id!=$f["fID"] && $f["position"]==$specialty){  // display facility info    
	if($prev_id)
	echo '</h3>';
	echo '<h2 style="margin-bottom:0px;">'.htmlspecialchars($f["facility_state"]).'</h2>';
	echo '<h3 style="margin-bottom:0px;">'.htmlspecialchars($f["facility_name"]).'</h3>';
	echo htmlspecialchars($f["facility_city"]);
	echo ', ';
	echo htmlspecialchars($f["facility_state"]);
	$prev_id=$f["facility_ID"];
}  

}
echo '</h3>';

mysql_close();
?>

[kickstart]

Hi

 

GROUP BY is used to group similar rows together to get an aggregate value. For example, say you had a list of cities and states with their populations, you would use GROUP BY to get the highest population for each state. If you use GROUP BY without having the same fields in the group by as you are selecting, then the non aggregate fields in the select that are not in the GROUP BY will be from an indeterminate row.

 

Normal way to solve this would be to select the full details in the appropriate order, and only display some of the fields when their value changes. As such a PHP issue.

 

Something like this (please excuse any typos).

 

$query = "SELECT facility_info.facility_ID as fID, facility_name, facility_city, facility_state, open_positions.position, open_positions.status FROM facility_info, open_positions WHERE facility_info.facility_ID=open_positions.facility_ID AND open_positions.status!='NULL' ORDER BY facility_state, facility_name";
$results = mysql_query($query) or die(mysql_error());
$prev_id=0;
$num=mysql_num_rows($results);

$prev_state = '';
while($f=mysql_fetch_array($results))
{
if($prev_id!=$f["fID"] && $f["position"]==$specialty){  // display facility info    
if($prev_id)
echo '</h3>';
if ($f["facility_state"] != $prev_state)
{
echo '<h2 style="margin-bottom:0px;">'.htmlspecialchars($f["facility_state"]).'</h2>';
$prev_state = $f["facility_state"];
}
echo '<h3 style="margin-bottom:0px;">'.htmlspecialchars($f["facility_name"]).'</h3>';
echo htmlspecialchars($f["facility_city"]);
echo ', ';
echo htmlspecialchars($f["facility_state"]);
$prev_id=$f["facility_ID"];
}  

}
echo '</h3>';

mysql_close();
?>

 

All the best

 

Keith

[BlackAce]

You're exactly right. Thank you for taking time to give explanation, that really helps me get a better grasp on these functions.

 

I really appreciate it.