Confused how to write this simple UPDATE / INSERT
OK, I'm confused how to write an INSERT / UPDATE for this simple application I'm developing locally to learn more about db interactions.
I have an "edit" page that is populated from the db and contains in this case up to 9 custom links ( user_id 2 ), 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 here, the first 3 inputs filled with URLs from the link table below ... These inputs don't seem to appear in my question, although they do in my preview.
My table - only 2 columns and looks like this:
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 start writing an INSERT / UPDATE query, say edit the third link and add a fourth, perhaps 5th, 6th, 7th, 8th and 9th?
I also have a section for the user's personal information on the same page, and I can easily update this with a request, for example:
$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']}";
The easiest 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 check if the hidden element was empty, if so, insert if not use value in update statement
UPDATE table_name SET linkurl='www.mynewlink.com'
WHERE linkurl='www.yahoo.com' and user_id=1
source to share
I really don't understand what you are running into. It would be helpful if you showed something that you tried that didn't do what you wanted.
The only advice I have is that you probably want to add the link_id column to the link table. This will make it easier to uniquely identify each row and maintain the original input order. (I don't know about the MySQL implementation, but in general, in RDBMS theory, you shouldn't assume that rows are stored in the order in which they were inserted.)
More details: I think link_id will be a number between 1 and 9, so that user_id and link_id together uniquely identify a row in the link table. I don't think it can be done with auto-increment, but I don't know mysql. When you select outside the table it will be something like "SELECT url FROM links WHERE user_id = 3 ORDER BY link_id"; this will keep the links in the correct order. When the form is submitted, I think you will determine the link_id value from the field number. Assuming your environment has a way to determine the number of rows affected by the SQL operation, I would suggest you try updating like "UPDATE links SET url = 'newvalue" WHERE user_id = 3 AND link_id = 4 ", then if that reports 0 updated rows, type INSERT instead.
PS I know you said this was a tutorial project, but as you go next, read about SQL Injection and how to avoid it!
source to share
So, I would probably delete all 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] . "')";
}
}
source to share