MySQL Question

[profilesite]

i've got a table full of websites and i want to group them by the domain name, eg.. some are listed as www.domain.com and some as www.domain.com/blah. I want to group them to show the most popular domain, any ideas?

[php~pro]

Add either a user rating feature or just add a field for a count figure next to the url field. Write a small function to update the relevant count by 1 each time the link is clicked. To show the most popular results query the db for results with the new field count desc.

[profilesite]

hi, thanks for replying. that is the system i have in place already, what i am looking to do is find the most popular domain, rather than most popular individual link. I imagine what i need is a group by with some string function to strip the domain name out of the URL field, so stats for www.domain.com/link2 and www.domain.com/link2 would be grouped. hope that makes sense.

[godzilla]

I haven't found a way to strip strings in sql... unfortunately. if any one does please post it here!!

[rivux]

I've done this two different ways, one is to have a script that runs on a nightly basis and summarizes the tracking data which allows me to order by domain. This is all done in php and requires and extra table to hold the summarized data.

To do it on the fly so to speak, you can try using a rather ugly and brutal query I wrote.

select count(referrer) as refcount, referrer, Left(referrer, LOCATE('/',referrer, LOCATE('/',referrer, LOCATE('/',referrer, LOCATE('/',referrer,0)+1)+1)+1)) as domainref from tracksection group by Left(referrer, LOCATE('/',referrer, LOCATE('/',referrer, LOCATE('/',referrer, LOCATE('/',referrer,0)+1)+1)+1)) having (referrer Not Like '%mydomain.com%' AND referrer != '') order by refcount desc

If you replace 'referrer' with the field that holds your domain info and 'tracksection' with your table name, then the query should work. I know its rather ugly looking but it gets the job done in a pinch. lol I also have the mydomain.com in there in case your domains are listed (if you track internal pages as well) and you wish to weed them out. If you don't need this you can just take out the entire 'having ...' section.

[Amitkrathi]

Clean urls with php parse_url() function & then store in temporary table & then retrieve records from temporary table with group by clause to get most popular domains.