Confused how to write this UPDATE

[magenta placenta]

OK, I'm confused how to write an update for this simple app I'm developing locally to learn more about db interactions.

I have an "edit" page, which is populated from the db and lists up to 9 user links, so something like:

<input type="text" name="link1" value="http://www.yahoo.com">
<input type="text" name="link2" value="http://www.google.com">
<input type="text" name="link3" value="http://www.amazon.com">
<input type="text" name="link4" value="">
<input type="text" name="link5" value="">
<input type="text" name="link6" value="">
<input type="text" name="link7" value="">
<input type="text" name="link8" value="">
<input type="text" name="link9" value="">
<input type="submit" name="submitted" value="update">

My links table is just 2 columns and looks like:

user_id 	linkurl
2	        http://www.yahoo.com
2	        http://www.google.com 	 	 
2	        http://www.amazon.com

How do I go about writing an UPDATE query for, say, editing the 3rd link and adding a 4th? I have it stuck in my head that I need 9 link columns in my db.

I also have on this same page a section for the user's personal info and I can update that easily with a query like:

$query = "UPDATE users
          SET first_name='$firstname', last_name='$lastname', email='$email', state='$state'"

          if($newpass1){
              $query .= ", pass=md5('$newpass1')";
          }
						
          $query .= " WHERE user_id = {$_SESSION['user_id']}";

[tripy]

you have got the basis...
Now, you cannot update and insert in 1 operation.
If you happens to pass each links in your form, what I usually do when there is not a lot of data, is to delete all, then inserts.
Otherwise, you have to
1) get the form data
2)do a select to check if it exists
2.1) if it exists, ignore it
2.2) if it does not exists, do an insert

In that view, if you drop every records first

delete from links

then you can do a series of insert which each links.
Of course, this means that all the links that are different from the one you pass in your form are deleted...

Something else you could do, is to put an unique index on the link address.
Then, you simply do inserts like
http://dev.mysql.com/doc/refman/5.0/...duplicate.html

insert into links (url) values ('http://www.whatever.com') on duplicate key update url='http://www.whatever.com'

which can be translated to "try to do this insert, but if it fails because the value exists, do an update"

[magenta placenta]

Something else you could do, is to put an unique index on the link address.

Wouldn't this mean that no two users could have the same link? Only one user could have a link to http://www.yahoo.com for example?

[longneck]

make a compound unique index that includes the user id.

[magenta placenta]

The links table was defined: PRIMARY KEY(user_id, linkurl)

So I need to add: UNIQUE (user_id, linkurl) ?

[longneck]

primary key and unique are almost the same, the differences being that PRIMARY KEY does not allow any part of the key to be null and there can only be one PRIMARY KEY on a table.

so the answer to your question is no. in fact, you don't need any additional index to use INSERT ... ON DUPLICATE KEY UPDATE

[magenta placenta]

Thanks for the replies. I'm getting pretty confused on this end, I've been changing so much stuff, I'm having a hard time remembering what I've even changed, lol. I don't think one of my tables is right, so I'm going to be toasting it and STARTING OVER. I'm going to start a new thread for that momentarily, I hope you can follow-up in there.