I've been playing for a while with this search string:

SELECT urlbanned
FROM bannedurls
WHERE urlbanned LIKE '%$linkname%'
LIMIT 0 , 30

This work's fine, it shows the url's for sex.com that are listed in the database but what i want the query todo in conjunction with php is stop at the first / so that it won't go anyfurther or submit the form - does that make sense?

I'm not completely clear on what you want, but if you only want to retreive one record, then use LIMIT 1 at the end.

If you are using this in php, be sure to use mysql_real_escape_string() to safely escape $linkname, otherwise you open yourself to injection attacks.

What i want todo is,

If someone looks up say sex.com/somedirectory/somepage.html in the database and it comes accross sex.com/ on it's own without anything after the first / it stops and says sorry sex.com is listed in our blocked url's list rather than having to block each and every page on sex.com in the database,

make more sense?

I'd recommend using parse_url (http://www.php.net/parse_url) to parse the url in question to validate and then pull the domain, and then validate against the MySQL database.

IMO, This is the easiest and most efficient method.

Hope I've helped.

You need regular expressions, just using simple LIKE statements won't return any valid results because of search limits imposed by the mySQL engine (unless you alter source as described on mysql.com).


SELECT * FROM bannedurls WHERE urlbanned REGEXP '^$linkname' LIMIT 1;


That'll return the first row, which should be enough? This technique depends on proper filtering of the $linkname value of course. Try stripping all leading http(s):// before entering them into your database. The ^ symbol symbolizes the beginning of a line.

Good luck.
Alex

Interesting codings above I might say. :)

One more point to note here, is that the search is case insensitive. So if you search for ^foo, it will pick up foo, FOO and fOo.

Unless you put it under a condition, with an if above, it could be changed by using that variable in the SQL statement. ;)

One more point to note here, is that the search is case insensitive. So if you search for ^foo, it will pick up foo, FOO and fOo.Be careful, as this is not entirely true.

The case sensitivity depends on your column's collation. If it ends with _cs, it is case sensitive, if it ends with _ci, it isn't. (ex. utf8_general_ci).

It's recommended to leave it on a case insensitive collation, and then to force sensitivity when required with the 'BINARY' keyword. Here's a good example from the mySQL manual


SELECT 'WORD' REGEXP '[[:upper:]]{4}') AS upper_match;
this will be a 1


SELECT 'WORD' REGEXP '[[:lower:]]{4}') AS lower_match;
this will be a 1 on an "*_ci" collation


SELECT BINARY 'WORD' REGEXP '[[:upper:]]{4}') AS bin_upper_match;
this will be a 1


SELECT (BINARY 'WORD' REGEXP '[[:lower:]]{4}') AS bin_lower_match;
this will be a 0 even on an "*_ci" collation

The same applies to standard expressions as I'd suggested in my last post in this thread.

Good luck.
Alex