PHP MySql Caching Problem

[topatidle]

Hello,

 

I'm using PHP 5.2.4 with MySql 5.0.45 on Apache 2.2 to run my site.

The scenario I'm experiencing is as follows:

1) Access a page that displays a result of a query performed on the database. Let's say the result shown is Hello.

2) Change the value stored in the database using the same page (submitting a form and performing an UPDATE query). Let's say the new value is World. The new value is displayed in the page.

3) Check the database - the value stored in the database is World.

4) Access the page over and over again - the value shown changes from Hello to World and vice versa with (almost) each access to the page.

 

I believe the problem is got something to do with caching. I've disabled webserver and proxy caching using header() with Expires, Cache-Control and Pragma. In addition, I pass a unique parameter (timestamp) with each request of the page, so the query string is different with each access to the page.

When I execute the query fetching the data in the MySql Query Browser after the update I always get World as the result.

When I echo the result of the query to the page, the result changes between Hello and World (ensych with the result shown in the page).

 

Some information about the way I perform the queries:

1) The SELECT is executed with mysql_query and the data is fetched with mysql_fetch_array with MYSQL_ASSOC.

2) The UPDATE is executed as a transaction: mysql_query("SET AUTOCOMMIT=0") and mysql_query("BEGIN") are called before the UPDATE query and mysql_query("COMMIT") is called after the UPDATE query.

 

Does anyone know what causes this problem and how it can be fixed? it's been bothering me for a long time now, and I think it's time for me to solve it.

 

Thanks in advance.

[teng84]

can you post the code?

 

i believe you are selecting the records before you update or insert the secord

 

eg..select...then update

[topatidle]

Hi,

 

Thanks for the quick reply.

 

I believe that this is not the case -

Each time the page is accesses the data SELECTed from the database is shown in a textbox. The page also has a Submit button that calls a script that updates the database and loads the same page again.

The (short) scenario is:

Access the page - value shown is Hello -> update the value to World and Submit -> the page is shown again and the value shown is World -> access the page again and again (even with different browser windows) and see the value switch between Hello and World.

 

Thanks

[teng84]

cant you post your code?

 

or try to avoid cache 

header("Cache-Control: no-cache, must-revalidate");

[topatidle]

Hi,

 

Here is the code for the main parts (I've removed a lot from the sides, but I'm sure you'll get the picture):

 

helloWorld.php Code:

--------------------

 

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");

header("Cache-Control: no-store, no-cache, must-revalidate");

header("Cache-Control: post-check=0, pre-check=0", FALSE);

header("Pragma: no-cache");

...

<form action="updateValue.php" method="post">

...

$value_sql = mysql_query("SELECT * FROM `values`");

$num_of_values = mysql_num_rows($value_sql);

if($num_of_values > 0) {

while($row = mysql_fetch_array($value_sql, MYSQL_ASSOC)){

foreach( $row AS $key => $val ){

$$key = stripslashes( $val );

}

<input type="text" name="value">$value // let's say there's only one row in the database so there will be only one input field

}

}

...

</form>

 

updateValue.php Code:

----------------------

 

include 'db.php';

 

mysql_query("SET AUTOCOMMIT=0");

mysql_query("BEGIN");

...

mysql_query("UPDATE `values` SET value = '".$_POST['value']."');

...

mysql_query("COMMIT");

...

include 'helloWorld.php';

[teng84]

i still dont completely understand your coding structure

 

but heres what you can do if your update page only do that thing then combine it with your hello world  page and use header not include..

 

so your code should look like this

--------------------

if (your submit button is pressed){

udate here

}

 

then do select here