SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL query help required

    Hi Guys,

    I have some domains stored in a database and I have created a search to return matching domains based on what the user types.

    For example if the user types "h" all domains containing a "h" should be returned.

    hotgolf.com
    hhh.com
    sphere.co.uk

    The problem comes when a user searches for an "o" for example as "o" is contained in the extension (.com and .co.uk).

    I am guessing that I am going to need some kind of regex to achieve what I am trying to do. Any help would be greatly appreciated!

    Here is the current MySQL query I am using:

    PHP Code:
    domain like '%".mysql_real_escape_string($_GET['keywords'])."%' 

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    simplest solution: don't do the analysis when retrieving the data, do it when storing the data -- split the domain (e.g. example.com) into two columns, the domain (e.g. example) and the tld (e.g. com)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    simplest solution: don't do the analysis when retrieving the data, do it when storing the data -- split the domain (e.g. example.com) into two columns, the domain (e.g. example) and the tld (e.g. com)
    Hmmm yes I thought of doing it this way, but im integrating with an existing system and this will simply be too much work.

    Is there a way using regex?

  4. #4
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you could add two cols to the table and write a short query script to take the domain name, and split it and insert the results in the two new cols. Personally I would loose the ' . ' altogether and program for that on the view side. This would also allow you to do future searches if the visitor only wanted to look at .com names. You would then have the ability to get all the .com names from the table.

    url | domain | extension

    Just a suggestion.

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A couple of questions

    1. Is the part of the domain name you wish to match on effectively up to the first dot. That is hotgolf.com not www.hotgolf.com or www3.hot.golf.com.

    2. When you have two or more characters for example "ho", do you want them as consecutive characters in the part of the domain name being searched.

  6. #6
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PhilipToop View Post
    A couple of questions

    1. Is the part of the domain name you wish to match on effectively up to the first dot. That is hotgolf.com not www.hotgolf.com or www3.hot.golf.com.

    2. When you have two or more characters for example "ho", do you want them as consecutive characters in the part of the domain name being searched.
    1. The domains are always stored like hotgolf.com (so it will only be the first dot).

    2. Yes when I have "ho" being searched it should return hotgolf.com.

    Hope this makes sense.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Zaggs View Post
    1. The domains are always stored like hotgolf.com (so it will only be the first dot).
    okay, now you're in business!!

    Code:
    WHERE SUBSTRING_INDEX(domain,'.',1) LIKE ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    okay, now you're in business!!

    Code:
    WHERE SUBSTRING_INDEX(domain,'.',1) LIKE ...
    will that work for .co.uk domains? Or does that make it more complicated? :-)

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Zaggs View Post
    will that work for .co.uk domains?
    dude...

    what happened when you tested it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you just need to do this "select * from table where column LIKE '%$var' "

  11. #11
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    dude...

    what happened when you tested it?

    It returned ".com" domains but no ".co.uk"

  12. #12
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I mean "SElect * from table where column LIKE '$var%'";

  13. #13
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dacoda View Post
    I mean "SElect * from table where column LIKE '$var%'";
    That won't work. Thanks anyway

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Zaggs View Post
    It returned ".com" domains but no ".co.uk"
    then ur doing it wrong
    Code:
    CREATE TABLE zaggs
    ( domain VARCHAR(99)
    );
    INSERT INTO zaggs VALUES
     ( 'hotgolf.com' )
    ,( 'example.com' )
    ,( 'hotgolf.co.uk' )
    ,( 'puke.com' )
    ;
    
    SELECT * FROM zaggs 
    WHERE SUBSTRING_INDEX(domain,'.',1) LIKE '%ho%'
    ;
    domain
    hotgolf.com
    hotgolf.co.uk
    
    SELECT * FROM zaggs 
    WHERE SUBSTRING_INDEX(domain,'.',1) LIKE '%uk%'
    ;
    domain
    puke.com
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •