REGEX Pattern

Schweppesale · #1 (**permalink**) 12-30-09, 06:43

Hi, I've never done this(REGEX) using MYSQL before so bare with me

I have the following simple query.

Code:

 $query = "UPDATE #__jt_banners SET 
                impressions = impressions + 1
                WHERE id = '$id'";

It tallies each visiting user as an impression, however i need to first compare his/her "user agent" against a database of known search engine bots, etc.

In addition, I have a separate table which stores C class IP Addresses in the following format "72.14.199"

Returning the IP address and User agent is simple enough through PHP.

How can I then rewrite the query so that it first checks to make sure that these values don't match any within our database.

structure:
jos_jt_userAgents:
int "id"
text "user_agent"
text "description"
text "link"

jos_jt_ip_addresses
int "id"
var_char(45) "search_engine"
var_char(45) "ip_address"
text "description"

jos_jt_banners
int "impressions"
bllahblahablahlah

Schweppesale · #2 (**permalink**) 12-30-09, 18:42

ok, i tried the following. It doesn't seem to be incrementing the impressions column at all though. Am I at least on the right track?

Code:

 $user_agent = $_SERVER['HTTP_USER_AGENT'];

/*
        $query = "UPDATE #__jt_banners SET 
                impressions = impressions + 1
                WHERE id = '$id'";*/
        $query = "SELECT #__jt_banners.impressions
                         , #__jt_banners.id
                         , #__jt_userAgents.id AS uid
                         , #__jt_userAgents.user_agent
                    FROM #__jt_banners, #__jt_userAgents
                     UPDATE #__jt_banners SET
                        impressions = impressions + 1
                            WHERE (id = '$id' AND (user_agent LIKE '$user_agent' = NULL))";

Schweppesale · #3 (**permalink**) 12-30-09, 19:02

this one increments the impression column even after I add my browser's user agent to the table

Code:

 $query = "UPDATE #__jt_banners, #__jt_userAgents SET
                        #__jt_banners.impressions = #__jt_banners.impressions + 1
                            WHERE (#__jt_banners.id = '$id' AND
                    (#__jt_userAgents.user_agent NOT LIKE '$user_agent'))";

Internet Services

Web Development

Digital Marketing

Consumer Tech