SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
Thread: Return only the main domain
-
Nov 17, 2008, 08:17 #1
- 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 09:01.
-
Nov 17, 2008, 08:36 #2
- 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
-
Nov 17, 2008, 09:02 #3
- Join Date
- Oct 2008
- Posts
- 32
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Nov 17, 2008, 09:14 #4
- 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:
-
Nov 17, 2008, 09:20 #5
- 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, '/', 3 ) AS URL,
COUNT(SUBSTRING_INDEX( URL, '/', 3 )) AS URLCount
FROM THE_TABLE GROUP BY URL ORDER BY URLCount DESC
Bookmarks