Professional Web Applications Themes

Clickable Aphabetical Listing from MySQL - PHP Development

I've got a list of towns in a MySQL database, which I currently pull from the database ... e.g. [url]http://www.local-hotel.com/townsall/AU/[/url] 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 ...

  1. #1

    Default 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. [url]http://www.local-hotel.com/townsall/AU/[/url]

    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


    elyob Guest

  2. #2

    Default Re: Clickable Aphabetical Listing from MySQL

    elyob wrote:
    >I've got a list of towns in a MySQL database, which I currently pull from
    >the database ... e.g. [url]http://www.local-hotel.com/townsall/AU/[/url]
    >
    >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
    >
    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:
    hex kid Guest

  3. #3

    Default Re: Clickable Aphabetical Listing from MySQL

    elyob wrote:
    >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
    >..
    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:
    hex kid Guest

  4. #4

    Default Re: Clickable Aphabetical Listing from MySQL

    Message-ID: <7mYKa.8216$Vo.57615461news-text.cableinet.net> from elyob
    contained the following:
    >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)
    I did a similar think by feeding values from a drop down box into a query.
    [url]http://www.ckdog.co.uk/php/abstracts.php[/url]

    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 [url]http://www.ckdog.co.uk/rfdmaker/[/url]
    Geoff Berrow Guest

  5. #5

    Default Re: Clickable Aphabetical Listing from MySQL


    "hex kid" <hexkidhotpop.com> wrote in message
    news:f177f47492453fa4f48396ebb905ded5news.meganet news.com...
    > elyob wrote:
    > >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
    > >..
    >
    > 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
    >
    >
    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



    elyob Guest

  6. #6

    Default Re: Clickable Aphabetical Listing from MySQL


    "elyob" <newsprofilehotmail.com> wrote in message
    news:7mYKa.8216$Vo.57615461news-text.cableinet.net...
    > I've got a list of towns in a MySQL database, which I currently pull from
    > the database ... e.g. [url]http://www.local-hotel.com/townsall/AU/[/url]
    >
    > 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, 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



    elyob Guest

Similar Threads

  1. Do banner ads need to be clickable
    By bilbo--baggins in forum Adobe Flash, Flex & Director
    Replies: 6
    Last Post: June 20th, 01:54 AM
  2. any clickable control
    By Corno in forum ASP.NET Web Services
    Replies: 1
    Last Post: September 6th, 06:09 PM
  3. clickable boundcolumn is it possible
    By rob merritt in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: February 17th, 11:00 PM
  4. Buttons are still clickable
    By Diederik in forum Macromedia Flash
    Replies: 6
    Last Post: November 2nd, 12:24 PM
  5. Clickable area
    By yiannisezn webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 3
    Last Post: September 26th, 08:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139