homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Checking if a String is Contained in Another String

Msg#: 4306600 posted 6:46 pm on May 1, 2011 (gmt 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. :)



WebmasterWorld Senior Member 5+ Year Member

Msg#: 4306600 posted 9:06 am on May 4, 2011 (gmt 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).

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved