Solved

MySQL returning incorrect value

Posted on 2016-07-15
60
54 Views
Last Modified: 2016-07-22
Hi Guys,
Regardless of the actual value in the db column, the $Get_TheCOUNT query always adds 1.
So if the value in the db column 'TheCOUNT' = 0, the '$ExistingTheCOUNT' shows a value of 1.
And of course the UPDATE column's value becomes 2 instead of 1.
What the heck is going on?
Thanks Sas

    	//Get counts
	$Get_TheCOUNT="SELECT TheCOUNT FROM TheTABLE WHERE id='$id'";
	$TheCOUNT_result=mysql_query($Get_TheCOUNT)or die(mysql_error());
	while($row = mysql_fetch_array($TheCOUNT_result)){ 
	$ExistingTheCOUNT=$row['TheCOUNT']; 
} $AddONE='1';
	$UpDate_TheCOUNT=("$ExistingTheCOUNT" + "$AddONE");
	$query="UPDATE TheTABLE SET TheCOUNT='$UpDate_TheCOUNT'  WHERE id='$id'"; 
	mysql_query($query) or die(mysql_error());  

Open in new window

0
Comment
Question by:sasnaktiv
  • 26
  • 18
  • 14
  • +1
60 Comments
 
LVL 21

Accepted Solution

by:
Kim Walker earned 190 total points
ID: 41713791
Are you sure there is only one row in the table with that id and that the last row with that id has a value of 0 for TheCOUNT column? If there is only one row, there is no need for the while loop. But if there are multiple rows with that id, the value of $ExistingTheCOUNT after the while loop will the be the value from the last row. The while loop will overwrite the value of $ExistingTheCOUNT for each iteration.

With that being said, there is no need to do the math in PHP. You can do all this in one SQL statement.
$query="UPDATE `TheTABLE` SET `TheCOUNT`=`TheCOUNT`+1 WHERE `id`='$id'"; 
mysql_query($query) or die(mysql_error());

Open in new window


And while we're on the subject, the mysql connection functions of PHP have been updated to the OOP structured mysqli routines. It is highly recommended that you update your code to use the new functions.
$mysqli = new mysqli($hostname,$username,$password,$dbname);
$mysqli->set_charset('utf8');
$query="UPDATE `TheTABLE` SET `TheCOUNT`=`TheCOUNT`+1 WHERE `id`='$id'"; 
$mysqli->query($query);
if ($mysqli->affected_rows > 0) { echo $mysqli->affected_rows." records updated.\n"; }
if ($mysqli->error) { die($mysqli->error); }

Open in new window

1
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 185 total points
ID: 41713807
The code reads current counte values, adds one and stores that. Next time you read the incremented value. So what's wrong?

How do you come to your conclusion $ExistingTheCOUNT is already +1 in relation to the stored count? When reading next time? Of course. The whole code increments a counter.

I don't need to repeat Kims good advice, but if you want to see what you read, ouput it or log it somewhere...
    	//Get counts
	$Get_TheCOUNT="SELECT TheCOUNT FROM TheTABLE WHERE id='$id'";
	$TheCOUNT_result=mysql_query($Get_TheCOUNT)or die(mysql_error());
	while($row = mysql_fetch_array($TheCOUNT_result))
        { 
	 $ExistingTheCOUNT=$row['TheCOUNT']; 
        } 
	$UpDate_TheCOUNT=$ExistingTheCOUNT + 1;
	$query="UPDATE TheTABLE SET TheCOUNT=$UpDate_TheCOUNT  WHERE id='$id'"; 
	mysql_query($query) or die(mysql_error()); 
        echo "The count was $ExistingTheCOUNT and was updated to $UpDate_TheCOUNT";
 

Open in new window


Kim especially is right this code may be influenced by many table rows having the same ID, but in the end the fetch while loop gets the last one and the update sets all counter rows to the same value. You count up in this counter, that's what counters are for, so what is your problem?

Is TheCount column not an numeric type perhaps? Why do you do string adding??? I see a tendency of code putting everything in quotes. Numbers are numbers, you don't put quotes around them and you also store them in numeric fields, eg integer, bigint, float, such types.

Bye, Olaf.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41714649
Thanks to both Kim & Olaf for your much appreciated attention to this oddity.
Before I go any further I want to let you know that there is only one row with the id. It is truly unique. So that removes one goose to chase.
Also with each test I change the value of TheCOUNT to '0' before executing the PHP script.

Kim, I tried both of your recommendations.
The first one does indeed function properly, however it only updates the column once. Regardless of how many times I call on the PHP script the value in TheCOUNT remains at '1'.

Your second suggestion using mysqli does indeed UPDATE TheCOUNT and it will do so each time the PHP script is triggered.
BUT it increases the value by '2' not '1' !!!

It defies logic.
Sas
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41714651
Of course the count remains at 1, if you always reset it to 0.

If Kim's code increments by 2, then that can only be the case, if you call it twice, or additional to your own code.

I don't think there is any mystery going on here.

You could post your full code, including reset and surrounding test code, then we tell you what it's doing and what end value to expect.

