Link to home
Start Free TrialLog in
Avatar of sasnaktiv
sasnaktivFlag for United States of America

asked on

MySQL returning incorrect value

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

ASKER CERTIFIED SOLUTION
Avatar of Kim Walker
Kim Walker
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sasnaktiv

ASKER

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
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.
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
Did you really copy as Kim posted, notice he used backticks for column names, not single quotes.

Please copy & paste.

Bye, Olaf.
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

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.
Olaf, how can I echo/log the calls?
What's the accurate way to write that echo/log?
Sas
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.
TheCount column type is 'init(11)'
And yes, when I set the value to '0' I do it manually.
Thanks for the help Kim.
"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.
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.
Yes Kim, that was a typo. Sorry about that.
I am in fact using phpMyAdmin and there are no triggers.
Sas
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

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
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...
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

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.
Okay give me a moment to try this new approach from Kim
Again other names. Is this some kind of psychological test?

Bye, Olaf.
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.
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
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.
Of course my friend. I'm working directly off the server.
Sas
Was this the only record that was updated? There isn't another record with the same id that was updated to 1?
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?
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
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

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???
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.
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.
Sorry, you would have to browse the table afterward to see the result.
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.
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] => )
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.
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] => )
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?
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

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.
Gotta run. Be back in a couple of hours. Thanks for the help. It's really a strange one.
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.
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?
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?
Errors are on. And none are reported.
Have you ever seen anything like this before?
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.
> 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.
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."
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
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.
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.
Okay, so how do I log the calls?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
How do I award points to you guys?
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.
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.
I don't see a button or any other means to award points. How do I do it?
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.