Update Database and display changes

SteveAllam

I have a page that queiries a mysql table and displays the results on a page (works perfectly). I then allow a user to edit a row and submit to update the database, the form is posted and the screen refreshs.

Now this is where the problem occurs, the database is updated correctly but the page displays the old data. A second submit then will show the correct data.

It would appear that the SELECT statement has processed before the UPDATE is finished. I can't LOCK the tables as this is all happening in the same thread. I know that you can DELAY the UPDATE, but it would appear that you cannot DELAY the select.

Any one have a solution.

mrhappiness

please show your code

if you do UPDATE first and SELECT afterwards you should get the new data

SteveAllam

not the actual code but a simplified version that shows the problem

<?php require_once('Connections/protek.php'); ?>
<?php
function udate() {
$udate = "update options set product_option = '$_POST[option]' WHERE id = '$_POST[id]'";
mysql_query($udate);
}
$maxRows_test = 10;
$pageNum_test = 0;
if (isset($_GET['pageNum_test'])) {
  $pageNum_test = $_GET['pageNum_test'];
}
$startRow_test = $pageNum_test * $maxRows_test;

mysql_select_db($database_protek, $protek);
$query_test = "SELECT * FROM options WHERE product_name = 'sleepgard'";
$query_limit_test = sprintf("%s LIMIT %d, %d", $query_test, $startRow_test, $maxRows_test);
$test = mysql_query($query_limit_test, $protek) or die(mysql_error());
$row_test = mysql_fetch_assoc($test);

if (isset($_GET['totalRows_test'])) {
  $totalRows_test = $_GET['totalRows_test'];
} else {
  $all_test = mysql_query($query_test);
  $totalRows_test = mysql_num_rows($all_test);
}
$totalPages_test = ceil($totalRows_test/$maxRows_test)-1;
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<table border="2" cellpadding="2" cellspacing="2">
  <tr>
    <td>id</td>
    <td>product_name</td>
    <td>product_option</td>
    <td>&nbsp;</td>
  </tr>
  <?php do { ?>
  <tr>
    <form name="form1" method="post" action="">
	<td><input name="id" type="text" id="id" value="<?php echo $row_test['id']; ?>" readonly="true"></td>
    <td><?php echo $row_test['product_name']; ?></td>
    <td><input name="option" type="text" id="option" value="<?php echo $row_test['product_option']; ?>"></td>
    <td><input name="imageField" type="image" onClick="<?php udate() ?>" src="images/update.jpg" width="100" height="40" border="0">
    </form></td>
  </tr>
  <?php } while ($row_test = mysql_fetch_assoc($test)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($test);
?>

mrhappiness

Originally posted by SteveAllam
<input name="imageField" type="image" onClick="<?php udate() ?>"

won't work

php is executed at the server not at the client and therefor can't react to the user clicking somewhere

just call udate(); right after defing the function

SteveAllam

ah but it does! the function is called and executed correctly. DB is updated. it just does'nt do it by the time the re-read of the data is done.

SteveAllam

if you just call the function from somewhere in the script the same thing happens.

mrhappiness

reading your code you realize that the update is done after you read the data, don't you?

and (i know i'm repeating myself) you can't mix php-code and html like you are trying to do with onlick eventhandler...

<?php require_once('Connections/protek.php'); ?>
<?php
function udate() {
$udate = "update options set product_option = '$_POST[option]' WHERE id = '$_POST[id]'";
mysql_query($udate);
}
if (isset($_POST['id'])) {
//the form has been submitted
  udate();
}
$maxRows_test = 10;
$pageNum_test = 0;
if (isset($_GET['pageNum_test'])) {
$pageNum_test = $_GET['pageNum_test'];
}
$startRow_test = $pageNum_test * $maxRows_test;

mysql_select_db($database_protek, $protek);
$query_test = "SELECT * FROM options WHERE product_name = 'sleepgard'";
$query_limit_test = sprintf("%s LIMIT %d, %d", $query_test, $startRow_test, $maxRows_test);
$test = mysql_query($query_limit_test, $protek) or die(mysql_error());
$row_test = mysql_fetch_assoc($test);

if (isset($_GET['totalRows_test'])) {
$totalRows_test = $_GET['totalRows_test'];
} else {
$all_test = mysql_query($query_test);
$totalRows_test = mysql_num_rows($all_test);
}
$totalPages_test = ceil($totalRows_test/$maxRows_test)-1;
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<table border="2" cellpadding="2" cellspacing="2">
<tr>
<td>id</td>
<td>product_name</td>
<td>product_option</td>
<td> </td>
</tr>
<?php do { ?>
<tr>
<form name="form1" method="post" action="">
<td><input name="id" type="text" id="id" value="<?php echo $row_test['id']; ?>" readonly="true"></td>
<td><?php echo $row_test['product_name']; ?></td>
<td><input name="option" type="text" id="option" value="<?php echo $row_test['product_option']; ?>"></td>
<td><input name="imageField" type="image" src="images/update.jpg" width="100" height="40" border="0">
</form></td>
</tr>
<?php } while ($row_test = mysql_fetch_assoc($test)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($test);
?>

give this a try

SteveAllam

Thanks!! but that doesn't even uodate the data at least mine does that!!!

As for not mixing PHP etc. Yeah you can under certain conditions, fact is the function is called and executes correctly wether it is called from within a php area on from an event. it is the order and speed that is the problem. If you use an INSERT instead of UPDATE then when the page refreshes you will get a new row (i.e the row has been created by the function) but no data in it, as the page has displayed before this is written, if you then look at the DB the data has been written correctly.

I have succesfully used events to trigger PHP in plenty of places, and as I said the same bahaviour occurs if you don't use an event trigger, i.e. you check is the $POST variables are set, I used

if ($_POST['option']  != "") {
udate();
}

Gerardo2006

i have this problem too,, it update the table but not at the re-read page,,
any advice plz?
thnx

DoctorWho

Cache issue?

Try headers(no-cache) etc.

IF you close your browser and go back in, do you get the update??