Bye, Olaf.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41714669
I guess I didn't make myself clear.
I'll manually set the TheCOUNT to '0' in the db.
Then execute Kim's first suggestion which changes TheCOUNT from '0' to '1' as it should.
Then (without resetting TheCOUNT to '0') execute Kim's first suggestion again. The result is '1' not '2'.
If I execute the script yet again TheCOUNT remains at '1'. It never increases by '1'.

Now I've done some additional testing in which I  set the TheCOUNT to '5' in the db.
Then execute Kim's first suggestion. It sets the value of TheCOUNT to '1' !!!

So Kim's first suggestion seems to always set the value of TheCOUNT to '1'.
And Kim's second suggestion always adds by increments of '2'.

I'm using Kim's scripts exactly as he's written them.

Any idea of what's going on here?
Sas
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41714680
Did you really copy as Kim posted, notice he used backticks for column names, not single quotes.

Please copy & paste.

Bye, Olaf.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41714695
You're right about the backticks. But once I correct that, it still adds by increments of '2'.
Notice that my Table Name and Column Names are different from what I submitted.

$query="UPDATE MemberReports SET OptOutCount=`OptOutCount`+1  WHERE `id`='$id' "; 
mysql_query($query) or die(mysql_error());

Open in new window


$mysqli = new mysqli($hostname,$username,$password,$dbname);
$mysqli->set_charset('utf8');
$query="UPDATE `MemberReports` SET `OptOutCount`=`OptOutCount`+1 WHERE `id`='$id' ";  
$mysqli->query($query);
if ($mysqli->affected_rows > 0) { echo $mysqli->affected_rows." records updated.\n"; }
if ($mysqli->error) { die($mysqli->error); }

Open in new window

0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41714706
Are there any triggers in the database? The code clearly adds exactly 1, there is no mistake to be found in it. So if you don't have something like an update trigger what else but a double call of the code would cause increments by 2?

Please echo/log the calls and you'd see.

Bye,  Olaf.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41714714
Olaf, how can I echo/log the calls?
What's the accurate way to write that echo/log?
Sas
0
 
LVL 21

Expert Comment

by:Kim Walker
ID: 41714717
How are you resetting it back to 0 manually? Are you using phpMyAdmin or some other db control panel? If so, look at the table structure for TheTable and tell me what is listed under the Type column for TheCount. It should be one of several numeric data types in order for the addition to work properly.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41714736
TheCount column type is 'init(11)'
And yes, when I set the value to '0' I do it manually.
Thanks for the help Kim.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41714737
"echo" simply is a PHP command, I even already used it in my modification of your code. How much PHP are you doing, are you beginning with PHP?
You could simply echo "increment"; and that'll show up somewhere in the result page.

When your php output does not go into a browser page or the page is complicated, it's much better to log debug output into a file, any file, text file. Simplest way is using error_log(): http://php.net/manual/en/function.error-log.php

Whenever the code has run you'll see either one time or two times "increment" on screen or in the servers error log. If you don't know where your server stores logs ask the hoster, or simpler, ask php, via phpinfo();

Bye, Olaf.

PS:
And yes, when I set the value to '0' I do it manually.
Hopefully you save the value 0 in phpMyAdmin before starting your next experiment.
0
 
LVL 21

Expert Comment

by:Kim Walker
ID: 41714781
I'm guessing that's a typo since I'm not aware of a date type init. It should be int.

Are you sure there aren't any triggers set up to automatically increment that column on an update? If you're using phpMyAdmin, there's a tab at the far right for triggers.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41714785
Yes Kim, that was a typo. Sorry about that.
I am in fact using phpMyAdmin and there are no triggers.
Sas
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41714804
How about this for testing?

$mysqli = new mysqli($hostname,$username,$password,$dbname);
$mysqli->set_charset('utf8');
$query="UPDATE `MemberReports` SET `OptOutCount`= 0 WHERE `id`= $id"; 
$mysqli->query($query);
if ($mysqli->affected_rows > 0) { echo $mysqli->affected_rows." records reset.\n"; }
if ($mysqli->error) { die($mysqli->error); }
$query="UPDATE `MemberReports` SET `OptOutCount`=`OptOutCount`+1 WHERE `id`= $id"; 
$mysqli->query($query);
if ($mysqli->affected_rows > 0) { echo $mysqli->affected_rows." records updated.\n"; }
if ($mysqli->error) { die($mysqli->error); }

Open in new window

0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41714815
Well Olaf,
That sets the value of TheCOUNT to '0'
And then sets it to '1'

It will do that each time the php script is called to perform, so it will never count subsequent hits to the php script.
Is that what you intended?
Sas
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41714816
I said 'for testing'.

It'll prove, that Kims code only increments by 1.
Also it's definitely setting the count to 0 first.

Bye, Olaf.

PS: If you dislike that, make another experiment. For example don't reset to 0 and instead add 2. If this causes an increment of 4, you're nearer to knowing the code must be called twice. Repeatedly doing the same thing again and again will only lead to the same unbelievable result and give no new knowledge...
0
 
LVL 21

Expert Comment

