Clickable Aphabetical Listing from MySQL

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • elyob

    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


  • hex kid

    #2
    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:

    Comment

    • Geoff Berrow

      #3
      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.


      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/

      Comment

      • hex kid

        #4
        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=substrin g(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:

        Comment

        • elyob

          #5
          Re: Clickable Aphabetical Listing from MySQL


          "hex kid" <hexkid@hotpop. com> wrote in message
          news:f177f47492 453fa4f48396ebb 905ded5@news.me ganetnews.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=substrin g(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



          Comment

          • elyob

            #6
            Re: Clickable Aphabetical Listing from MySQL


            "elyob" <newsprofile@ho tmail.com> wrote in message
            news:7mYKa.8216 $Vo.57615461@ne ws-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



            Comment

            Working...