[RESOLVED] PDO - Adds 2 to $value regardless of code

[rbrown56]

I have tried this:

    echo '<pre>'.print_r($rows, true).'</pre>';
    $last = strtotime('now');
    echo $rows['0']['count'].'<br>';
    $add_to_row_count = $rows['0']['count']+1;
    echo $add_to_row_count.'<br>';
    $stmt = $database_handler->prepare("
        UPDATE ip_blocker 
        SET 
        count = :add_to_row_count, 
        last = :last 
        WHERE 
        id = ".$rows['0']['id'].""
    );
    $stmt->bindValue(":add_to_row_count", $add_to_row_count, PDO::PARAM_INT);
    $stmt->bindValue(":last", $last, PDO::PARAM_INT);
    $stmt->execute();
    echo '<pre>'.print_r($rows, true).'</pre>'; 

And I have tried this:

    echo '<pre>'.print_r($rows, true).'</pre>';
    $last = strtotime('now');
    echo $rows['0']['count'].'<br>';
    //$add_to_row_count = $rows['0']['count'];
    //echo $add_to_row_count.'<br>';
    $stmt = $database_handler->prepare("
        UPDATE ip_blocker 
        SET 
        count = :add_to_row_count, 
        last = :last 
        WHERE 
        id = ".$rows['0']['id'].""
    );
    $stmt->bindValue(":add_to_row_count", $rows['0']['count']+1, PDO::PARAM_INT);
    $stmt->bindValue(":last", $last, PDO::PARAM_INT);
    $stmt->execute();
    echo '<pre>'.print_r($rows, true).'</pre>'; 

either way the above will add to count by 2.... 2, 4, 6, 8,etc...


And both of these does not change the count:

    echo '<pre>'.print_r($rows, true).'</pre>';
    $last = strtotime('now');
    echo $rows['0']['count'].'<br>';
    $add_to_row_count = $rows['0']['count'];
    echo $add_to_row_count.'<br>';
    $stmt = $database_handler->prepare("
        UPDATE ip_blocker 
        SET 
        count = :add_to_row_count, 
        last = :last 
        WHERE 
        id = ".$rows['0']['id'].""
    );
    $stmt->bindValue(":add_to_row_count", $add_to_row_count, PDO::PARAM_INT);
    $stmt->bindValue(":last", $last, PDO::PARAM_INT);
    $stmt->execute();
    echo '<pre>'.print_r($rows, true).'</pre>'; 

    echo '<pre>'.print_r($rows, true).'</pre>';
    $last = strtotime('now');
    echo $rows['0']['count'].'<br>';
    //$add_to_row_count = $rows['0']['count'];
    //echo $add_to_row_count.'<br>';
    $stmt = $database_handler->prepare("
        UPDATE ip_blocker 
        SET 
        count = :add_to_row_count, 
        last = :last 
        WHERE 
        id = ".$rows['0']['id'].""
    );
    $stmt->bindValue(":add_to_row_count", $rows['0']['count'], PDO::PARAM_INT);
    $stmt->bindValue(":last", $last, PDO::PARAM_INT);
    $stmt->execute();
    echo '<pre>'.print_r($rows, true).'</pre>'; 

Eyes are burning and can't figure out why...
It is probably something stupid, that I'm not seeing...

This is the only section that adds to the count.

I'm checking the db each time with phpmyadmin and it confirming what It is outputing to the screen

the table 'count' column is set for INT(20)

Thanks,
Bob

[Weedpacket]

Without knowing where all these $rows are coming from, since they are what appear to be deciding which rows get updated.

But nevertheless, did you know you can write something like UPDATE ip_blocker SET count = count + 1 WHERE....?

[rbrown56]

It is an IP blocker script I'm writing, running on my dev server so there is only 1 ip being processed at the time.
this is the statement where the rows are coming from:

    $stmt = $database_handler->prepare("
        SELECT * 
        FROM ip_blocker 
        WHERE ip = :their_ip_address 
        AND user_agent NOT LIKE '%google%' 
        AND user_agent NOT LIKE '%yahoo%' 
        AND user_agent NOT LIKE '%bing%' 
        AND user_agent NOT LIKE '%msn%' 
        AND user_agent not like '%slurp%'"
        );
        //echo $their_ip_address;
    $stmt->bindValue(':their_ip_address', $their_ip_address, PDO::PARAM_INT);
    $stmt->execute();
    $rows = $stmt->fetchALL(PDO::FETCH_ASSOC); 