by:Kim Walker
ID: 41714834
I tend to agree with Olaf that something is causing this query to repeat. Are you targeting a single id with this script or do you have a list of ids that you're iterating through? If you have a list, how is the list being compiled? is it possible there are duplicates in the list?

Let's try another modification to see step-by-step feedback.
$mysqli = new mysqli($hostname,$username,$password,$dbname);
$mysqli->set_charset('utf8');
$query="UPDATE `MemberReports` SET `OptOutCount`=`OptOutCount`+1 WHERE `id`='$id' ";  
$mysqli->query($query);
if ($mysqli->affected_rows > 0) {
	$res = $mysqli->query("SELECT `OptOutCount` FROM `MemberReports` WHERE `id`='$id'");
	while ($row = $res->fetch_array() ) {
		$result = $row[0];
		echo "<p><i>{$query}</i> resulted in an OptOutCount of {$row[0]} and {$mysqli->affected_rows} record(s) updated.</p>\n";
	}
}
if ($mysqli->error) { die($mysqli->error); }

Open in new window

0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41714840
Yes I understand testing that's all we've been doing.
And you are right it changes the value of OptOutCount to '1'
And as you suggested if I experiment with `OptOutCount`+2 it changes the value of OptOutCount to '2'. As it should.

Next I deleted the following lines from your test so as not to reset OptOutCount to '0'
$query="UPDATE `MemberReports` SET `OptOutCount`= 0 WHERE `id`= $id"; 
$mysqli->query($query);
if ($mysqli->affected_rows > 0) { echo $mysqli->affected_rows." records reset.\n"; }
if ($mysqli->error) { die($mysqli->error); }

Open in new window


When using the following code  the value in OptOutCount remains at '2' no matter how many times the page is called on. It just doesn't seem to UPDATE.
$query="UPDATE `0_Promo_eMailReports` SET `OptOutCount`=`OptOutCount`+2 WHERE `UniqueT`='$UniqueT' ";
$mysqli->query($query);
if ($mysqli->affected_rows > 0) { echo $mysqli->affected_rows." records updated.\n"; }
if ($mysqli->error) { die($mysqli->error); }

Open in new window


I don't see how the script can be called twice, because it's the only script on the page.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41714842
Okay give me a moment to try this new approach from Kim
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41714843
Again other names. Is this some kind of psychological test?

Bye, Olaf.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41714845
When using the following code  the value in OptOutCount remains at '2' no matter how many times the page is called on. It just doesn't seem to UPDATE.

Are you sure you saved and uploaded the changed script without reset?
If the updates get stuck at '2', it's a sign you only updated the code in your editor, not on hdd and not serverside, if it's not localhost.

Bye, Olaf.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41714850
Here are the results when OptOutCount is set to '0' first:
UPDATE `0_Promo_eMailReports` SET `OptOutCount`=`OptOutCount`+1 WHERE `id`='1468420091' resulted in an OptOutCount of 2 and 1 record(s) updated.

Note: I've changed the table name from MemberReports to 0_Promo_eMailReports
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41714852
Again, are you sure you saved and updated the code serverside before executing it? If the result is as expected with the previous code, it's a good sign you miss this point.

Bye, Olaf.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41714856
Of course my friend. I'm working directly off the server.
Sas
0
 
LVL 21

Expert Comment

by:Kim Walker
ID: 41714860
Was this the only record that was updated? There isn't another record with the same id that was updated to 1?
0
 
LVL 21

Expert Comment

by:Kim Walker
ID: 41714861
If you don't reset it to zero and execute it a second time, does the result change to 4 and then the third time to 8?
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41714866
With each hit to the php page the value is increased as such:
2 to 4 to 6 to 8 etc.

And yes there's only one entry with that id number.
I want to try something else, give me a few minutes.
Sas
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41714892
Here's something interesting.
When I set `OptOutCount` to '0' the echo "ExistingCount=$ExistingCount"; returns with a value of '1'.
Not '0'.
If  I set `OptOutCount` to '1' the echo indicates a value of '2' in `OptOutCount`.
Not '1'.

 So the problem is not with the UPDATE portion of the script. The problem is we are not getting the true value of `OptOutCount`.
How could that be?

      
$Get_Counts="SELECT `OptOutCount` FROM 0_Promo_eMailReports WHERE id='$id'";
	$Get_Counts_result=mysql_query($Get_Counts)
	or die(mysql_error());
	while($row = mysql_fetch_array($Get_Counts_result)){
$ExistingCount=$row['OptOutCount']; 
echo " ExistingCount=$ExistingCount";}

Open in new window

