Formatting SQL query results

[haasPlay]

I'm working on developing a add on that will allow store owners to enter retail locations of where to buy their products through the admin.

I have all of the admin files ready and tested. I also have the front end files working but would like to tweak the formatting of the query results....

I would like to make the query once, then if the state equals something (say CA) then all stores in CA would be printed with a fieldset around the results. The printing should then move on the next state and print those results. I can get it working with a WHERE statement in the query, but I don't want to do 50 queries, just one.

Here's what I have:

global $db;

 
 

$row = 0;

$stores = $db->Execute("SELECT stores_name, stores_address_1, stores_address_2, stores_city, stores_state, stores_zip, stores_phone, stores_url 
                        from " . TABLE_STORES . "
                        WHERE stores_state = 'ca'
                        order by stores_name" );

if ($stores->RecordCount() > 0)                    
{
echo '<fieldset><legend>California</legend><table cellpadding="2" cellspacing="0" width="100%"><tr>';
while (!$stores ->EOF) {

$row++;

echo '<td valign="top" width="33%">';

echo $stores->fields['stores_name'] . '<br />';
if ($stores->fields['stores_address_1']) {
echo $stores->fields['stores_address_1'] . '<br />';
}
if ($stores->fields['stores_address_2']) {
echo $stores->fields['stores_address_2'] . '<br />';
}
if ($stores->fields['stores_city']) {
echo $stores->fields['stores_city'] . ', ';
}
if ($stores->fields['stores_state']) {
echo $stores->fields['stores_state'] . ' ';
}
if ($stores->fields['stores_zip']) {
echo $stores->fields['stores_zip'] . '<br />';
}
if ($stores->fields['stores_phone']) {
echo $stores->fields['stores_phone'] . '<br /> ';
}

if ($stores->fields['stores_url']) {

echo '<a href="http://' . $stores->fields['stores_url'] . '"target=_blank">'. $stores->fields['stores_url'] . '</a>';





echo "<br /><br />\n";

echo '</td>';

}

if ((($row / 3) == floor($row / 3))) {

?>



</tr><tr>



<?php

}

$stores->MoveNext();

}

 



echo '</tr></table></fieldset>';
}

I think I need to make the stores_state a variable based on the query but I can't get it to work. Any guidance would be greatly appreciated.

Thanks.

[DrByte]

1. Change your query to remove the WHERE clause and change your ORDER BY clause to be: stores_state ASC, stores_name

2. Move your "echo" for the fieldset to inside the while loop, where indicated in the next item:

3. Just before the $row++ add some new lines:

$display_fieldset_wrapper = false;
if ($last_state != $stores->fields['stores_state']) {
  $last_state = $stores->fields['stores_state'];
  $display_fieldset_wrapper = true;
}
if ($display_fieldset_wrapper) {
  echo .......do your fieldset echo here
}

4. before the MoveNext() command, add another echo:

if ($display_fieldset_wrapper) {
  echo .......do your /fieldset echo here
}