1

I have a form created in HTML. From time to time, our reps need to come back and edit data that they had entered into the form previously. I have it set up where they can enter the OrderForm ID (ex. 1234) and it will populate the fields accordingly. I would like them to be able then to update or make changes to the fields and then when they hit submit, it would create a new number with a .1 attached (1234.1), and update the MySQL database.

I have been able to create the form, populate the search fields, but can't figure out how to update and assign a ".1" to the form ID.

Here is my code:

<?php

$connection = mysql_connect('localhost','username','*******') or die ("Couldn't connect to server."); 
$db = mysql_select_db('DBName', $connection) or die ("Couldn't select database.");

// -------------------------------------------------------------------
// Field Names
// -------------------------------------------------------------------

$drepid=mysql_real_escape_string($_POST['drepid']);
$datepicker=mysql_real_escape_string($_POST['datepicker']);
$repemail=mysql_real_escape_string($_POST['repemail']);
$dateneeded=mysql_real_escape_string($_POST['dateneeded']);
$description=mysql_real_escape_string($_POST['description']);
$qty=mysql_real_escape_string($_POST['qty']);
$pgsizeh=mysql_real_escape_string($_POST['pgsizeh']);
$pgsizew=mysql_real_escape_string($_POST['pgsizew']);
$pageno=mysql_real_escape_string($_POST['pageno']);
$stock=mysql_real_escape_string($_POST['stock']);
$ink=mysql_real_escape_string($_POST['ink']);
$inknote=mysql_real_escape_string($_POST['inknote']);
$rfq=mysql_real_escape_string($_POST['rfq']);
$finishing=mysql_real_escape_string($_POST['finishing']);
$dfirstname=mysql_real_escape_string($_POST['dfirstname']);
$dlastname=mysql_real_escape_string($_POST['dlastname']);
$dorganization=mysql_real_escape_string($_POST['dorganization']);
$email=mysql_real_escape_string($_POST['email']);
$daddress1=mysql_real_escape_string($_POST['daddress1']);
$daddress2=mysql_real_escape_string($_POST['daddress2']);
$dcity=mysql_real_escape_string($_POST['dcity']);
$dstate=mysql_real_escape_string($_POST['dstate']);
$dzip=mysql_real_escape_string($_POST['dzip']);
$phone=mysql_real_escape_string($_POST['phone']);
$fax=mysql_real_escape_string($_POST['fax']);
$proof=mysql_real_escape_string($_POST['proof']);
$whoproof=mysql_real_escape_string($_POST['whoproof']);
$quote=mysql_real_escape_string($_POST['quote']);
$amount=mysql_real_escape_string($_POST['amount']);
$delivery=mysql_real_escape_string($_POST['delivery']);
$notes=mysql_real_escape_string($_POST['notes']);

$data = "UPDATE DB_Table SET drepid='$drepid', datepicker='$datepicker', repemail='$repemail', dateneeded='$dateneeded', description='$description', qty='$qty', pgsizeh='$pgsizeh', pgsizew='$pgsizew', pageno='$pageno', stock='$stock', ink='$ink', inknote='$inknote', rfq='$rfq', finishing='$finishing', dfirstname='$dfirstname', dlastname='$dlastname', dorganization='$dorganization', email='$email', daddress1='$daddress1', daddress2='$daddress2', dcity='$dcity', dstate='$dstate', dzip='$dzip', phone='$phone', fax='$fax', proof='$proof', whoproof='$whoproof', quote='$quote', amount='$amount', delivery='$delivery', notes='$notes' WHERE drfq=.$drfq";
$query = mysql_query($data) or die("Couldn't execute query\"$data\" Error:" . mysql_error()); 
?>
Improve this question
2
  • Consider changing your approach. Do you have to have a decimal system for versioning? You could instead add 3 new fields: dateAdded, dateModified, and numberOfEdits. numberOfEdits would start off as 0 and increment every time you update a row. Commented Jan 29, 2013 at 14:38
  • We use these form ID's as a reference tool - not only when emailing back and forth, but also in relation to creating the print job. It makes it alot easier if the ID number stays the same (ie: 1234) but would change each time a revision is made to a decimal number (ie: 1234.1, 1234.2, 1234.3, etc ...) Currently, nothing is being updated - not even into the existing record. Commented Jan 29, 2013 at 14:59

2 Answers 2

Reset to default
1
  • You will never be UPDATEing a row, you will always be INSERTing a new row
  • If more than one person is using the system at the same time, how do you deal with conflicts? What happens if person A loads a record, person B loads a record, person A saves changes and...person B overwrites those changes
  • is your id field a decimal (number) or a string? Say you're editing record 1004 a bunch of times. What happens when record 1004.9 is reached, do you increment to 1004.10 (equivalent to 1004.1 mathematically). How do you sort the IDs? How do you decide which ID is the "last" update?

Another approach which may be worth a think is to use a history table. Basically you keep your original table with one record per...record, and you track changes made, date, userid in the history table. In this manner you can rebuild any previous version of a record. Conflicts can also be resolved fairly easily. You could also present each version of the record with whatever pseudo decimal notation you want, without having problems sorting.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

James - good stuff. I appreciate your feedback. Here is a real world scenario ... First off, only one rep at a time will need to make revisions to the record since he is the one that initially filled out the form. 90% of the time, that initial form is sufficient to proceed with the print job. Occasionally, he will need to go back into the record and make a revision (paper stock change, # of pages, etc ...) He would make those changes and it would update the database (whether it is a new record, or use of the History table - that is open to discussion).
If a rep ends up putting in more than 2 revisions, it warrants a phone call. 99% of the time there are only 1 or 2 revisions needed for an update per record.
0

Another approach on this would be to add a couple of fields like the other answers. Add a field called deleted and a child_id/parent_id field or something similar. When the user amends the record mark the record as deleted and duplicate the record (without the deleted field set) and set the parent_id to the parent record. Adds to the code for updates etc and you need to only search for non deleted records. But also allows you to see the changes to a record over a period of time.

Improve this answer

4 Comments

Thank you Dave. Rather than deleting a record, could I perhaps put a radio button on the form that says "Update record?". When clicked and submitted, it would then automatically save the record with a decimal revision. Without that field checked, the form would create a brand new record. This might be helpful if the rep needed to use the data in the form to create a new job that is similar (ie: reprint of a prev job, or a new job with similar specs), but it would generate a new ID number. Could I do that?
No records are ever deleted. They are simply marked in the database as deleted which would really mean an old record. In your scenario it might be that you call the field updated and then create a duplicated record with the new values and a link back to the parent record via a parent id.
Definately worth a shot. Do you have some code I could use for that?
Fraid none that I can lay my hands on... On the update of your user form do a select with the user id and grab the user details. Insert them into a new record on the user details table with any additional data. Then update the original record with the deleted flag. Also best to wrap it all in a transaction if possible so you do not get strange DB records on failure.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.