SQL Query: Incrementing by two instead of 1

Question

Hello all,

I am trying to update a field in a table by increasing its integer value by 1. Here is what I am using:

function updateViews($id){

$sql = "UPDATE tweets SET tweet_views = tweet_views + 1 WHERE tweet_key = '$id'";

$result = mysql_query($sql) or die("DB Error : ". mysql_error());

return $result;

}

However, I find its incrementing by 2 each time rather than 1? What am I doing wrong?

Thanks

Update

From the Answers the SQL is correct. Do you think this may be effected by the rewrite engine??? I ask because I am 100% sure this doesn't run twice or that I don't make the call since there are two scripts. One that calls the function and one that holds the function! This is confusing.

Update 2

Using the debug function. I get this output:

array(4) {
  ["file"]=>
  string(35) "/home/magic/public_html/dbUpdate.php"
  ["line"]=>
  int(16)
  ["function"]=>
  string(15) "myDebugFunction"
  ["args"]=>
  array(0) {
  }
}

array(4) {
  ["file"]=>
  string(31) "/home/magic/public_html/view.php"
  ["line"]=>
  int(10)
  ["function"]=>
  string(11) "updateViews"
  ["args"]=>
  array(1) {
    [0]=>
    &string(5) "7jjdd"
  }
}

It looks as if the script is being called once but it is still getting updated twice??? HELP! :(

ALso from the Log file, it looks as if the scripts are being called three times??

13:16:28 id:4a6c9d7cf38016.29304000
  _SERVER[REQUEST_URI]=/lucic
  _SERVER[REDIRECT_URL]=/lucic
  /home/magic/public_html/dbUpdate.php@16 :myDebugFunction
  /home/magic/public_html/view.php@10 :updateViews
13:16:30 id:4a6c9d7eaf93e3.88114161
  _SERVER[REQUEST_URI]=/lucic
  _SERVER[REDIRECT_URL]=/lucic
  /home/magic/public_html/dbUpdate.php@16 :myDebugFunction
  /home/magic/public_html/view.php@10 :updateViews
13:16:31 id:4a6c9d7f846557.12618673
  _SERVER[REQUEST_URI]=/lucic
  _SERVER[REDIRECT_URL]=/lucic
  /home/magic/public_html/dbUpdate.php@16 :myDebugFunction
  /home/magic/public_html/view.php@10 :updateViews

UPDATE 3

Here is the contents of my htaccess file which may be causing a problem.

# REWRITE DEFAULTS
RewriteEngine On
RewriteBase /

RewriteCond %{HTTP_HOST} ^www\.mysite\.com$ [NC]
RewriteRule ^(.*)$ http://mysite.com/$1 [R=301,L]

# /view.php?t=h5k6 externally to /h5k6
RewriteCond %{THE_REQUEST} ^GET\ /view\.php
RewriteCond %{QUERY_STRING} ^([^&]*&)*t=([^&]+)&?.*$
RewriteRule ^view\.php$ /%2? [L,R=301]

# /h5k6 internally to /view.php?t=h5k6
RewriteRule ^([0-9a-z]+)$ view.php?t=$1 [L]

Answer 1

You are running the query more than once by mistake. :)