0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41714964
This is insane!
Once i remove all the ` from the code the echo "ExistingCount=$ExistingCount"; returns the correct value.
    	//Get previous counts
	$Get_Counts="SELECT OptOutCount FROM 0_Promo_eMailReports WHERE id='$id'";
	$Get_Counts_result=mysql_query($Get_Counts)
	or die(mysql_error());
	while($row = mysql_fetch_array($Get_Counts_result)){
$ExistingCount=$row[OptOutCount]; //if($ExistingCount>='1'){$ExistingCount="$ExistingCount"-1;}
echo "ExistingCount=$ExistingCount";
} 

Open in new window


But once I add the second half of the mysqli query it's back to UPDATING the wrong value again!
$query="UPDATE `0_Promo_eMailReports` SET `OptOutCount`=`OptOutCount`+1 WHERE `UniqueT`='$UniqueT'"; 
$mysqli->query($query);

Open in new window


WHY???
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 21

Expert Comment

by:Kim Walker
ID: 41714970
If you run your update statement with a static value for $UniqueT in phpMyAdmin, does it produce the expected results? Open the table in phpMyAdmin, click the SQL tab, paste your update query with a static UniqueT value and click the GO button. If this still doubles the increment value, there's a problem with your table and nothing we do in PHP is going to change that.

Perhaps it would be a good idea to go to Operations in phpMyAdmin and click the Check Table button to see if it's OK.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41714978
Open the table in phpMyAdmin, click the SQL tab, paste your update query with a static UniqueT value and click the GO button.

It does not give me any value, let alone a double increment. All it returns is "1 row affected. (Query took 0.0006 sec)"

And Check Table results that everything is okay.
0
 
LVL 21

Expert Comment

by:Kim Walker
ID: 41714992
Sorry, you would have to browse the table afterward to see the result.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41715004
I can't follow what you're doing how and why. Why do you do this?
$ExistingCount="$ExistingCount"-1;

Open in new window

If you want to see the existing count, you don't change it.

And why do you put something into a string, which is a number? Why do you mix specifying the record with id and UniqueT? You seem to do something fundamentally wrong. You're not consistent and concise about your doings and that gives no concise result. You  mix up table names and fields to specify the row.

You work on the server, fine. Acting in phpMyAdmin you initially only change the browser HTML, if you edit something. To get it to the database you have to save or execute a query there.

You work on your code in some editor, again you have to save, before the next run does that changed code.

Surely you'll say you know this and you do save data and code changes. Well, in generally knowing this doesn't mean you do things thoroughly and always. You're jumping here and there with table names, use of backticks vs not using them or use of id vs UniqueT shows me how you are not focused. If you don't want to show real names and data, then first create a repro of your problem and come here with the full blown code for us to analyse, the way you do this is not helpful, neither to us nor to yourself.

Let's just do a script, which determines inital and final state of a full row you query, from the exact same table. Getting phpMyAdmin out of the picture. Also the script will have a version number it echos...

$version = "1.0.0";
echo "Running test script version ".$version;
// fill in $hostname,$username,$password,$dbname credentials 
// and any other initialisation, eg $UniqueT.

$mysqli = new mysqli($hostname,$username,$password,$dbname);
$mysqli->set_charset('utf8');

//Get initial row state
$query="SELECT * FROM `0_Promo_eMailReports` WHERE `UniqueT`=$UniqueT"; 
$res = $mysqli->query($query);
if ($mysqli->affected_rows > 0) { 
	echo "<p><i>{$query}</i> fetched {$mysqli->affected_rows} record(s).</p>\n";
}
if ($mysqli->error) { die($mysqli->error); }
while ($row = $res->fetch_array() ) {
	echo "<p>"
	print_r($row);
	echo "</p>\n";
}

//increment
$query="UPDATE `0_Promo_eMailReports` SET `OptOutCount`=`OptOutCount`+1 WHERE `UniqueT`=$UniqueT";  
$mysqli->query($query);
echo "<p><i>{$query}</i> updated {$mysqli->affected_rows} record(s).</p>\n";

//Get final row state
$query="SELECT * FROM `0_Promo_eMailReports` WHERE `UniqueT`=$UniqueT"; 
$res = $mysqli->query($query);
if ($mysqli->affected_rows > 0) { 
	echo "<p><i>{$query}</i> fetched {$mysqli->affected_rows} record(s).</p>\n";
}
if ($mysqli->error) { die($mysqli->error); }
while ($row = $res->fetch_array() ) {
	echo "<p>"
	print_r($row);
	echo "</p>\n";
}

Open in new window

When you change something the first change should be the version number. Think of the three parts of it as major version, minor version, and a patch number. So mainly you change the last one. You could also simply do with a single number, for this is just a test script.

Maybe you're also just influenced of data changes happening in parallel to your testing from the website? If you want to exclude any other influence better work on test data. As said a repro code of some separate test data would be much more to the point.

Bye, Olaf.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41715806
Hi Olaf.
I'm totally baffled by all of this and I want you to know that I appreciate your help greatly.
Sas

Below are the results:
Running test script version 1.0.0

SELECT * FROM `0_Promo_eMailReports` WHERE `UniqueT`=1468420091 fetched 1 record(s).

Array ( [0] => 49 [id] => 49 [1] => 0 [admin_ID] => 0 [2] => [Target_ID] => [3] => [wrapperURL] => [4] => 1468420091 [UniqueT] => 1468420091 [5] => 2016-07-13 [DateSent] => 2016-07-13 [6] => 10:28 AM [TimeSent] => 10:28 AM [7] => Wednesday, July 13, 2016 [OpenDate] => Wednesday, July 13, 2016 [8] => 1:49 PM [OpenTime] => 1:49 PM [9] => 1 [SentCount] => 1 [10] => 18 [OpenCount] => 18 [11] => 11 [OptOutCount] => 11 [12] => [PopulationCLICKS] => [13] => 9 [webCLICKS] => 9 [14] => [detailCLICKS] => [15] => [StoreCLICKS] => [16] => [Link_1_CLICKS] => [17] => AN INTRODUCTION TO OUR SCHOOL [Mail_Subject] => AN INTRODUCTION TO OUR SCHOOL [18] => [Mail_BounceBack] => [19] => Letterhead [Mail_Template] => Letterhead [20] => [QualifiedLead] => [21] => [opt_out] => [22] => [neighborhood] => )

UPDATE `0_Promo_eMailReports` SET `OptOutCount`=`OptOutCount`+1 WHERE `UniqueT`=1468420091 updated 1 record(s).

SELECT * FROM `0_Promo_eMailReports` WHERE `UniqueT`=1468420091 fetched 1 record(s).

Array ( [0] => 49 [id] => 49 [1] => 0 [admin_ID] => 0 [2] => [Target_ID] => [3] => [wrapperURL] => [4] => 1468420091 [UniqueT] => 1468420091 [5] => 2016-07-13 [DateSent] => 2016-07-13 [6] => 10:28 AM [TimeSent] => 10:28 AM [7] => Wednesday, July 13, 2016 [OpenDate] => Wednesday, July 13, 2016 [8] => 1:49 PM [OpenTime] => 1:49 PM [9] => 1 [SentCount] => 1 [10] => 18 [OpenCount] => 18 [11] => 12 [OptOutCount] => 12 [12] => [PopulationCLICKS] => [13] => 9 [webCLICKS] => 9 [14] => [detailCLICKS] => [15] => [StoreCLICKS] => [16] => [Link_1_CLICKS] => [17] => AN INTRODUCTION TO OUR SCHOOL [Mail_Subject] => AN INTRODUCTION TO OUR SCHOOL [18] => [Mail_BounceBack] => [19] => Letterhead [Mail_Template] => Letterhead [20] => [QualifiedLead] => [21] => [opt_out] => [22] => [neighborhood] => )
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41715823
Ok, this shows it's alright, the value was incremented from 11 to 12. So is the problem solved?

You read a record into a PHP array, you update the database record, the array then stays as is, until you requery the same record and read back the updated value to see it. This is a very simple SQL roundtrip. I think you have had a wrong expectation of what things should be.

Bye, Olaf.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41715853
I'm so terribly confused Olaf.
I just ran your script again and the OptOutCount went from 12 to 14 in the db.

Running test script version 1.0.0

SELECT * FROM `0_Promo_eMailReports` WHERE `UniqueT`=1468420091 fetched 1 record(s).

Array ( [0] => 49 [id] => 49 [1] => 0 [admin_ID] => 0 [2] => [Target_ID] => [3] => [wrapperURL] => [4] => 1468420091 [UniqueT] => 1468420091 [5] => 2016-07-13 [DateSent] => 2016-07-13 [6] => 10:28 AM [TimeSent] => 10:28 AM [7] => Wednesday, July 13, 2016 [OpenDate] => Wednesday, July 13, 2016 [8] => 1:49 PM [OpenTime] => 1:49 PM [9] => 1 [SentCount] => 1 [10] => 18 [OpenCount] => 18 [11] => 13 [OptOutCount] => 13 [12] => [PopulationCLICKS] => [13] => 9 [webCLICKS] => 9 [14] => [detailCLICKS] => [15] => [StoreCLICKS] => [16] => [Link_1_CLICKS] => [17] => AN INTRODUCTION TO OUR SCHOOL [Mail_Subject] => AN INTRODUCTION TO OUR SCHOOL [18] => [Mail_BounceBack] => [19] => Letterhead [Mail_Template] => Letterhead [20] => [QualifiedLead] => [21] => [opt_out] => [22] => [neighborhood] => )

UPDATE `0_Promo_eMailReports` SET `OptOutCount`=`OptOutCount`+1 WHERE `UniqueT`=1468420091 updated 1 record(s).

SELECT * FROM `0_Promo_eMailReports` WHERE `UniqueT`=1468420091 fetched 1 record(s).

Array ( [0] => 49 [id] => 49 [1] => 0 [admin_ID] => 0 [2] => [Target_ID] => [3] => [wrapperURL] => [4] => 1468420091 [UniqueT] => 1468420091 [5] => 2016-07-13 [DateSent] => 2016-07-13 [6] => 10:28 AM [TimeSent] => 10:28 AM [7] => Wednesday, July 13, 2016 [OpenDate] => Wednesday, July 13, 2016 [8] => 1:49 PM [OpenTime] => 1:49 PM [9] => 1 [SentCount] => 1 [10] => 18 [OpenCount] => 18 [11] => 14 [OptOutCount] => 14 [12] => [PopulationCLICKS] => [13] => 9 [webCLICKS] => 9 [14] => [detailCLICKS] => [15] => [StoreCLICKS] => [16] => [Link_1_CLICKS] => [17] => AN INTRODUCTION TO OUR SCHOOL [Mail_Subject] => AN INTRODUCTION TO OUR SCHOOL [18] => [Mail_BounceBack] => [19] => Letterhead [Mail_Template] => Letterhead [20] => [QualifiedLead] => [21] => [opt_out] => [22] => [neighborhood] => )
0
 
LVL 21

Expert Comment

by:Kim Walker
ID: 41715860
Olaf is on the right track here. There must be something else in your file that is updating the record a second time. In the first results of Olaf's test, OptOutCount started as 11 and went to 12 after the update. In the second results you just posted, it went from 13 to 14. At what point in time did it go from 12 to 13?

Have you isolated Olaf's test code into a file by itself?
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41715901
Yes Kim,
That's the only thing on the page other than a png image of a logo, and an 'include' to the $hostname,$username,$password,$dbname credentials.

I removed that image and ran the script again. The result was CORRECT! The OptOutCount increased by just '1'.

I put back the image and gave it another shot. The result was WRONG! The OptOutCount increased by '2'.

Below is the code that caused the problem, but I don't see anything (other than 'include "../_includeFILES/_DomainName_HardWired.php";') that would make this (and me) go crazy. Do you?
include "../_includeFILES/_DomainName_HardWired.php";
print "  
    <meta charset='utf-8'>
</head>  <body>

	<CENTER><TABLE BORDER=0 width=320  bgcolor='#FFFFFF'  cellpadding=8 cellspacing=0>
<TR>
<TD><!-- OUTER FRAME OR BORDER -->


<TABLE BORDER=0 width=320  bgcolor='#FFFFFF'>
<TR>
<TD  colspan=2 align=left  height=100 bgcolor=#FFFFFF>
<P>
<span style='font-size: 16pt; font-family: Arial; color:#000000; font-weight: bold;'>
<img src='http://$ClientDomainName.$ClientDomainTAG/FILES/ART/Logo_200x200.png' width=100 alt='$account_name'>
<P><HR size=1 width=100% color='#840822'>


</CENTER> 

<div  align='right' v align=top Colspan=2  height='20'><FONT face='helvetica, Arial' color=#AF1F44  size='3'><B>
eMail Deleted</B></FONT>&nbsp;
</div>
<center>
<TABLE border=0 width=320  bgcolor='#FFFFFF'>
<TR>
<TD  colspan='1' align='left'  bgcolor='#FFFFFF'>
&nbsp;<BR>

		<FONT face='Verdana, Arial'  size='2' color=black>

		<FONT face='Verdana, Arial'  size='2' color=black>

";

Open in new window

0
 
LVL 21

Expert Comment

by:Kim Walker
ID: 41715907
Are you sure that HardWired.php doesn't include a routine to increment the record? But I can't imagine why it would automatically increment the optout count. It is also possible for the image file to run a routine to increment the record. But again, why would it increment the optout count?

Does the HardWired.php include line come before or after your increment code? It could be causing the page to reload which would repeat your script if it comes after your script.

Either way I believe we've determined that your code is working properly and has been all along.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41715922
Gotta run. Be back in a couple of hours. Thanks for the help. It's really a strange one.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41715933
OptOutCount went from 12 to 14 in the db
Where do you have your eyes?

initial:
[11] => 13 [OptOutCount] => 13

final:
[11] => 14 [OptOutCount] => 14

Incremented from 13 to 14. Whereever you saw a 12, that was outdated, before you ran the script.

Bye, Olaf.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41717513
This is really nutty!
I deleted "include "../_includeFILES/_DomainName_HardWired.php";"
The count that's entered into "OptOutCount" is correct. I check that by watching the results in phpMyAdmin -- both before and after executing your php script.
In "_DomainName_HardWired.php" I saw nothing that should impact the count. Do you?

Below is the code for "_DomainName_HardWired.php"
<?php
$DomainName="MyDomainName";
$DomainTAG="com";

$ClientDomainName="ChildrensInternationalSchool";
$ClientDomainTAG="com";
$ClientEmailAddress="noreply@MyDomainName.com";

$DomainName_DETOUR1="MyDomainName";
$DomainTAG_DETOUR1="com";

$Directory1="_marks";
$Directory2="manage";
$Directory3="Report";
$Directory4="Bounced";
$Directory4a="_LogIn";
$Directory5="QualifiedLEADS";

$Sender_Name="ChildrensInternationalSchool";
$account_name="The Children`s INTERNATIONAL School";
$database="db_ACCESS.inc";

