Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Checking if a String is Contained in Another String

6:46 pm on May 1, 2011 (gmt 0)

New User

5+ Year Member

joined:Apr 24, 2010
votes: 0

I know there is a way to check if a string contains another string using the 'REGEXP' command in SQL; however, is there a way to check if a string is contained in another string? I'm trying to build a sessions system in PHP but I want to perform different actions for bots since they don't accept cookies. To do this, I want to check a portion of the browser agent ($_SERVER['HTTP_USER_AGENT']). Unfortunately it isn't always the same so I'd rather put the regex in the database instead of PHP.

In short, I'm looking for the reverse solution to this:

SELECT bot_user_agent FROM ' . BOTS_TABLE . ' WHERE bot_user_agent REGEXP "/' . $_SERVER['HTTP_USER_AGENT'] . '/"

Problem with the above query is it checks for the entire user agent within the bot_user_agent column which isn't what I want to achieve. I want to check if the value in the bot_user_agent column is contained in the $_SERVER['HTTP_USER_AGENT'] variable; or example: the value in bot_user_agent column could be 'Googlebot' and it checks for 'Googlebot' in $_SERVER['HTTP_USER_AGENT']. This can be achieved in PHP using the preg_replace() function but only if the table contains one value (otherwise it picks up the first/last value, depending on the loop). I'd rather do it in SQL.

Hope this makes sense and thanks for your help in advance. :)
9:06 am on May 4, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Apr 30, 2007
votes: 0

You will have to know some sort of separator for the user agent string. For example if space is a separator you could create an array of the UA elements

$ua = preg_replace('/\s\s+/', ' ', trim($ua));
$ua_array = explode(' ', $ua)

So now you have the parts of the UA in an array and you could run the regexp or use the in or like operators,

"select ...... where bot_user_agent in (" . implode(',', $ua_array) . ")"

You could add another pass on the ua array to filter out elements that are unlikely to represent a user agent (elements with less than 10 chars etc).