CODEWITHSUNDEEP
phpmysqlsql-update
user2018881
user2018881

Reputation: 11

UPDATE Syntax for use with HTML form and php

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()); 
?>

Upvotes: 1

Views: 185

Answers (2)

Dave Gill
Dave Gill

Reputation: 244

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.

Upvotes: 0

James
James

Reputation: 22247

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

Upvotes: 1

Related Questions