views:

272

answers:

4

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']}";
+1  A: 

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!

Dave Costa
I want the link_id to be an auto_increment, yes? How do I do that when my user_id is an auto_increment? mysql is reporting "Incorrect table definition; there can be only one auto column and it must be defined as a key" when I try to add link_id as an auto_increment.
Edited the answer
Dave Costa
I'll be using mysql_real_escape_string eventually, I'm just focusing on getting the basics working now.
+2  A: 

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
Re0sless
A: 

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] . "')";
  }
}
Brian Fisher
Does this have the potential to delete all user links in the db should an error happen and the insert is not able to run (since it's not a transaction)?
I would recommend putting it in a transaction, and also validating that $_SESSION['user_id'] is an integer.
Brian Fisher
Your session user id should already be validated - on page load. If it's invalid, you should be killing the session and kicking the "user" out.
davethegr8
Yeah, I'm doing that.
I ran the code above and it deleted all my user links, lol. The insert was never made. Note, I did change the code above a little...First, made it a for loop, then did $link{$i} instead of $_POST['link' . $i] as I'm scrubbing/cleaning $_POST and assigning to $linkX
If you post your code I'll take a look at it.
Brian Fisher
$query = "DELETE FROM links WHERE user_id = {$_SESSION['user_id']}";for ($i = 1; $i < 10; $i++) { if (!empty($link{$i})) { $query = "INSERT INTO links (user_id, linkurl) VALUES ({$_SESSION['user_id']}, '" . $link{$i} . "')"; } }
Ugh, line breaks/spacing not recognized here.....I'm cleaning $_POST like: $link1 = trim(strip_tags($_POST['link1']));
I don't see where the problem would have come from, it looks ok to me. On just a side note, I would recommend using arrays instead of var1, var2, etc... Using var[1], var[2], var[3] is cleaner. Sorry I couldn't be more help.
Brian Fisher
A: 

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.

marc
Dave's Answer already included this advice
Brian Fisher