[rbrown56]

Also tried your:

    $stmt = $database_handler->prepare("
        UPDATE ip_blocker 
        SET 
        count = count +1, 
        last = :last 
        WHERE 
        id = ".$rows['0']['id'].""
    ); 

Does the same thing... adds 2 instead of 1...

Without the "+1"
Does not add to the count.

I'm starting to think it is a bug...

[bradgrafelman]

Sounds more like an issue where your code is being executed twice.

Can you show us a more complete code snippet that includes the context around the SQL query execution?

[rbrown56]

still need to work on the the section after the "exit;"
So that is why it is there.

function DB_ATTRIBUTES($input) {
    $opt = array(
        /* any occurring errors wil be thrown as PDOException */
        //'PDO::ATTR_ERRMODE' => "PDO::ERRMODE_SILENT",
        PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING,
        //PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        /* an SQL command to execute when connecting */
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'",
        //PDO::ATTR_EMULATE_PREPARES => "false"
        );
    foreach ($opt as $key => $value) {
        $input->setAttribute($key, $value);
    }
}

function Get_DB_ATTRIBUTES($input) {
    $attributes = array(
    "AUTOCOMMIT", "ERRMODE", "CASE", "CLIENT_VERSION", "CONNECTION_STATUS",
    "ORACLE_NULLS", "PERSISTENT", "PREFETCH", "SERVER_INFO", "SERVER_VERSION",
    "TIMEOUT"
    );
    foreach ($attributes as $val) {
        echo "PDO::ATTR_$val: <br>";
        echo $input->getAttribute(constant("PDO::ATTR_$val")) . "<br>";
    }
}

$PDO_setting = $config['db']['driver'].':host='.$config['db']['host'].';dbname='.$config['db']['dbname'].'; '.$config['db']['username'].', '.$config['db']['password'].', ';

