Confused how to write this simple UPDATE/INSERT

Question

OK, I'm confused how to write an INSERT/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 (user_id 2 in this case), so something like:

<input type="text" name="link1" value="www.yahoo.com">  
<input type="text" name="link2" value="www.google.com">  
<input type="text" name="link3" value="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">

There should be 9 text inputs showing up here, the first 3 inputs populated with urls from the links table below...Those inputs don't seem to be showing up in my question even though they do in my preview.

My links table is just 2 columns and looks like:

user_id     linkurl
1           http://www.abcnews.com

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

3           http://www.ebay.com
3           http://www.craigslist.org

How do I go about writing an INSERT/UPDATE query for, say, editing the 3rd link and adding a 4th, possibly a 5th, 6th, 7th, 8th and 9th?

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']}";

Answer 1

I don't really understand what you're having trouble with. It might be helpful if you showed something you tried that didn't do what you wanted.

The one piece of advice I do have is you probably want to add a link_id column to the links table. This would make it much easier to uniquely identify each row, and to preserve the original input order. (I don't know about MySQL's implementation, but in general RDBMS theory you shouldn't assume that rows are stored in the order they were inserted.)

More detail: I am thinking that link_id would be a number between 1 and 9, so that user_id and link_id together uniquely identify a row in the links table. I don't think this is implementable via autoincrement, but I don't know mysql. When you select out of the table, it would be something like "SELECT url FROM links WHERE user_id = 3 ORDER BY link_id"; this would keep the links in the correct order. When the form is submitted, I think you would determine the value of the link_id from the field number. Assuming that your environment has a way to determine the number of rows affected by a SQL operation, I would suggest that you try an update like "UPDATE links SET url = 'newvalue' WHERE user_id = 3 AND link_id = 4", then if that reports 0 rows updated do an INSERT instead.

P.S. I know you said this is a learning project, but as you get further along please read about SQL Injection and how to avoid it!

Answer 2

The simplest way i can think of is to change your html to something like this

<input type="text" name="link1" value="www.yahoo.com">  
<input type="hidden" name="oldlink1" value="www.yahoo.com">  
<input type="text" name="link2" value="">  
<input type="hidden" name="oldlink2" value="">

then when you read it in check if the hidden element was blank, if so do an insert, if not use the value in the update statement

UPDATE table_name SET linkurl='www.mynewlink.com' 
WHERE linkurl='www.yahoo.com' and user_id=1

Answer 3

So I would probably delete all the existing entries for the user and then re-populate them:

$query = "DELETE FROM links WHERE user_id = {$_SESSION['user_id']}";
foreach ($i = 1; $i < 10; $i++) {
  if (!empty($_POST['link' . $i])) {
    $query = "INSERT INTO links (user_id, linkurl) VALUES ({$_SESSION['user_id']}, '" . $_POST['link' . $i] . "')";
  }
}

Answer 4

I'd recommend adding a unique row identifier to the table (such as an identity column in SQL Server) and using that to decide which record(s) to update. Operations without a row identifier would necessarily be inserts.