SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard edshuck's Avatar
    Join Date
    Jul 2000
    Posts
    1,200
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi folks

    a quick question for the mysql or php gurus.

    I have a table with a field for the telephone number. this includes the npa, nnx and the last 4 digits in the following order. 415 555 1234

    I have about 400 numbers in the field and would like to identify the quantity using count() (and of course GROUP) of the quantity of each area code. thus 201(1), 415(20), 800(47) etc.

    But I do not know how to call in wild cards to do the read of just the first 3 digits.

    The format is uniform, there are no 011's.

    Here is more or less where I am just now.

    SELECT phone FROM mytable ORDER BY phone asc;

    If I move the areacode to a seperate col. the problem is automatically solved. And I can do that one.

    But is there a way without adding a col.

    Thanks

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ed,

    I don't see why you can't use LEFT() for this task. This untested but should work

    Code:
    SELECT LEFT(phone, 3) as areacode, count(phone) as count FROM mytable group by phone
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,939
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    where phone like '123%'

    will return all phone numbers starting with 123.

    Get it?

    Now if you want to count them up and all of that you can do some grouping and looping I'm assuming you know how to do that?
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  4. #4
    SitePoint Wizard edshuck's Avatar
    Join Date
    Jul 2000
    Posts
    1,200
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi guys

    Thanks freddy. I need to just sit back and read the list of features. But, it sounds really good.

    Thanks Chris. Your answer fits well for another question that I had not thought of until you answered it.

    Kudos and Karma all round.

    peace

  5. #5
    SitePoint Wizard edshuck's Avatar
    Join Date
    Jul 2000
    Posts
    1,200
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    Tried freddy's idea and just a tad of modification got the following. And it does exactly what I think I need.

    SELECT LEFT(phone, 3) as areacode, count(phone) from mytable group by areacode.

    And the beauty is that the areacode exists seperately only temporarily in an alias as a seperate item.

    Thanks again to all.

  6. #6
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would use seperate columns for country code and area code. IMHO

  7. #7
    SitePoint Wizard edshuck's Avatar
    Join Date
    Jul 2000
    Posts
    1,200
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    Your suggestion is a good one. But I just want to handle the north american plan now. The addition of a column and a where statement in the code should do the trick.

    Thanks a lot.

    peace


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
  •