recognize user agents

joane1

Hi all
I have a db table (varchar(200)) with some user agent string, separated with a new line (but \n is not visible in the field).

Now, how can I recognize if useragent of visitator is between those in the table?

Horizon88

Something like:

$sql = "SELECT agent_id FROM user_agents WHERE user_agent_string = '".$_SERVER['HTTP_USER_AGENT']."';";

may work, provided the user agent is an exact match.

Another, more flexible - but also more intensive - way would be to load them all (select * from user_agents) into an array, then use a foreach to loop through them until you hit a match with preg_match or a simple string comparison.

If you provide a bit more information about how you're storing the data and some examples of what exactly you're comparing against, we could help you a bit more. Always helps if you post any code you're working with.

bradgrafelman

Why not use the PHP function [man]get_browser/man ?

joane1

i explain my situation.

I have a config table with more fields: one of those is "user_agents" (varchar 400). In this field, i insert some user agents string (google, yahoo etc.): see image http://img301.imageshack.us/img301/7482/useragentsjq4.jpg

Now, in another page, i check the visitor user agent with this code:

$sql = mysql_query("SELECT user_agents FROM tab_config", $db);

$baduseragents = array();

while ($row = mysql_fetch_array($sql)) {
    $baduseragents[] = $row['user_agents'];
}
	$user_agents = isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : '';

   if (!in_array("$user_agents", $baduseragents)) {

//.......
	}

How can i compare the visitors user agent with the user agents stored in my table?

bradgrafelman

Are you simply trying to do a strict string comparison to see if the User-Agent header matches exactly one of the strings in your SQL table? If so, just do...

if(isset($_SERVER['HTTP_USER_AGENT']) {
	$query = 'SELECT 1 FROM tab_config WHERE user_agents=\''
				 . mysql_real_escape_string($_SERVER['HTTP_USER_AGENT']) . '\'';

$exec = mysql_query($query);

if(mysql_num_rows($exec)) {
	// user agent found in SQL table
}
}

joane1

uhm.... your code doesn't works bacause i have all user agents string in only one field....
I put the strings in db because the admin user can add, edit or remove the strings from a control panel.

Or explain me, please, another way to obtain this comparision.

luqman93

Why not use like to match it as such: (Note : I spaced this out a lot so it's easier to read.')

$uagent = mysql_real_escape_string($_SERVER['USER_AGENT']);

if ( isset ( $uagent ) ) {

   $query = mysql_query("SELECT * FROM `tab_config` WHERE `user_agents` LIKE ' " .$uagent. " ' ") or die ( mysql_error (' Mysql error occurred! '));

   $returned = mysql_num_rows ( $query );

   if ( $returned !== '0') {

   // User Agent Found. Do something like redirect??

   } else {

    // User Agent Not found.

   }
}

or this to kinda match it ( using % with LIKE)

$uagent = mysql_real_escape_string($_SERVER['USER_AGENT']);

if ( isset ( $uagent ) ) {

   $query = mysql_query("SELECT * FROM `tab_config` WHERE `user_agents` LIKE '%" .$uagent. "%' ") or die ( mysql_error (' Mysql error occurred! '));

   $returned = mysql_num_rows ( $query );

   if ( $returned !== '0') {

   // User Agent Found. Do something like redirect??

   } else {

    // User Agent Not found.

   }
}

joane1

now, i use this code:

$uagent = mysql_real_escape_string($_SERVER['HTTP_USER_AGENT']);
if ( isset ( $uagent ) ) {
       $query = mysql_query("SELECT user_agents FROM tab_config WHERE user_agents LIKE '%" .$uagent. "%' ") or die ( mysql_error (' Mysql error occurred! '));
       $returned = mysql_num_rows ( $query );
       if ( $returned !== '0') {
	echo "User Agent Found";
       } else {
        echo "User Agent NOT Found";
       }
}

It print ever the first condition: "User Agent Found"

The user agent string are stored in a field as this:
varchar(400)
Googlebot/2.1 (+http://www.googlebot.com/bot.html) Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) Mozilla/5.0 (compatible; Yahoo! Slurp; http://help.yahoo.com/help/us/ysearch/slurp) msnbot/1.1 (+http://search.msn.com/msnbot.htm)

Every user agent is separated by a space
I don't understand.

NogDog

Problem #1 is storing multiple values in a single column. Doing so causes that table's design to fail the 1st normal form requirement of database normalization that "Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else)."

It really would be a more robust design to have those separate user agent values in separate rows, which would consequently make it simpler and more "normal" to address them via SQL.