Grouping Data Results From MySQL

PHP Script to Display Grouped Data

[boxfan]

Hi, I'm hoping someone can point me in the right direction in figuring out the process of grouping data results from a MySQL query.

Here's what I need to do.

I have a database with a list of stores who are resellers for us. I want to create a page in PHP that displays the state name and then lists the stores who are resellers in that state.

Alabama

Store #1
street address 1
street address 2
city, state zip code

Store #2
street address 1
street address 2
city, state zip code

Arizona

Store #1
street address 1
street address 2
city, state, zip code

Etc.

I can not figure out the logic of pulling this data from the database and presenting it. The fields are pretty much divided as they are presented above: store_name, address1, address2, city, state, zipcode, phonenumber.

This is one of the pathetic variations that I tried

while ($row = $conn->fetch_object ()) {
if ($row->RL_state == "AL") {
echo "<p><a name=\"AL\">Alabama</a></p>";
echo "<hr>";
for($i=0; $i<$row->cnt; $i++) {
echo "<p><b>$row->RL_company</b><br />";
echo "$row->RL_street1<br />";
if ($row->RL_street2)
echo $row->RL_street2 . "<br />";
echo "$row->RL_city, $row->RL_state $row->RL_zipcode</p>";
}
}

Obviously this isn't the right logic.

Can someone help me get started?

[ChadSEO]

boxfan,

Something like this will do what you want:

$state = '';
while ($row = $conn->fetch_object ()) {
if($state!= $row->state) {
echo $row->state."<br><br>";
$state = $row->state;
}
echo $row->store_name.'<br>';
echo $row->address1.'<br>';
echo $row->address2.'<br>';
}

Anytime a new state is found, it'll print out the state name just once. When you're doing the query, make sure you order by state, and this should work fine.

Chad

[boxfan]

Nice! Thank you Chad.

[boxfan]

One other question.

How would I add the following line if there was not a store located in the state?

Alabama

No stores yet, check back soon

Alaska

Store #1
street1
street2
city, state, zip code
phone

California

No stores yet, check back soon

Assuming the below code is being used

$state = '';
while ($row = $conn->fetch_object ())
{
if($state!= $row->RL_state) {
echo "<hr>";
echo "<p><a name=\"" . $row->RL_state . "\">" . $row->RL_statename . "</a></p>";
$state = $row->RL_state;
}
echo "<p><b>" . $row->RL_company."</b><br />";
echo $row->RL_street1."<br />";
if ($row->RL_street2)
echo $row->RL_street2."<br />";
echo $row->RL_city . ",&nbsp;" . $row->RL_state . "&nbsp;" . $row->RL_zipcode . "<br />";
echo $row->RL_phone . "</p>";
}

[ChadSEO]

boxfan,

If I were going to do it that way, then I would change things around a bit. You need to come up with a list of states, either a static array, or just another query. Then it goes a little something like this:

#Generate your list of states
$states = array('AL','AK'.....);

#Loop through list of all states
while (list($key, $state) = each($states )) {
echo "<hr>";
echo "<p><a name=\"" . $row->RL_state . "\">" . $row->RL_statename . "</a></p>";

# Query for stores just in that particular state, $store_count being the number of results

if($store_count == 0) {
echo "No stores yet, check back soon.<br>";
}
else {
while ($row = $conn->fetch_object ())
{
if($state!= $row->RL_state) {
$state = $row->RL_state;
}
echo "<p><b>" . $row->RL_company."</b><br />";
echo $row->RL_street1."<br />";
if ($row->RL_street2)
echo $row->RL_street2."<br />";
echo $row->RL_city . ",&nbsp;" . $row->RL_state . "&nbsp;" . $row->RL_zipcode . "<br />";
echo $row->RL_phone . "</p>";
}
}

Basically, loop through the list of states, and do a separate query for each state. If there are no results, output the "Sorry" message, otherwise print out the stores like before. Hope that helps!

Chad

[boxfan]

That's exactly what I needed. Thanks.