Need help displaying query results

Anon

I have an easy one for you. Unfortunately it is not so easy for me. This is what I am trying to to do. I am running a simple query to extract all agencies in ny:

Now I want to display the results like this:

A
About Artists Agency, Inc.
355 Lexington Avenue, 17th Floor
New York, NY 10017
Phone: (212)490-7191
Abrams Artists Agency
275 Seventh Avenue, 26th Floor
New York, NY 10001
Phone: (646) 486-4600
back to top

B
Barry-Haft-Brown Artists Agency (B-H-😎 (SAG-AFTRA-EQUITY)
165 West 46th Street, Suite 908
New York, NY 10036
(212) 869-9310
Richard Bauman & Associates (SAG-AFTRA-EQUITY)
250 West 57th Street, Suite 2223
New York, NY 10107
(212) 757-0098
back to top

Specifically I want to develop an array that displays the listings with a header of the according letter ("A") then displays all the listings that start with the letter "A". Then when all the A's are finished put an anchored "back to top".
Then all the listings with the letter "B" and so on.

This is what I have so far but it just displays all the listings:

<?//display results
$NumRecords = 0;
while(($row = mysql_fetch_array($result)))
{$NumRecords++;?>

<? print $row["CompanyName"]; ?> 
<? print $row["Address"]; ?> 
<? print $row["City"]; ?>, <? print $row["State"]; ?>
<? print $row["ZIPCode"]; ?> 
<? if ($row["Phone"]) print "Phone: " . $row["Phone"] . " "; ?>
<? if ($row["Fax"]) print "Fax: " . $row["Fax"] . " "; ?>
<? if ($row["Country"]<>'US') print $row["Country"] . " "; ?>
<? if ($row["EMail"]) print "EMail: <a href=\"mailto:" . $row["EMail"] . "\">" . $row["EMail"] . "</a> "; ?>
<? if ($row["WebSite"]) print "WebSite: <a href=\"http://" . $row["WebSite"] . "\" target=\"_blank\">" . $row["WebSite"] . "</a> "; ?>

<?} //end of while loop
if (0 == $NumRecords) print "No talent agencies are currently listed in " . $VLocation . ". ";
?>

Thank you in advance.🙂

fitzbean

for ($i = 65; $i < 91; $i++) {

$Letter = chr($i) . "%";

$result = mysql($db,"SELECT * FROM $table WHERE CompanyName = '$Letter'")

while(($row = mysql_fetch_array($result)))

<? print $row["CompanyName"]; ?> 
<? print $row["Address"]; ?> 
<? print $row["City"]; ?>, <? print $row["State"]; ?>
<? print $row["ZIPCode"]; ?> 
<? if ($row["Phone"]) print "Phone: " . $row["Phone"] . " "; ?>
<? if ($row["Fax"]) print "Fax: " . $row["Fax"] . " "; ?>
<? if ($row["Country"]<>'US') print $row["Country"] . " "; ?>
<? if ($row["EMail"]) print "EMail: <a href=\"mailto:" . $row["EMail"] . "\">" . $row["EMail"] . "</a> "; ?>
<? if ($row["WebSite"]) print "WebSite: <a href=\"http://" . $row["WebSite"] . "\" target=\"_blank\">" . $row["WebSite"] . "</a> "; ?>

<?} //end of while loop
}
if (0 == $NumRecords) print "No talent agencies are currently listed in " . $VLocation . ". ";
?>

That should do it.

fitzbean

Oh just throw a line before the while loop to handing the printing of the letter before each letter listing: ie:

<? print $Letter; ?>

Anon

<?
$NumRecords=0;
unset($companies);

while($row=mysql_fetch_array($result))
{++$NumRecords;
$firstletter=strtoupper(substr($row['CompanyName'],0,1));
$blocks[$firstletter][]=$row;
}

You'll then have an array $blocks. Each element of the array is indexed by a letter 'A' - 'Z', and is itself an array containing the companies that have names starting with that letter. And of course each company is represented by the array returned for it by mysql_fetch_array().

Eg., $blocks['B'] lists companies with names starting with 'B'. B-H-B might be $blocks['B'][0], and $blocks['B'][0]['ZIPCode'] would then be 10107.

To get your listing:

foreach($blocks as $letter=>$block)
{
print $letter.' ';
foreach($block as $company)
{
print $company['CompanyName'].' ';
print $company['Address'].' ';
...
}
}

A bonus is that only the letters that actually have companies under them are printed - If there aren't any companies with names starting with 'Z', it won't print a 'Z'. Also, it will list an outfit with a name like "123-Go" under '1'.

No sorting is done, but this should be done by the SQL query anyway.

Anon

Thank you for the response. It is all very helpful.

Anon

Hayley
I ended up using your suggestion. It works great! Also I added another loop to diplay links to all the possible grouped listing
(a b c h f r z)
with a link down to listing beginning with the according letter.

And then when it prints the letter I have it print a named anchor to correspond with the prviously listed links.

Thanks again.

Joey