SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Return only the main domain

    I have the following table (fields: ID and URL) with the following data:

    ID ---------- URL
    1 ----------- http://sitepoint.com
    2 ----------- http://www.sitepoint.com
    3 ----------- http://www.sitepoint.com/forum
    4 ----------- http://sitepoint.com/article_here
    5 ----------- http://google.com
    6 ----------- http://google.com/search-results
    7 ----------- http://site.com
    8 ----------- http://site.com/pages/category/article
    9 ----------- http://subdomain.site.com

    How do I query so that it will only return the main domain? What I want in the results are only these:
    • sitepoint.com
    • google.com
    • site.com
    Last edited by GetWebHost; Nov 17, 2008 at 08:01.

  2. #2
    SitePoint Zealot smadeira's Avatar
    Join Date
    Oct 2003
    Location
    Pennsylvania
    Posts
    192
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try

    Code SQL:
    SELECT substring_index(URL, '/',1) AS URL FROM TABLE

    If it doesn't like the fields that have no '/' in it then you may need to do a case statement and check if the '/' exists and then get the substring. If the '/' doesn't exist then you just return the field.
    Scott

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by smadeira View Post
    try

    Code SQL:
    SELECT substring_index(URL, '/',1) AS URL FROM TABLE

    If it doesn't like the fields that have no '/' in it then you may need to do a case statement and check if the '/' exists and then get the substring. If the '/' doesn't exist then you just return the field.
    Thanks for the help but I it returns all the entries, from the example above it return 9 results. What I want is that it should only return three results as there are only three unique domains

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you have to use SUBSTRING_INDEX(url,'/',3) because of the slashes after http:
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I already got it. Thanks for the response.
    PHP Code:
    SELECT SUBSTRING_INDEX(URL,  '/') AS URL,
    COUNT(SUBSTRING_INDEXURL,  '/')) AS URLCount
    FROM THE_TABLE GROUP BY URL ORDER BY URLCount DESC 


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
  •