Summary list? It must be simple yes?

Fritz

my lists are (field city and field name) ...

a basic select will list them like this...

boston tom
boston mary
boston tina
miami rick
miami ned

i'd like to sub group them to display as...

boston
tom
mary
tina

miami
rick
ned

how can I do this?

DeltaRho2K

// First query to get the city's names (SUB your field names accordingly)
$city = mysql_query("SELECT [B]cityname[/B] FROM [B]tablename[/B] ORDER BY [B]cityname[/B]");

// Loop through the results to display each city name
// We define the $cityname variable here, so we can use it in a subsequent querying loop.
While ($city = mysql_fetch_array($cities)):
  $cityname = $city['cityname'];
  echo $cityname."<BR>";

// While in the loop, run another query so you can get each name for the given city.
// (You have to think outside the box here.)  Remember, each loop shows one city name, so that variable is used in each loop of the following query. 
   $names = ("SELECT firstname FROM tablename WHERE cityname = '$cityname' ORDER BY firstname");

// Now we loop the names for each city.
     while ($name = mysql_fetch_array($names)):
     echo $name['firstname']."<BR>";

// We end our first nested while loop.     

     endwhile;

// Now we end our outside while loop.
endwhile;

slick

An alternative if you want to reduce the number of calls to the database would be the query:

SELECT firstname,cityname FROM tablename ORDER BY cityname

Then, when processing the results, simply keep track of the current city name, and only print it when it changes.

Either way works fine though!

jung

I'll try the first on tonight when I get home to get a feel for it.

basically a while statement inside of a whild statement?

thanks.

jung

Originally posted by slick
SELECT firstname,cityname FROM tablename ORDER BY cityname

Then, when processing the results, simply keep track of the current city name, and only print it when it changes.

interesting... how would I 'keep track of the current city name, and only print it when it changes ????

DeltaRho2K

I don't think you can... I have tried that before many times, with no luck... That's why I nested a loop within a loop the way I did. I write all of my code using this method when I need to, and haven't created any significant load on my server at all using this method. (Even when looping thousands of records)

slick

It's quite straight-forward really:

// OK, let's assume I've done the query and checked that there are more than 1 results etc,
// before going into the results processing loop, set up a running variable.  

// We'll reset the value of this every time we come across a new city
$lastCityName = "";

// loop through our result set (remember, in my original query, I've ordered the results by cityname)
while($row=mysql_fetch_array($res)){
   $firstName = $row["firstname"];
   $cityName = $row["cityname"];

   // check if the name of the current city is different to the value of $lastCityName:
   if($cityName!=$lastCityName){
       // we have a new city, so print it, and update the value of our running variable:
       echo($cityName);   // you can apply your own print formatting here, this is just for illustration
       $lastCityName = $cityName;
   }

   // do whatever other results processing you need here...
}

So, no nested while loops!

Hope that's clear!
Slick

DeltaRho2K

Hey Slick that's great... The only thing I see with that is the limitation of printing the output.

If I am reading you correctly, yours will produce something like:

Boston Tom
INDENTED>John
INDENTED>Mary
New York Sam
INDENTED>Mary
INDENTED>Susan
etc...

(but this is the only way you would be able to output it...)

With a nested loop, you would have more options in displaying the results... including building a better table structure.

Boston
Tom
John
Mary

New York
Sam
Mary
Susan

If I am wrong, by all means, let me know... I guess I am having a tricky time visualizing your method without actually building my own example to play with...

slick

The code I posted was just a basic example. I have used this method to good success with ordered/unordered lists, complete table rows and so on. So long as you keep track of the city names changing you're all set.

For example, the following code is an extract of an existing script I have that displays a list of categorised Useful Links (see www.hickeyshikers.com/links.html) stored in a database table.

The script firstly collects all the categories, and displays these as quick links at the top of the page. Then, the links in each category are printed, with title (as a link to the destination URL) and description of the destination site.

          $currentCategory = 0;
          $catList = "Quick Links:<br>";
          $pageContent = "<table width='500' align='left' valign='top' border='0' cellpadding='2' cellspacing='0'>";
          while($row=mysql_fetch_array($res)){
                $linkID = $row['id'];
                $linkCat = $row['linkCategory'];

            if($linkCat!=$currentCategory){
                // new category, so print heading
                $currentCategoryName = getFieldByID("linkcats","linkcatsname","id",$linkCat);
                $pageContent .= "<tr>
                                  <td class='maintext'colspan='2'><a name='cat$linkCat' class='usefullink'>$currentCategoryName</a></td>
                                 </tr>";
                $catList .= "<a href='#cat$linkCat' class='usefullink'>$currentCategoryName</a><br>";
                $currentCategory = $linkCat;
            }

            $linkURL = stripslashes($row['linkURL']);
            $linkTitle = stripslashes($row['linkTitle']);
            $linkDescription = convertPseudo($row['linkDescription']);
            $separator = "<tr>
                           <td width='20'><br></td><td><br></td>
                          </tr>";

            if($linkTitle==""){
                $linkTitle = $linkURL;
            }
            $pageContent .= "<tr>
                              <td width='20'><br></td>
                              <td class='maintext'><a href='$linkURL' class='usefullink' target='new$linkID'>$linkTitle</a></td>
                             </tr>
                             <tr>
                              <td width='20'><br></td>
                              <td class='maintext'>$linkDescription</td>
                             </tr>" . $separator;
      }
      $pageContent .= "</table>";
      $catList .= "<hr width='200' align='left' color='#455A58' size='1' noshade><br><br>";

      // print the final results:
      echo("$catList<br><br>$pageContent");

As you can see, this code actually keeps a running store of two lists. Sorry it's not commented, but there's nothing too complex going on.

Note - the names of the link categories are stored in a different database table. The 'getFieldByID()' function simply gets the category name based on its id.

The 'convertPseudo()# function parses custom tags (similar to this forums') into true HTML.

With a little modification, you can get this style of coding to generate almost any type of printed output.

Hope that's clearer...
Slick

DeltaRho2K

That's great stuff Slick... I am going to write out some code today to try things out... Thanks.
~DR