$TAGline="Preparing Children For A Global World";
$TAGimageFULLPATH="http://www.$DomainName.$DomainTAG/$Directory1/$Directory2/ART/Logo_100x100.png";
$TAGimageSize='width=100';

$titleColor='840822';

echo"
 <meta name=\"viewport\" content=\"width=device-width; initial-scale=-1.0; maximum-scale=-1.0; user-scalable=1;\">
<meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\"/>
<meta name=\"viewport\" content=\"target-densitydpi=device-dpi\" />
<meta name=\"MobileOptimized\" content=\"320\"/>
<meta name=\"HandheldFriendly\" content=\"true\"/>

	<link href='http://www.$DomainName.$DomainTAG/CSS/button_NxtORANGE.css'  rel='stylesheet' type='text/css' />
    <link href='http://www.$DomainName.$DomainTAG/CSS/button_NxtGREEN.css'  rel='stylesheet' type='text/css' />
    <link href='http://www.$DomainName.$DomainTAG/CSS/button_NxtGENERIC.css'  rel='stylesheet' type='text/css' />
    <link href='http://www.$DomainName.$DomainTAG/CSS/bg_HeaderBox.css'  rel='stylesheet' type='text/css' />
    <link href='http://www.$DomainName.$DomainTAG/CSS/aLink_ButtonRED_Small.css'  rel='stylesheet' type='text/css' />
    <link href='http://www.$DomainName.$DomainTAG/CSS/aLink_ButtonRED.css'  rel='stylesheet' type='text/css' />
    
    <style>
