[RESOLVED] Comparing strings -- query or preg_match?

mrgrammar

I want to log each useragent to my web page, but some of them need to be excluded. I have a database with an excludes table to contains records for all the various types of exclusion (IP, useragents, etc.). The web page captures the user agent of the visitor. I then need to check that user agent against the excludes table to see if it should be excluded.

// captured from browser
$ua = $_SERVER['HTTP_USER_AGENT'];
$ip = $_SERVER['REMOTE_ADDR'];

$ua_array = array();
$ip_array = array();
$ctr = 0;

$query = "SELECT * FROM table_name WHERE excludes = '".$ua."'";
$result = mysql_query($query);
while($qr = mysql_fetch_array($result)){
	$ua_array[$ctr] = $qr['ua_exclude_field'];
	$ip_array[$ctr] = $qr['ip_exclude_field'];
	$ctr++;
}

foreach($ua_array as $exc_ua){
	if($ua == $exc_ua){$exclude = 'yes';}
}
foreach($ip_array as $exc_ip){
	if($ip == $exc_ip){$exclude = 'yes';}
}

QUESTION 1: EFFICIENCY
I could just run a query against the database to check the include, but I would have to do so for each type of exclude. Therefore, my thought is to read all the types of excludes into their own array with just one pass of the database and then compare each type of exclude against their respective array. In the example above, I've only included IP and useragent. Is this the best method?

QUESTION 2: BEST METHOD
Assuming that I want to exclude based on exactly what is in the db exclude field, is it better to use a query or to use preg_match?

If it is better to use preg_match, how would I get the database field contents ($ua_array[] for example) into the correct regex pattern for preg_match?

QUESTION 3: PARTIAL STRING

One of many possible user agents is:
"Mozilla/5.0 (compatible; YandexBot/3.0; +http://yandex.com/bots)"

I can add this string in full to the DB, or I could just put in YandexBot to exclude all user agents that contained that string. In that case, I would be checking to see if any part of $ua (the user agent captured from the browser) contained the string stored in the database. Am I correct that preg_match would be best to use?

In that case, is this correct for creating the array?

while($qr = mysql_fetch_array($result)){
	$ua_array[$ctr] = "'/^".$qr['ua_exclude_field']."/'";
	$ctr++;
}

johanafm

mrgrammar;10996686 wrote:
I want to log each useragent to my web page, but some of them need to be excluded.

Why? If this is about bots, you're going about it in a very wrong / bad way.

mrgrammar;10996686 wrote:
QUESTION 1: EFFICIENCY
Is this the best method?

No. With a properly indexed database the best method is to include all relevant indata in the query. When the query is excuted you have your answer. No looping, arrays etc needed.

mrgrammar;10996686 wrote:
is it better to use a query or to use preg_match?

How does preg_match compare to a query? If you don't need regular expressions at all, why use them at all?

mrgrammar;10996686 wrote:
If it is better to use preg_match, how would I get the database field contents ($ua_array[] for example) into the correct regex pattern for preg_match?

Would you not be matching the pattern AGAINST the strings. What would putting the strings into the pattern achieve?

mrgrammar;10996686 wrote:
Am I correct that preg_match would be best to use?

No. You still do not need any kind of pattern matching and even if you did, why would you even consider preg?

mrgrammar

johanafm;10996728 wrote:
Why? If this is about bots, you're going about it in a very wrong / bad way.

I want to see all traffic to the site, bots and everyone. However, I want to distinguish between the two. Therefore, I want info about bots and unwanted visitors saved in a different excludes table instead of the table containing the log of those I want to count. Hence, I have a list of IPs and useragents in a table that are to be excluded.

Weedpacket

mrgramar wrote:
Therefore, I want info about bots and unwanted visitors saved in a different excludes table instead of the table containing the log of those I want to count.

It would probably be easier to store them in the same table; just filter out unwanted records when querying it, basing the WHERE clause on the contents (which might after all change after the records have been added) of another table.

If you're really not interested in recording certain rows at all, you could add a BEFORE INSERT trigger that checks the excludes table and cancels the insert if a match is found.