0

this the condition: there is a form in html and php haivng around 120 fields its for a website i am making this form on submitting goes to a php page where i first retrive all the values using $_REQUEST[]and then using insert query insert all of them in their specific coloums in the same table in my mysql database. Now i will have to do all the process again for updating these values. Becuase syntax for insert query and update query are quite different .

I dont want to write another 100 lines of code . Is there any way to use the code i wrote inside my insert query to use to update the data.?

dannysood
  • 1,129
  • 3
  • 18
  • 36

4 Answers4

6

Actually in MySQL there is an alternative syntax for insert that is very similar to the syntax for update. You can write

insert customer set customerid=12345, firstname='Fred', lastname='Jones

etc.

Personally I prefer this syntax because it's easy to see what value is going into each field. This is especially true on records with long lists of fields.

On the minus side, it's not standard SQL, so if you ever decide to port your app to a different database engine, all your inserts would have to be rewritten.

Another option I've occasionally used is to write a little function to create your insert and update statements. Then the syntax of your function can be the same, no matter how different the generated code is.

Jay
  • 26,876
  • 10
  • 61
  • 112
2

Another alternative, and depending on requirements and keys, you could use:

replace into tbl (<cols>) values (<vals>)

which will insert if not exist, or replace based on keys (insert/update in one query)

or if you are only inserting and don't want to insert twice, you could use:

insert ignore into tbl (<cols>) values (<vals>)

where if the record is already inserted based on keys, it is gracefully ignored

for more info http://dev.mysql.com/doc/refman/5.0/en/replace.html

dannysood
  • 1,129
  • 3
  • 18
  • 36
Adam MacDonald
  • 1,958
  • 15
  • 19
  • yes this is exactly what i was looking for but i couldnt understand the difference between the two (my primary key is names of different cities and what i exactly want is that if the name of city already exists in the database it would update the data for the city else add a new row) which one of the two would be better in this scenario – dannysood Jul 26 '11 at 18:38
  • Hi Danny, for your request, you would want to use replace into. Remember, if your cities include more than one state, to avoid city name conflicts (same city name in multiple states), you may want to make state and city a unique key .. that way, when you replace into, it will use both the city and state, and not update a city in a different state – Adam MacDonald Jul 26 '11 at 18:46
  • thanks and yes i have already made both state and city primary. Thanks :) – dannysood Jul 26 '11 at 18:50
  • Hi Danny, yw :) .. this is just an option/suggestion, city and state should be indexes, and (city+state) together should be unique .. ALTER TABLE ADD UNIQUE (`city` ,`state`); .. or with phpMyAdmin, put a checkmark into both city and state, and then click on Unique (U), perhaps an auto increment id or something could be primary – Adam MacDonald Jul 26 '11 at 19:03
1

There is a quite similar syntax for INSERT and UPDATE:

INSERT INTO <table> SET
 column1 = value1,
 column2 = value2,
 ...
;

UPDATE <table> SET
 column1 = value1,
 column2 = value2,
 ...
WHERE <condition>
;
wonk0
  • 13,402
  • 1
  • 21
  • 15
  • i user the diiferent syntax( "insert into table(coloum1,coloumm2,......)value('value1','value2'........) – dannysood Jul 26 '11 at 18:17
1
INSERT INTO yourtable (field1, field2, field3, ...)
VALUES ($field1, $field2, $field3, ...)
ON DUPLICATE KEY UPDATE field1=VALUES(field1), field2=VALUES(field2), etc...

Details on this construct here.

Marc B
  • 356,200
  • 43
  • 426
  • 500