SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Sep 2004
    Location
    Arizona
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    display based on a words first letter

    Hey everyone, I'm getting better at mySQL, but I have a question.

    I'm organizing everything in my table 'lyrics' by a few things. The main thing I'll be displaying it in is 'artist'. I'd like to display all the artists that begin with a letter (a-z and #), how would I do this?

    Is there someway like...

    ...WHERE artist=a, or something like that. Sorry if I sound...slow in this email, I'm not great at wording myself when it comes to this stuff.

    Thanks in advance.

  2. #2
    SitePoint Addict
    Join Date
    Jan 2004
    Location
    Colorado Springs
    Posts
    308
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you haven't built your schema to categorize by A-Z, then you're best bet would be to query by using LIKE and wildcards. IE:

    WHERE artist LIKE '%a'

    This looks for all artists starting with a.

  3. #3
    SitePoint Member
    Join Date
    Sep 2004
    Location
    Arizona
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot, that works!

    Now I've got two questions, sorry, I know how annoying it is to have someone constantly ask questions. Don't think it's for a lack of trying...=/

    1) How would I display an artist that has only numbers in their name, or begins with a number (a la 10,000 maniacs)?

    2) This one will be kind of tough, I imagine. Maybe not.

    I have A table lyrics with the columns: ID, artist, album, song, lyrics

    Now, let's say I choose to display all artists beginning with the letter a, as illustrated above. I don't want to display the same artist 25 times each time they appear in the database. I want to display them once, and only once.

    How will I go about doing this?

  4. #4
    SitePoint Member
    Join Date
    Aug 2004
    Location
    mishawaka, in
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I would do is select * from table order by artist. Then loop through the results:

    Code:
    $sql = "SELECT * FROM table_name ORDER BY artist";
    $r = mysql_query($sql);
    
    while ($row=mysql_fetch_array($result))
    {
       if ($prev_artist != $row[artist])
       {
          echo "$row[artist]";
       }
        $prev_artist = $row[artist];
    }
    If you are ordering by artist then all the identical ones will be in a row. Compare if the previous is different from the current. If it is display it, if it isn't do nothing. Then I set the previous equal to the current for the next iteration. Hope this makes sense.

  5. #5
    SitePoint Member
    Join Date
    Sep 2004
    Location
    Arizona
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    solid. Thank you very much

    now if someone could help me with the numbers question, I'll be set =D

  6. #6
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Similar for numbers...
    Code:
    WHERE artist LIKE '%1'
    as that field is text.
    Also, best to change the above...
    Code:
    $r = mysql_query($sql);
    to
    Code:
    $result = mysql_query($sql);
    to match the rest of the code
    Lats...


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
  •