Problems updating an int column

[landysaccount]

Hello.

I have a website and would like to keep track how many times an ad has been displayed. I created a simple script but, noticed that the impression number does not match the real times an ad has being displayed. I don't know what I'm doing wrong and I'm here asking if you guys can take a look at the script and let me know what I'm doing wrong:

First here is the table's desc:

mysql> desc ad;
+----------------+----------------------+------+-----+---------+----------------+
| Field          | Type                 | Null | Key | Default | Extra          |
+----------------+----------------------+------+-----+---------+----------------+
| ad_id          | int(20) unsigned     | NO   | PRI | NULL    | auto_increment |
| ad_name        | varchar(100)         | NO   |     | NULL    |                |
| ad_img_path    | varchar(150)         | NO   |     | NULL    |                |
| ad_url         | varchar(80)          | YES  |     | NULL    |                |
| ad_width       | int(4) unsigned      | NO   |     | NULL    |                |
| ad_height      | int(4) unsigned      | NO   |     | NULL    |                |
| ad_alt         | varchar(50)          | YES  |     | NULL    |                |
| ad_exp_date    | date                 | YES  |     | NULL    |                |
| ad_active      | enum('Y','N')        | YES  |     | Y       |                |
| ad_remain_imp  | int(10)              | YES  |     | NULL    |                |
| ad_impressions | int(11)              | YES  |     | 0       |                |
| ad_clicked     | int(10) unsigned     | YES  |     | 0       |                |
| ad_date        | int(15) unsigned     | NO   |     | NULL    |                |
| ad_flash       | enum('true','false') | YES  |     | false   |                |
+----------------+----------------------+------+-----+---------+----------------+

<?php

$ads_arr = array();

$query = "select * from ad where ad_width=250 and ad_height=90 and ad_active='Y'";
$ads  = new DB($query);

while( $all = mysql_fetch_array( $ads->result ) ){
      $ads_arr[] = $all['ad_id'];      
}

   // print_r($ads_arr);
    shuffle($ads_arr);
    //print_r($ads_arr);






?>

<div id="promo_block">
    <?
        $count = 1; // limit
        foreach( $ads_arr as $id){

    if( $count > 3 ){ break; }else{ $count++; }

       $query = "select ad_id from displayed where ad_id=$id";
       $disp  = new DB($query);

       if( $disp->__CountRows__() > 0 ){
        continue;
       }

       // echo "id: $id";
       $query = "select * from ad where ad_id=$id ";
       $ad = new DB($query);
       $a = mysql_fetch_array( $ad->result );     
?>
      <div id="promo_250x90">
          <a onclick="recordClick(<?=$a['ad_id']?>);" <? if( $a['ad_id'] != 16 ){ echo 'target="_blank"'; } ?> href="<?=$a['ad_url']?>">
          <? if($a['ad_flash'] == 'true')
              {
          ?>
                <object width="<?=$a['ad_width']?>" height="<?=$a['ad_height']?>">
                  <embed src="<?=$a['ad_img_path']?>" width="<?=$a['ad_width']?>" height="<?=$a['ad_height']?>"></embed>
                </object>
          <?  } // end if flash
              else{
          ?>
                <img src="<?=$a['ad_img_path']?>" width="<?=$a['ad_width']?>" height="<?=$a['ad_height']?>" alt="<?=$a['ad_alt']?>" />
          <?
              }

          ?>

          </a></div>
<?    
      $query = "select ad_impressions from ad where ad_id=".$a['ad_id'];
      $imp   = new DB($query);

      $i = mysql_fetch_array($imp->result);

      $current_imps = 0;

      $current_imps = intval($i['ad_impressions']);
      $current_imps++;

      $query  = "update ad set ad_impressions =" . intval($current_imps) . " where ad_id=".$a['ad_id'];
      $update = new DB($query);   

      $query = "insert into displayed (ad_id) values ($id)";
      $save  = new DB($query);  

      //  this is the limiter

    }

?>

I thought I was going through the loop several times but, $current_imps gets reset back to 0 every time through...

Thanks in advanced for your help.

[dalecosp]

Why do all that? You want to increment a number in the database, increment a number in the database. For example, in MySQL:

mysql> create table foo (bar int);
Query OK, 0 rows affected (0.09 sec)

mysql> select * from foo;
Empty set (0.00 sec)

mysql> insert into foo values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;
+------+
| bar  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> update foo set bar=bar+1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from foo;
+------+
| bar  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

So, you should need only one SQL query to do the incrementing:

$query = "update ad set ad_impressions=ad_impressions+1 where ad_id=".$a['ad_id'];

🙂

[bradgrafelman]

In fact, note that by not doing it in 1 query you run the risk of losing some of those hits due to a race condition. If multiple requests come in (nearly) simultaneously, their parallel execution might allow them all to SELECT the same value, add 1 to it, then UPDATE the database with the result (which will only get increased by 1 even if 10 separate threads are doing this at the same time).

[landysaccount]

I forgot to mention in my post that I was doing:

$query = "update ad set ad_impressions=ad_impressions+1 where ad_id=".$a['ad_id']; 

but, changed it to what I posted, thinking that maybe I had to force the variable to an int....

That didn't work either.

[landysaccount]

I don't understand when I run the script or visit the site on a test machine the hit count is crazy, it starts at 0, on the first visit then is 6, the next is some other number almost double, etc....

Can anyone see a glitch on the script?

[dalecosp]

It lacks commentary 😉

Please don't take this rant as a personal attack; you're not the only guilty party. I've even written a lot of code that's not as well-documented as it should be. It was especially bad when I had less experience, and when I worked only for myself.

Seriously, though comments might help. I don't know if you plan first, or just sit down & hack, or what; since I've been doing more programming in my new job than my last one (and typically more complex issues involved), I'm back to old-school design documentation and flow-charts, transfer of logic to the script comment fields, then code, and then debug by sections. I don't get "stuck" much - well, not generally to the point where I have to come post on the forums, that is.

It certainly helps if you're going to have others try to read/interpret your code for themselves. Yes, we have to analyze code all the time; no, we don't necessarily want to come here and do it for free 😉 You'd probably save time for us, at least, by putting a tad more into it yourself.