Is there a "within" function in MySQL?

[pdstein]

I'm writing some PHP/MySQL code to do affiliate tracking based on the referral URL. Each affilate has already supplied the baseURL of their website. An affilate that has their own domain name would have a baseURL of http://www.MyDomain.com but an affiliate that's on a free server might have a baseURL of http://www.freeserver.com/mydir/

So, what I want to do is when somebody clicks through our homepage I take the referring URL (the URL where they came from) and compare it to the affilates' baseURLs to see which affiliate (if any) the visitor came from. Something like...

SELECT * from affiliate where baseURL WITHIN 'http://www.freeserver.com/mydir/home.html'

would match a row where baseURL is http://www.freeserver.com/mydir/

SELECT * from affiliate where baseURL WITHIN 'http://www.freeserver.com/mydir/subdir/page6.html'

would also match a row where baseURL is http://www.freeserver.com/mydir/

This way an affiliate can link to us from any page in their site and we know the referral came from them.

It's kind of the opposite of the LIKE function. With the LIKE function you start with a sub-string and match it to any string that has the sub-string within it. With the WITHIN function I'm looking for I am starting with the larger string and want to match it to any string that is a sub-string of it.

Is there a function that works like a "within" function?

- Paul

[dr.p]

If you just want to match `baseUrl` values to URL's minus the html page (if present) then just strip the file name from the URL and use something like:

Select * From xtable Where xurl REGEXP "^http://yoururl.com/dir/"

Look at "SELECT" function in MySQL manual for details on REGEXP.

Regardless of what URL you're using:
~http://yoururl.com/dir/page1.html
~http://yoururl.com/dir/page2.html
~http://yoururl.com/dir/page100.html

You should use the document root (http://yoururl.com/dir/) instead of the documents themselves.

[godzilla]

In general it is suggested to reduce the search criteria rather then increasing the key value. That is, as dr.p mentions I would reduce the referrer adress to meet my needs.

But you may just as test try something like

WHERE baseURL+'%' LIKE 'http://www.freeserver.com/mydir/home.html'

'+' is usually used in sql to concatenate strings and may work for this case also...
Try and tell me.