SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast bind727's Avatar
    Join Date
    Jan 2003
    Location
    Calgary, Alberta
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help in the "WHERE" syntax

    here is what I am trying to do which I am having no luck with finding the syntax on mysql.com

    Code:
     
     $where .= " AND SUBSTRING(c.home_phone,0,4) LIKE '%".$_POST['areacode']."%' ";
    the idea is to search a phone number for a user supplied areacode.

    Any suggestions are welcome and I know you guys have some creative ones.

    Joel
    +---------------------------+
    Joel Pittet | Web Developer
    www.JoelPittet.com
    +---------------------------+

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    this will probably be easy to solve but we need more information first

    what are some sample values in the home_phone field?

    456-456-4567
    4564564567
    3456781234
    (456) 456-4567
    (123) 456-4567
    345-3456
    234.234.2345
    2345671234

    unless there is some format uniformity, you will have a hard time getting accurate results

    for instance, if you were searching for '%456%' you would return false positives from the above data, even if you did restrict yourself to looking at the first four characters

    by the way, that syntax you have looks okay
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It may be easiest if your database was setup with the area code and phone number in separate rows. Then you could do a

    $areacodequery = "SELECT * FROM table WHERE areacode = '456'";

    $query = mysql_query($areacodequery);
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!

  4. #4
    SitePoint Enthusiast bind727's Avatar
    Join Date
    Jan 2003
    Location
    Calgary, Alberta
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry for not giving more information there is a home_phone field. it doesn't have a unified value. I needed to get the substring but it didn't work. What I did was WHERE LEFT(home_phone, 4) LIKE '555%' which now that I look at it wont give me very nice results iether

    I will have to do some sql field formating and strip the extra whitespace and chars.......
    +---------------------------+
    Joel Pittet | Web Developer
    www.JoelPittet.com
    +---------------------------+


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
  •