How to use a form to insert OR update | Newbie | | Join Date: May 2007
Posts: 7
| | |
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
|  | Moderator | | Join Date: Nov 2006 Location: Iceland
Posts: 3,745
| | | re: How to use a form to insert OR update
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.
| | Newbie | | Join Date: May 2007
Posts: 7
| | | re: How to use a form to insert OR update
Quote:
Originally Posted by 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. 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
| | Moderator | | Join Date: Nov 2006 Location: Iceland
Posts: 3,745
| | | re: How to use a form to insert OR update
Quote:
Originally Posted by fjm1967 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. Quote:
Originally Posted by fjm1967 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 :) Quote:
Originally Posted by fjm1967 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. Quote:
Originally Posted by fjm1967 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.
| | Newbie | | Join Date: May 2007
Posts: 7
| | | re: How to use a form to insert OR update
Quote:
Originally Posted by Atli 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
| | Moderator | | Join Date: Nov 2006 Location: Iceland
Posts: 3,745
| | | re: How to use a form to insert OR update
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?
| | Newbie | | Join Date: May 2007
Posts: 7
| | | re: How to use a form to insert OR update
Quote:
Originally Posted by 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? :) 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.
:(
| | Moderator | | Join Date: Nov 2006 Location: Iceland
Posts: 3,745
| | | re: How to use a form to insert OR update
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 :)
| | Newbie | | Join Date: May 2007
Posts: 7
| | | re: How to use a form to insert OR update
Quote:
Originally Posted by 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 :) 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?
| | Moderator | | Join Date: Nov 2006 Location: Iceland
Posts: 3,745
| | | re: How to use a form to insert OR update
Quote:
Originally Posted by fjm1967 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.
| | Newbie | | Join Date: May 2007
Posts: 7
| | | re: How to use a form to insert OR update
Quote:
Originally Posted by Atli 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. :)
| | Moderator | | Join Date: Nov 2006 Location: Iceland
Posts: 3,745
| | | re: How to use a form to insert OR update
Quote:
Originally Posted by fjm1967 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
-
}
-
}
-
| | Newbie | | Join Date: May 2007
Posts: 7
| | | re: How to use a form to insert OR update
Quote:
Originally Posted by Atli 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.
| | Moderator | | Join Date: Nov 2006 Location: Iceland
Posts: 3,745
| | | re: How to use a form to insert OR update
Your very welcome. Glad I could help :)
See you around.
| | Newbie | | Join Date: Sep 2007
Posts: 1
| | | re: How to use a form to insert OR update
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.
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,295 network members.
|