try{
    /* See if they are in the database */
    $database_handler = new PDO($PDO_setting);
    DB_ATTRIBUTES($database_handler);
    //Get_DB_ATTRIBUTES($database_handler);
    $stmt = $database_handler->prepare("
        SELECT * 
        FROM ip_blocker 
        WHERE ip = :their_ip_address 
        AND user_agent NOT LIKE '%google%' 
        AND user_agent NOT LIKE '%yahoo%' 
        AND user_agent NOT LIKE '%bing%' 
        AND user_agent NOT LIKE '%msn%' 
        AND user_agent not like '%slurp%'"
        );
    //echo $their_ip_address.'<br>';
    $stmt->bindValue(':their_ip_address', $their_ip_address, PDO::PARAM_INT);
    $stmt->execute();
    $rows = $stmt->fetchALL(PDO::FETCH_ASSOC);
    echo '<pre>'.print_r($rows, true).'</pre>';
    /* Check for any results. */
    if (empty($rows)) {
        /* They are not in the database, so add them. */
        $stmt = $database_handler->prepare("
            INSERT INTO ip_blocker 
            (
            ip, 
            host, 
            count, 
            visits, 
            banned, 
            first, 
            user_agent, 
            referer
            ) 
            VALUES 
            (
            :their_ip_address, 
            :their_host, 
            '1', 
            '1', 
            '0', 
            :time_now, 
            :their_user_agent, 
            :their_referer
            )
            ");
        $stmt->bindValue(":their_ip_address", $their_ip_address, PDO::PARAM_INT);
        $stmt->bindValue(":their_host", $their_host, PDO::PARAM_STR);
        $stmt->bindValue(":time_now", strtotime('now'), PDO::PARAM_INT);
        $stmt->bindValue(":their_user_agent", $their_user_agent, PDO::PARAM_STR);
        $stmt->bindValue(":their_referer", $their_referer, PDO::PARAM_STR);
        $stmt->execute();
    } else {
        /* They are in the database, so start counting their page hits and add last time page was hit. */
        echo '<pre>'.print_r($rows, true).'</pre>';
        $last = strtotime('now');
        echo $rows['0']['count'].'<br>';
        $add_to_row_count = $rows['0']['count']+1;
        echo $add_to_row_count.'<br>';
        $stmt = $database_handler->prepare("
            UPDATE ip_blocker 
            SET 
            count = :add_to_row_count, 
            last = :last 
            WHERE 
            id = ".$rows['0']['id'].""
        );
        //$stmt->bindValue(":add_to_row_count", $add_to_row_count, PDO::PARAM_INT);
        $stmt->bindValue(":last", $last, PDO::PARAM_INT);
        $stmt->execute();
        echo '<pre>'.print_r($rows, true).'</pre>';
        exit;
        /* start check to see if they need to be banned */
        $un_ban_in_seconds = 24; /* hours * min * secs  * 60 * 60*/
        $on_page_time_in_seconds = 60;
        $max_page_requests = 20;
        $bancount = 1;
        $count = $rows['0']['count'];
        $banned = $rows['0']['banned'];
        $first = $rows['0']['first'];
        $last_hit = $rows['0']['last'];
        $host = $rows['0']['host'];
        $current_time = strtotime('now');
        $referer = trim($rows['0']['referer']);
        $difference_from_first_hit = $last_hit - $first;
        //echo $difference_from_first_hit;
        /* check to see if they are banned */
        if ($rows['0']['banned'] == 1) {
            /* They are banned so check to see if time is up */
            if ($un_ban_in_seconds < $difference_from_first_hit) {
                $last = strtotime('now');
                $stmt = $database_handler->prepare("
                    UPDATE ip_blocker 
                    SET 
                    ip = :their_ip_address, 
                    host = :their_host, 
                    count = 0, 
                    visits = 0, 
                    banned = 0, 
                    first = :time_now, 
                    user_agent = :their_user_agent, 
                    referer = :their_referer, 
                    diff = 0
                    WHERE id =".$rows['0']['id'].""
                    );
                $stmt->bindValue(":their_ip_address", $their_ip_address, PDO::PARAM_INT);
                $stmt->bindValue(":their_host", $their_host, PDO::PARAM_STR);
                $stmt->bindValue(":time_now", strtotime('now'), PDO::PARAM_INT);
                $stmt->bindValue(":their_user_agent", $their_user_agent, PDO::PARAM_STR);
                $stmt->bindValue(":their_referer", $their_referer, PDO::PARAM_STR);
                $stmt->execute();
            }
        } else {
            /* They are not currently banned see if they need to be */
            if ($count >= $max_page_requests) {
                $last = strtotime('now');
                $stmt = $database_handler->prepare("
                    UPDATE ip_blocker 
                    SET banned = 1, diff = :difference_from_first_hit 
                    WHERE id =".$rows['0']['id'].""
                );
                $stmt->bindValue(":difference_from_first_hit", $difference_from_first_hit, PDO::PARAM_INT);
                $stmt->execute();
                echo "you have been banned!";
            }
        }
    }
} catch (PDOException $e) {
    echo $e->getMessage();
}


/* Close the database connection */
$database_handler = null; 

[rbrown56]

oops... been messing with it so much I forgot to un-comment this line when I put it back to what I started with when I copied it

//$stmt->bindValue(":add_to_row_count", $add_to_row_count, PDO::PARAM_INT); 

I can't edit my post...

[rbrown56]

No one has any ideas why this returns 2 instead of one?

[rbrown56]

Figured it out...

The back story first...
I have an old version of Cold Fusion Studio 4.5 because I haven't found any other editors that have the editor screen output the way I like, fonts (I like arial 12pt, code highlighting etc.. CF Studio is awesome... However since it was written in 2000 as I have moved up in OS's some of the functions don't work so it is just an editor at this point.

Problem is Macromedia got their hands on CF Studio and f'n it up and over priced it and turned it into Dreamweaver (which would always mess with the code like almost as bad a MS Frontpage) then Adobe got it hands on it and did the same thing and really overpriced it...

So anyway... enough of the history lesson... I went out looking for a new IDE and saw that Netbeans now has a php editor module. It is pretty close to CF Studio as far as the editor goes and I tweaked the code highlighting. The only downsides are no snippet library, the editor only shows one tab even through there are more than one, and the high lighting shows php vars colored inside of double quotes. It is a little slow once in a while when it is figuring out what you want to do next with the code completion.

So I installed the Debugger and stepped through the program and it worked as it was supposed to....

So scratching my head... I decided to switch browsers from Chrome and use Firefox and it works as it is supposed to. So for some reason Chrome is refreshing the page 2 times. And when I added the next section of code chrome refreshed 3 times added 3 to the count.

So I'm not sure what is going on with chrome but in the task manager when you start up chrome I get 3 exe's running and they (google) says this is for the plugins or monitoring your first born child or whatever...