Finishing Query to look for a partial string

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mrgrammar
Forum Newbie
Posts: 3
Joined: Wed Jul 06, 2011 7:07 am

Finishing Query to look for a partial string

Post by mrgrammar »

I have set up a script to log all traffic to my site, bots and everyone. However, I want to distinguish between that traffic I want and that I do not want. Therefore, I want info about bots and unwanted visitors saved in a different exclogs table instead of the table containing the log of those I want to count. Hence, I have a list of IPs and useragents to be excluded stored in a 'exclusions' table.

The Yandex bot's useragent looks something like this:
Mozilla/5.0 (compatible; YandexBot/3.0)

The identifying string stored in the exclusions table is:
YandexBot

I need to fashion the query that checks the full useragent to see if it contains the phrase stored in the database.

Code: Select all

// user agent captured from browser
$useragent = "Mozilla/5.0 (compatible; YandexBot/3.0)";

// query to check to see if $useragent has the string 'YandexBot' in it
$query = "SELECT exc_ua FROM exclusions WHERE exc_ua [what goes next?]";
I'm not sure what needs to go in place of [what goes next?] above since what is stored in the table is just a portion of $useragent.

How should this query be completed?
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: Finishing Query to look for a partial string

Post by Eric! »

Try

Code: Select all

// query to check to see if $useragent has the string 'YandexBot' in it
$query = "SELECT exc_ua FROM exclusions WHERE exc_ua LIKE '%YandexBot%'";
mrgrammar
Forum Newbie
Posts: 3
Joined: Wed Jul 06, 2011 7:07 am

Re: Finishing Query to look for a partial string

Post by mrgrammar »

I considered using LIKE but then I found instr() and it does exactly what I need. Thanks for the input.
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: Finishing Query to look for a partial string

Post by Eric! »

They are the same thing actually however LIKE tends to be a lot faster depending on how you're doing the search and how the table is structured.
Post Reply