(ok, thats just a guess, but I'd suggest some logging to make sure)

Answer 2

The SQL is correct. I'd trace the PHP and make sure that there isn't a duplicate call to the function that runs the SQL.

Answer 3

You can test whether your function is called twice or your script with some debug logging.

function myDebugFunction() {
  static $iid = null;

  if ( is_null($iid) ) {
    $iid = uniqid('', true);
  }

  $log = date('H:i:s').' id:'.$iid."\n".
    "  _SERVER[REQUEST_URI]=". @$_SERVER['REQUEST_URI']."\n" .
    "  _SERVER[REDIRECT_URL]=". @$_SERVER['REDIRECT_URL'];
  foreach(debug_backtrace() as $bt) {
    echo '<pre>'; var_dump($bt); echo '</pre>';
    $log .= "\n  ".$bt['file'].'@'.$bt['line'].' '.@$bt['class'].':'.$bt['function'];
  }
  $log .= "\n";
  error_log($log, 3, 'mydebug.log');
}

function updateViews($id) {
  myDebugFunction();
  $sql = "
    UPDATE
      tweets
    SET
      tweet_views = tweet_views + 1
    WHERE
      tweet_key = '".mysql_real_escape_string($id) ."'
  ";
  $result = mysql_query($sql) or die("DB Error : ". mysql_error());
  return $result;
}

If you find the same id twice in mydebug.log the function has been called twice (within the same php instance). Otherwise your script has been invoked twice.

Answer 4

There's no trigger involved, is there?

Answer 5

Check the encoding of the file. If the browser discovers non-ascii letters in the page, then it might reload the page with another encoding. Non-ascii letters could be æøå, Russion, Chinese or Hebrew letters. Even if you have a meta tag that specifies the encoding, the browser will still reload the page and start processing it again.

To fix this problem open your file in an advanced browser (notepad++ for example) and specify the encoding. In Notepad++ this is in Format>Encode in UTF-8. If you use the Format>Convert to UTF-8, some strange bytes might appear at the beginning of the document, so it is safer to use the Encode method.

Answer 6

Do you use Smarty?

--edit-- I asked this because Smarty has a bug, when you have an img tag without a src defined in the template, the base index.php will be included twice.

The same ca happen if you have an img (you could have for ex ) or an iframe in the same page, as the base script might get displayed twice(or more) in just one refresh of the page.

Answer 7

Is this page an error handling page?

The fact that the REDIRECT_URL is the same as the REQUEST_URI suggests so since REDIRECT_URL seems to be a variable set by apache when handling ErrorDocuments or similar.

Given that the file does indeed seem to be being run more than once some redirect looping may be occuring - what's in your .htaccess file?

EDIT: there's way more info here: http://onlamp.com/pub/a/onlamp/2003/02/13/davidsklar.html?page=last

Answer 8

There are some current browsers (I think Chrome is one of them) that incorrectly issues a GET after a POST to a form, which might account for the problem you're seeing.

Could you log the requests to your php application as well and see if you're hit twice from the browser?

Answer 9

You may want to include the HTML of the resulting page here. Its possible a script, link (css) or img tag are not formatted properly, and are getting into the rewrite by just being "", "#" or "?" since those will 'resolve' to the same url. If you're not absolute-pathing all of the urls (images/header.gif instead of /images/header.gif, etc) some of those may be falling through, especially since to a browser those short urls look like directories. Try doing the request in curl (without mirroring, just a simple GET) and see if it happens there.

Also, turn on HTTP access and rewrite logging and see whats happening at that level (note, you have to put rewritelog directives in the compiled config, it wont work in a .htaccess). Failing that, watch the request in something like tamperdata, or even better, wireshark, to see the actual requests being made.

Other things to think about: MultiViews On in your apache conf could be trying to add a suffix extension and it could be getting caught oddly by your rewrite (not sure how, but who knows) - and your rewrite log will show that. mod_dir could be trying to add a trailing slash (since those short urls look sort of like directories), though, you'd probably see that.

Answer 10

just a guess, but do you have a virus scanner installed? Sometimes they prefetch a page - thus resulting in two hits. To check for that look at your access.log .

Answer 11

Just randomly: does your page have any auto-refresh/reload features, to get the latest comments, prices, updates etc? That might result in a second loading of the page, which might do what you're seeing.

Also, do you use any AJAX?

Answer 12

i would create a sproc/trigger to handle this on the database side.

call the sproc from your page on a specific event (like a button click).

your issue could be from unexpected browser refresh or it could also be from scope error on database side.

redesigning your code as a stored procedure should either fix your problem or help you figure out where the issue is arising.

Answer 13

Do you use header("location: xxxxx") redirects anywhere on your page (or included pages)? If you use them without calling exit() immediately after, the script will continue to execute.

For example, if you have a login check on an included page that sets some $_SESSION variable, then redirects you to the same page using a header() redirect, it's possible that the function exit() was not called afterward, which means the rest of the script will still get executed, including your page with the updateViews() function. However, the user won't see this because on the client side it will redirect before any data is output to the browser.

Can we see the whole script to verify?