Incorrect data in PHP form? 1

[alohaaaron]

Hi, I have a web form that displays data from a database. Multiple people access the web page and update the data and then hit the submit button to post it to the database. If person A is viewing and making updates to the same record through the web as person B, how will I keep the data accurate? Person A's update may overwrite person B's.

Thanks!

 

[southbeach]

A very legitimate concern. Well, you could:
1) Write a flag to a table to mark a row as locked thus checking if a row is locked prior to allowing a 2nd user editing it.
2) You could have a field with Last Update stamp. Load this to variable and check it prior to saving/updating the record. If it has changed, abort or give user option to post and ignore any other changes made ... by others ...
3) Use time stamp to limit how long can user sit on a record without saving. If it takes too long, abort changes saying that "It took you too long ... try again ..." type of message. This may be annoying but it will teach users to not sit idle on a record.

Hope you get the idea ... I personally like number two as it is very simple to implement. You could also lock the row at the database level but that, I'm afraid is beyond me and/or this forum ... I'm sure others will contribute further.

Good luck!

 

[ingresman]

Yes, lots of ways of doing it. Do a gsearch (here and on google) for optimistic locking for more stuff than you will ever need.
If you can implement your locking stategy in stored procedures so everyone adhers to the same stategy

 

[alohaaaron]

Thanks! The trouble is I have one user that is managing the records and needs to view multiple records at a time displayed like an Excel sheet with input text fields. Then they make changes to multiple fields and hit "save" or "submit", if I used method #2 above they may get prompted to accept the changes many times since they are modifying many records at a time. Other people can only modify one record at a time but there may be many people with open records. I'd like the manager of the records to be able to save all records they have modified by clicking a single button.

Would database record locking work better in this case? The manager initially opens up a web page to view all records not necessarily making changes at this time. Would the records be locked at this time?

 

[southbeach]

I do not think you'll have a person updating multiple records at once. Are you? I believe you will show them a data grid and allow them to pick and edit any record off the grid.

If the latter is the case, you should be able to employ 2nd option. If they are updating the records right off the grid, that is a different thing all together.

How/when is the updating taking place?

 

[jpadie]

taking an alternate view to southbeach's for a moment. if your user IS editing lots of rows like a spreadsheet then consider using ajax interaction to store the updates on a row by row basis rather than waiting for a submit button to be pressed after multiple updates.

 

[southbeach]

Good point jpadie. The one thing that ajax come with is the constantly touching the records. That said, it is a good way to sync your data live cell by cell as the user toggles through the data grid.

Of course, one would have to check for actual changes of cell value so, use onChange event to trigger your ajax scripts.

To simplify things, I would suggest using xajax. xajax is a PHP class you can easily use and deploy with any PHP application. You do not write much (if any) javascript. All the code you write is pure PHP.

Say for instance, you can have an event:
onChange="javascript: xajax_MyPHP_Function(this.value)"

This in turn will execute your PHP Function and pass the variables (the function name would be MyPHP_Function - xajax is prefixed solely because the JS function is written using the same name as the declared PHP function prefixed with ajax). In the same token, you can have the combination of JS and xajax so you would use:

onChange="javascript: myJS_function(this.value)"

and within the JS function name myJS_function you will call your xajax function passing any set of parameters.

Go to sourceforge.net and search for xajax ... download it, check their wiki and I guarantee you will be using it in less than an hour.

 

[alohaaaron]

Hi, I tried the xajax method but I don't seem to be able to update my database.

LastName and PK are fields in the database. Tracking is the tablename. I created a div field at the end of my script below and entered some static information to test if it at least saves that information but no luck.

In the examples I read I don't understand if the:
setParameter(0, XAJAX_JS_VALUE, 39); is necessary? It seems variables can be passed without this line? Some examples have it, others do not.

http://xajaxproject.org/

and this one does not.

http://xajaxproject.org/wiki/Tutorials:Learn_xajax_in_10_Minutes

Also in my SQL_update function, how does this line update the database? $objResponse->addAssign("div1","innerHTML", $name); It seems like it is just updating HTML on the client side and I could eliminate it since I just want to update the database?

Here is my code scaled down.

Thanks for the help.

<?php
require_once("../xajax_core/xajax.inc.php");
$xajax = new xajax();
$xajax->configure('javascript URI', '../');
$xajax->registerFunction("SQL_Update");

//connect to database

$cnx = mysql_connect($hostname,$username,$password) or DIE("DATABASE FAILED TO RESPOND.");

//update record function
function SQL_Update ($saveid,$name) {

$db1 = "Update Tracking Set LastName='$name' where PK='$saveid'";
$cur1 = mysql_query($db1);

$objResponse = new xajaxResponse();

$objResponse->addAssign("div1","innerHTML", $name);

return $objResponse;

}

//display all field names name
$cur = MYSQL_QUERY ("select * from Tracking",$cnx);

$numfields = mysql_num_fields($cur);

echo "<TABLE BORDER=1 CELLPADDING=1 CELLSPACING=0>";
echo "<TR class=bg><th><th>LastName</th></TR>";


//display all records
while ($row=mysql_fetch_row($cur)) { //get a row
for ($count = 0; $count < $numfields; $count++) {
$fieldname = MYSQL_FIELD_NAME($cur,$count);


switch ($fieldname) {

case "PK":
$saveid = $row[$count];
echo "<TR>";
break;
case "LastName":
echo "<td width=25><input type=text class=text SIZE=10 name=$fieldname id=$fieldname value='$row[$count]'></td>";
break;

default:
echo "<td WIDTH=25 ><input type=text class=text SIZE=3 name=$fieldname id=$fieldname value='$row[$count]'></td>";
} //end switch

} //end for
} //end while
echo "</TR>";
echo "</TABLE>";



?>



<html>
<head>

<title>Demo</title>

<?php

// output the xajax javascript. This must be called between the head tags
$xajax->printJavascript();

?>



</head>
<body>



<input type=text name=div1 id=div1 size =5 onChange="SQL_Update(1,this.value)" >


</body>
</html>

 

[southbeach]

alohaaron,

I never got any notice about your having updated this post, hope I am not too late (or so if you have successfully taken care of your problem).

Check this thread

http://www.tek-tips.com/viewthread.cfm?qid=1466795&page=1

here I posted a snip of code where I sampled the use of xajax to dynamically change combo boxes. The same principle can be used to post to your SQL tables.

If you still have questions, come back here and post again!

Regards,