a.RG_link:link{text-decoration: none; color: ##910021}
a.RG_link:visited {text-decoration: none; color: ##910021}
a.RG_link:hover {text-decoration: none; color:#00AF18}

</style>
";
?>

Open in new window


 Then I started deleting line by line to see if I could find the culpret.
And when I deleted the first 4 CSS links the problem was corrected. No more double increments!
But I don't see anything in any of the 4 CSS links that would cause the problem to begin with.
"_DomainName_HardWired.php" is included in just about every page on the website, and I don't have any other problem with any other page that queries the db.

Below is one of the CSS links:
.button {
	display: inline-block;
	outline: none;
	cursor: pointer;
	vertical-align: top;
	text-align: center;
	text-decoration: none;
	font: 14px/20px Arial, Helvetica, sans-serif;
	padding: 2px 2px 2px ; 
	text-shadow: 2px 3px 2px rgba(0,0,0,.9);
	-webkit-border-radius: .5em; 
	-moz-border-radius: .5em;
	border-radius: .6em;
	-webkit-box-shadow: 0 1px 2px rgba(0,0,0,.2);
	-moz-box-shadow: 0 1px 2px rgba(0,0,0,.2);
	box-shadow: 1px 4px 1px rgba(0,0,0,.2);
}
.button:hover {
	text-decoration: none;
}
.button:active {
	position: relative;
	top: 1px;
}
.NxtGREEN {
	color: #ffffff;
	border: solid 1px #000000;
	background: #084F11;
	background: -webkit-gradient(linear, left top, left bottom, from(#084F11), to(#07BC1B));
	background: -moz-linear-gradient(top,  #084F11,  #07BC1B);
	filter:  progid:DXImageTransform.Microsoft.gradient(startColorstr='#084F11', endColorstr='#07BC1B');
}
.NxtGREEN:hover {
border: solid 1px #000000;
	background: #07BC1B;
	background: -webkit-gradient(linear, left top, left bottom, from(#07BC1B), to(#084F11));
	background: -moz-linear-gradient(top,  #07BC1B,  #084F11);
	filter:  progid:DXImageTransform.Microsoft.gradient(startColorstr='#07BC1B', endColorstr='#084F11');
}
.NxtGREEN:active {
	color: #ffffff;
	background: -webkit-gradient(linear, left top, left bottom, from(#2F2F2F), to(#9F9F9F));
	background: -moz-linear-gradient(top,  #2F2F2F,  #9F9F9F);
	filter:  progid:DXImageTransform.Microsoft.gradient(startColorstr='#2F2F2F', endColorstr='#9F9F9F');
}

Open in new window


Do you have any insight on how to fix this crazy problem?
0
 
LVL 21

Expert Comment

by:Kim Walker
ID: 41717553
I don't see anything obvious. And certainly not from the link tags. But there are several variables that are defined in this include. I wonder what might be prevented from executing in their absence. Do you have errors turned on and are you receiving any undefined variable errors when you remove this include?
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41717878
Errors are on. And none are reported.
Have you ever seen anything like this before?
0
 
LVL 21

Expert Comment

by:Kim Walker
ID: 41718141
No. This is definitely a new one for me.

Are you sure you have errors turned on. You SHOULD be seeing Undefined Variable errors when you remove _DomainName_HardWired.php unless those variables are being established elsewhere. Those errors could be preventing the update from repeating somehow.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41718254
> I check that by watching the results in phpMyAdmin -- both before and after executing your php script.
Well, the value you watched on phpMyAdmin before MUST have been outdated already. This browser display of the tables is no live view into the database, it outdates, its a copy of the data as it was the last time you view it. So refresh the phpMyAdmin page to see the current value before execution.

The only other chance is, you're not alone on this database and other use of it increments the count, too.

That's all there is to it, most probably. You are just not thoroughly.

The code proves, that it only increments by one. Are you really assuming a +1 is sometimes exeuted as +2?

Now go and log each call, you'll see there is no increment by 2, the only thing you'd detect is calls coming from elsewhere also updating the data. If without this code, then not in your log, but always going through SELECT/UPDATE/SELECT the chances other code is acting between your two SELECTs too is very narrow.

I'm off, this is closed to me, find out where else this tables counter is touched. And if not, then just be more precise in your "eye witnessing" of states. The best eye witness of the current value of a table is not the html in a browser, but a SELECT retrieving the value.

Bye, Olaf.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41719027
Looks like I am a little late to this question, so I'll just ask the "view from 50,000 feet" part:

What are you trying to achieve and why?  If you can tell us that in plain, non-technical language, we can probably show you a well-known design pattern that gets the right answer.  

A sample response might be something like "I am trying to find out how many rows there are in a database table" or "I am trying to find the largest auto-increment key."
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41719724
Thanks for joining in Ray,
Simply put I'm trying (and have been successful many times before) increase the value in column 'OptOutCount' by '1' each time the PHP page is called on. For some unknown reason it updates the value by '2' instead.

We have been using the following code to accomplish it.
Notice that when `OptOutCount`+1 the db is increased by '2'.
Now the interesting thing is that when `OptOutCount`+5 the db is updated to '10'
$query="UPDATE `0_Promo_eMailReports` SET `OptOutCount`=`OptOutCount`+1  WHERE `UniqueT`=$UniqueT";  

Open in new window

The earlier conversations may shed some light on the problem.
Thanks,
Sas
0
 
LVL 21

Expert Comment

by:Kim Walker
ID: 41719752
Actually, we've determined that the update is getting executed twice for some reason. Selecting and echoing the value before and after the update shows that the update is adding the correct amount. But the before value has already been updated from the value in the database before the page is loaded. In one of the previous posts you can see that checking the value in the data base before loading the page shows the value to be 12. But what is displayed in the page shows the value before to be 13 and after to be 14. And looking at the database after the page loads confirms the value of 14.

What I suspect is happening is that something in the page is causing it to reload after executing the query the first time.

Until this most recent post, I believed there was another update query somewhere. But if changing the increment to 5 updates the value by 10 instead of 6, it must be repeating the query.
1
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41719762
That'll show up, if you log the calls. The log will show twe consecutive log entries. Then find out what refreshes the page after already been loaded. There are gazillions of ways this can happen.

Anyway, in the mid of the discussion we were not talking about any surrounding HTML and CSS and page load, simply the core execution of the MySQL related queries and some echoes. And this does not run twice on itself. Unless you call it twice yourself.

Logging is a thing helpful to show even that.

Bye, Olaf.
1
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41719789
Okay, so how do I log the calls?
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 125 total points
ID: 41719991
Here's how I would approach it.  Treat the issue as a "hit counter."  In such a design, your database table will record the incoming URL in two forms: PHP_SELF and REQUEST_URI.  You will also record HTTP_REFERER and the current timestamp.  Optionally you may record the HTTP_USER_AGENT and REMOTE_ADDR; it costs almost nothing to get this additional information and it may be useful for granular analysis some day.

That's it!  That's all you need to record.  Each time any page of your web site is loaded, you make a single instantiation call to the hit-counter class.  The hit-counter collects this information (very simple: everything you need to record exists in superglobal variables), and makes one INSERT query.  Because there are no UPDATE queries, race conditions cannot damage your data set or otherwise cause quirky results.  No need for transactions or LOCK TABLES or any of that complicating stuff that may be needed with UPDATE queries coming from many different places at the same time.

If you're really lucky and popular, your site will get a lot of hits, and this table will grow very fast.  You can learn about how to "prune" a database table, offloading old rows into summary rows, but that's a separate question.

The SELECT queries probably write themselves once you see the data collection.  By way of example, here are some questions you can ask and answer from this simple table.

0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41720224
Take Ray's approach perhaps. I already mentioned you can also use http://php.net/manual/en/function.error-log.php in my answer 41714737

The message you log can contain any info you like, also the superglobal variables Ray mentions, and the query, and  the counter value...

Just seeing double entries after testing your page you'd already know one request causes two calls somehow. Well, then look for Ajax in your HTML, redirects with header() in your PHP or anything else causing double calls. Maybe it's just you yourself requesting the page twice sometimes, being impatient?

Bye, Olaf.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41723925
I haven't done anything different and it's working now.
It must have something to do with the Full Moon!
Thanks for the attention and help,
Sas
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41723927
How do I award points to you guys?
0
 
LVL 21

Expert Comment

by:Kim Walker
ID: 41724053
I wouldn't object to splitting them evenly. I'm happy the problem has gone away, but I would like to know what caused it.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41724202
Well, in summary I just pointed out the code works as is (even without Kim's improvement of adding +1 in the update itself) and whatever causes counter jumps has to be elsewhere or from calling this code multiple times.

I contributed some ideas on tracking the reasons, I wouldn't object if you give major points to Kim, as he contributed most code to check out.

Bye, Olaf.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 41724630
I don't see a button or any other means to award points. How do I do it?
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41724778
I have have done this twice, perhaps. I'm not having your view. If you're that unsure, maybe request attention with the link at the lower right corner of your initial question post, and ask for help with the closing.

Bye, Olaf.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now