How to use a form to insert OR update

[fjm1967]

Hi all,

I am nearing the end of programming my select forms and I will be soon need to create my insert and update forms. I have a question as to how this is best handled.

I want to use one form to do the inserts and updates. Now, as far as the data in the database goes, if I have some data in a table already and only 1 field needs to be updated how does this work? Should I use an "if" conditional and isset on every field in the form or just update the whole table?

In other words, if the user wants to update a record and only changes 1 field in the form, I would *think* that only that 1 field should be updated and not all of the fields in the form. Am I correct on this?

I am also curious as how to handle the INSERT of new data as well. Lets say that the same form from above has 3 fields that have *NO* data and the user wants to update 1 or 2 fields in the form and sees that 3 of these fields are blank and then decides to fill it in. Those fields that the user just filled in will need an INSERT and not an update. How is this best done?

I suppose I am asking exactly how simultaneous updates and inserts are handled.

Thanks,

Frank

[Atli]

Hi Frank. Welcome to The Scripts!

It is very important to always validate any data that you receive from your form. Especially if you are going to be inserting it into a Database.
If you don't, a hacker might be able to clean out your entire database by typing SQL syntax into the form input fields!
Check out the htmlentities() function, if you need a place to start.

As for the update queries. I agree with you that you should not update fields that have not been changed. It is, however, often more trouble that it is worth, trying to find out if a field has been changed or not. I would not go through any real trouble to avoid updating data that has not changed, but I would prefer it if possible (especially if they contain large amounts of data).

Even if a field is empty in a row that already exists it will technically have a value, even if it is NULL (tho in reality NULL is not a value). So to insert data into such a field you would use an UPDATE command.
INSERT is only used to create new rows.

[fjm1967]

Hi Frank. Welcome to The Scripts!

It is very important to always validate any data that you receive from your form. Especially if you are going to be inserting it into a Database.
If you don't, a hacker might be able to clean out your entire database by typing SQL syntax into the form input fields!
Check out the htmlentities() function, if you need a place to start.

As for the update queries. I agree with you that you should not update fields that have not been changed. It is, however, often more trouble that it is worth, trying to find out if a field has been changed or not. I would not go through any real trouble to avoid updating data that has not changed, but I would prefer it if possible (especially if they contain large amounts of data).

Even if a field is empty in a row that already exists it will technically have a value, even if it is NULL (tho in reality NULL is not a value). So to insert data into such a field you would use an UPDATE command.
INSERT is only used to create new rows.

Hi Atli,

A couple of things. Firstly, I noticed that somehow... I have two accounts on TSDN. I have an account called "fjm" as well. Is there any way to consolidate accounts or maybe just delete this one? :)

Your right. data validation is something that I will be using in the program and is really nessessary. You are talking about injection, I know..

So, if I am understanding you correctly Alti, you are saying to simply just update all info on the form, right? That will be *much* easier indeed.

Ok. So again, if I am understanding you correctly, INSERT is only to create a new row and UPDATE just updates the *existing* row even if there is no value in a given field. Correct?

Here would be my issue then. I do not want to have a seperate "New record" and "Edit" buttion. I would prefer to have just one button for BOTH inserts and updates and program some logic into the script to decide what needs to happen. Is this possible?

Thanks,

Frank

[Atli]

Hi Atli,

A couple of things. Firstly, I noticed that somehow... I have two accounts on TSDN. I have an account called "fjm" as well. Is there any way to consolidate accounts or maybe just delete this one? :)

I don't know about deleting it but it is probably possible to ban it :P
Tho it would probably be easier if you just stopped using it and logged into the other one.

So, if I am understanding you correctly Alti, you are saying to simply just update all info on the form, right? That will be *much* easier indeed.

Much easier indeed. But it would maybe be good idea to come back to that when you create the first Service Pack :)

Ok. So again, if I am understanding you correctly, INSERT is only to create a new row and UPDATE just updates the *existing* row even if there is no value in a given field. Correct?

Correct.

Here would be my issue then. I do not want to have a seperate "New record" and "Edit" buttion. I would prefer to have just one button for BOTH inserts and updates and program some logic into the script to decide what needs to happen. Is this possible?

Everything is possible! :)

As I imagine this scenario, you would have to choose one or more fields as Unique fields. Then when your user fills out the form you would have to check whether the Unique fields exist. If they do you would update them. If they don't you would create a new row.

I personally would create a 'New' button and an 'Edit' button, but that's just me.

[fjm1967]

As I imagine this scenario, you would have to choose one or more fields as Unique fields. Then when your user fills out the form you would have to check whether the Unique fields exist. If they do you would update them. If they don't you would create a new row.

I personally would create a 'New' button and an 'Edit' button, but that's just me.

Ok, I *may* take your advice on having two buttons. Well, come to think of it, I do have an initial "New" button for the insert of 1 form and an "Update" button for the updates on ALL forms. Follow me on this Atli and see if you can help me a little more please.

I have my data pretty well normalized, so lets take an example of having 4 tables having to do with a customer, ok? These 4 tables comprise information about the customer's address, company name, email, etc..

I was initially thinking to use the New button to insert only the first table of information on the first form. Now that you have opened my eyes to inserting a new row, couldn't I just INSERT into all of the tables having to do with the customer across the board then UPDATE as needed?

Thanks,

Frank

[Atli]

