Clickable Aphabetical Listing from MySQL

I've got a list of towns in a MySQL database, which I currently pull from
the database ... e.g. http://www.local-hotel.com/townsall/AU/

I now want to put a [A] [b] [C] etc menu at the top, which will click to the
start of the towns with that letter using index.html#A .... I am guessing
that the best way would be to simply compare the first letter of the current
town to the first letter of the previous town. However, I would also need to
put in non-clickable menu letter (i.e. there are no towns beginning with Z)

How'd you lot plan this simple task?

Thanks

Nick

re: Clickable Aphabetical Listing from MySQL

elyob wrote:[color=blue]
>I've got a list of towns in a MySQL database, which I currently pull from
>the database ... e.g. http://www.local-hotel.com/townsall/AU/
>
>I now want to put a [A] [b] [C] etc menu at the top, which will click to the
>start of the towns with that letter using index.html#A .... I am guessing
>that the best way would be to simply compare the first letter of the current
>town to the first letter of the previous town. However, I would also need to
>put in non-clickable menu letter (i.e. there are no towns beginning with Z)
>
>How'd you lot plan this simple task?
>
>Thanks
>
>Nick
>[/color]

Start with
select distinct substring(town, 1, 1) from table order by 1
to get all the first letters for which there are available towns.

Based on that, build the [A] [b] [C] etc menu, with all the letters
but no link to unavailable towns.


Makes sense?

--
"Yes, I'm positive."
"Are you sure?"
"Help, somebody has stolen one of my electrons!"
Two atoms are talking:

re: Clickable Aphabetical Listing from MySQL

Message-ID: <7mYKa.8216$Vo.57615461@news-text.cableinet.net> from elyob
contained the following:
[color=blue]
>I now want to put a [A] [b] [C] etc menu at the top, which will click to the
>start of the towns with that letter using index.html#A .... I am guessing
>that the best way would be to simply compare the first letter of the current
>town to the first letter of the previous town. However, I would also need to
>put in non-clickable menu letter (i.e. there are no towns beginning with Z)[/color]

I did a similar think by feeding values from a drop down box into a query.
http://www.ckdog.co.uk/php/abstracts.php

To display all towns beginning with a certain letter, assign the output of
the drop down box to variable $letter and do this:

SELECT field1, feld2,townetc FROM tbltable WHERE town LIKE'$letter%'
$order";

The variable $order is there so that you can change how the results are
displayed. You may want to order the results on a different field, say a
date field or something.

--
Geoff Berrow
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

re: Clickable Aphabetical Listing from MySQL

elyob wrote:[color=blue]
>That's probably a better way then using the PHP method I was thinking of,
>although I would want to show a greyed out letter for the substrings that
>don't exist ...
>
>e.g. [A] [b] C [D] [E] [F] ...
>
>So here, C is shown but has no href # as there are no towns beginning with C
>..[/color]

You can create a table with all the letters and join it with the towns
table for a better approach.

select letter, count(town) from letters left join town
on letter=substring(town, 1, 1) group by letter

My test run returned something like this

letter count(town)
A 1
B 1
C 0
D 1
E 1

Now, just go through the result array
printing the link if count(town) != 0



Happy Coding :)

--
"Yes, I'm positive."
"Are you sure?"
"Help, somebody has stolen one of my electrons!"
Two atoms are talking:

re: Clickable Aphabetical Listing from MySQL

"hex kid" <hexkid@hotpop.com> wrote in message
news:f177f47492453fa4f48396ebb905ded5@news.meganet news.com...[color=blue]
> elyob wrote:[color=green]
> >That's probably a better way then using the PHP method I was thinking of,
> >although I would want to show a greyed out letter for the substrings that
> >don't exist ...
> >
> >e.g. [A] [b] C [D] [E] [F] ...
> >
> >So here, C is shown but has no href # as there are no towns beginning[/color][/color]
with C[color=blue][color=green]
> >..[/color]
>
> You can create a table with all the letters and join it with the towns
> table for a better approach.
>
> select letter, count(town) from letters left join town
> on letter=substring(town, 1, 1) group by letter
>
> My test run returned something like this
>
> letter count(town)
> A 1
> B 1
> C 0
> D 1
> E 1
>
> Now, just go through the result array
> printing the link if count(town) != 0
>
>[/color]

I'm now thinking of moving away from the MySQL approach .. we run multiple
sites all using the same code. This is going to affect all the sites. Some
of the sites are UK only, although the hotel site is worldwide. This extra
table probably isn't what I need when I already hold the details.

I'm now thinking along the lines of holding an array of the standard
alphabet, and probably passing the previous first letter for comparison
purposes throughtout the 'while' loop. If the new letter's not the next
letter in the alphabet, output it, repeat until they match, then output a
link ...

I'm just trying to figure out how this is going to work ...

Thanks for your help so far ..
Nick

re: Clickable Aphabetical Listing from MySQL

"elyob" <newsprofile@hotmail.com> wrote in message
news:7mYKa.8216$Vo.57615461@news-text.cableinet.net...[color=blue]
> I've got a list of towns in a MySQL database, which I currently pull from
> the database ... e.g. http://www.local-hotel.com/townsall/AU/
>
> I now want to put a [A] [b] [C] etc menu at the top, which will click to[/color]
the[color=blue]
> start of the towns with that letter using index.html#A .... I am guessing
> that the best way would be to simply compare the first letter of the[/color]
current[color=blue]
> town to the first letter of the previous town. However, I would also need[/color]
to[color=blue]
> put in non-clickable menu letter (i.e. there are no towns beginning with[/color]
Z)[color=blue]
>
> How'd you lot plan this simple task?
>[/color]

Thanks, I have it working using a simple array, php substr and html#. Not
100% finished as it shows the letters of those that have no towns with those
letters. But hey, who's picky!

The suggestions I received were extremely helpful in making my mind up on
how to do it. Once again, thanks.

Nick