You could do that yes, but how well that will work out depends on you table structures.
It wouldn't make sense for me on my databases but it might for you. Could you post the table structures?

[fjm1967]

You could do that yes, but how well that will work out depends on you table structures.
It wouldn't make sense for me on my databases but it might for you. Could you post the table structures?

:) I would like to post the table structures but unfortunately there is a max upload limit on this forum. There are 114 tables total in the db. I actually came back to tell you that I do not think that will work simply because I am using Innodb (MySQL) and because of the constraints on the tables and not allowing null values. I'm stumped, whooped and out of answers.

:(

[Atli]

I see...

Well guess that means you will have to create some logic to find out whether a row exists or if you need to create it. Shouldn't be that hard.

Or if you want to bypass the whole InnoDB constraints not allowing Null values problem you can always invent some data into the fields until your customers update them :)

[fjm1967]

I see...

Well guess that means you will have to create some logic to find out whether a row exists or if you need to create it. Shouldn't be that hard.

Or if you want to bypass the whole InnoDB constraints not allowing Null values problem you can always invent some data into the fields until your customers update them :)

Hi Atli,

Thanks for the response. I was thinking about maybe putting default values and letting the user enter the data later, The only problem I see with that are duplicate rows and my constraints. There would only be one allowable row with those default values and a second row of data would fail becuase of the unique constraints.

Wouldn't I need to go through a bunch of selects first to find out what data if any has already been inserted into the db before determining whether to use an insert or update? That definetely sounds like A LOT more work then I had planned on. Or am I incorrect on this?

[Atli]

Wouldn't I need to go through a bunch of selects first to find out what data if any has already been inserted into the db before determining whether to use an insert or update? That definetely sounds like A LOT more work then I had planned on. Or am I incorrect on this?

Well, you would only have to query to see if the customer already has a row in the table. I am assuming each customer has an ID that is referenced in each of the other tables?
If so you could probably build a single query to check the all. Somewhat like this:

SELECT
  t1.ID AS 't1',
  t2.ID AS 't2',
  t3.ID AS 't3'
FROM customer
LEFT JOIN t1
  ON t1.cID = customer.ID
LEFT JOIN t2
  ON t2.cID = customer.ID
LEFT JOIN t3
  ON t3.cID = customer.ID
WHERE customer.ID = 2
 

Where t1, t2 and t3 are referencing the ID of the customer table as 'cID'.

If there are no rows in tables t1, t2 and t3 that are linked to the given customer.ID then their respective fields will return a NULL.

So if a table returns NULL you would have to user INSERT, else you would have to use UPDATE.

[fjm1967]

Well, you would only have to query to see if the customer already has a row in the table. I am assuming each customer has an ID that is referenced in each of the other tables?
If so you could probably build a single query to check the all. Somewhat like this:

SELECT
  t1.ID AS 't1',
  t2.ID AS 't2',
  t3.ID AS 't3'
FROM customer
LEFT JOIN t1
  ON t1.cID = customer.ID
LEFT JOIN t2
  ON t2.cID = customer.ID
LEFT JOIN t3
  ON t3.cID = customer.ID
WHERE customer.ID = 2
 

Where t1, t2 and t3 are referencing the ID of the customer table as 'cID'.

If there are no rows in tables t1, t2 and t3 that are linked to the given customer.ID then their respective fields will return a NULL.

So if a table returns NULL you would have to user INSERT, else you would have to use UPDATE.

Atli,

Thanks for that clarification. You are correct by the way; each table has a foreign key from the customer table. So after the joins, if I do this:

[php]
if(isset($myRow)){
mysql_query( "UPDATE bla bla" );
}else{
INSERT blabla
}
[/php]

I think that would work out nicely. :)

[Atli]

Atli,

Thanks for that clarification. You are correct by the way; each table has a foreign key from the customer table. So after the joins, if I do this:

[php]
if(isset($myRow)){
mysql_query( "UPDATE bla bla" );
}else{
INSERT blabla
}
[/php]

I think that would work out nicely. :)

Yes, thats basically what I was thinking.

Except you would have to do more than just check the row. You would have to check the data in the row to see if each of the table needs to be UPDATED or INSERTED. Somewhat like this:

$row = mysql_fetch_assoc($result);
foreach($row as $tblName => $tblHasRow) {
  if($tblHasRow != null) {
    # Update
  }
  else {
    # Insert
  }
}
 

[fjm1967]

Yes, thats basically what I was thinking.

Except you would have to do more than just check the row. You would have to check the data in the row to see if each of the table needs to be UPDATED or INSERTED. Somewhat like this:

$row = mysql_fetch_assoc($result);
foreach($row as $tblName => $tblHasRow) {
  if($tblHasRow != null) {
    # Update
  }
  else {
    # Insert
  }
}
 

Ah yes, I left our the fetch_assoc()

Ok, that sounds very doable indeed. Atli, thank you VERY much for all of your help! You guys are truly terriffic here at TSDN! If I have a problem, I will post back. Thanks again.

[Atli]

Your very welcome. Glad I could help :)
See you around.

[skijared]

Rather than using all that code to see if a record exists you can simply use

ON DUPLICATE KEY UPDATE

example:

mysql_query ("INSERT INTO table (field1, field2) VALUES ('$value1','$value2') ON DUPLICATE KEY UPDATE field1='$value1', field2=CONCAT(field2, ' ', '$value2') ");

In this example I used CONCAT to add the new value to the old value in field2.

Good Luck.