I'm trying to allow users to add new records and update existing fields in a MySQL database using a PHP form.
I've built the form and users can add new records, but when I modify the $add function to use UPDATE instead of INSERT INTO, it uses the values that have been entered into the form to update all of the records instead of just the one that has been edited.
The full code is here: http://pastebin.com/s0TBUYgK
The UPDATE query that I've tried to replace the INSERT INTO query on line 20 with is:
$add = "UPDATE albums SET name = '$name', artist = '$artist', year = '$year'";
You don't have a where clause to restrict the update to just the one record being editted, e.g...
UPDATE albums SET .... WHERE id=$id;
^^^^^^^^^^^^
Remember that sql tends to be the sort of thing where "the less you specify, the more you get".
Given that sort of elementary error, I'm going to guess that you've also done NO sanitization and escaping on the data in $name, $artist, and $year, meaning your code is vulnerable to SQL injection attacks.
You should specify the id.
$add = "UPDATE albums SET name = '$name', artist = '$artist', year = '$year' WHERE `id`='$id'";
Add on your form:
<input type="text" name="id" value="<?php echo $id; ?>" />
and then when processing post
$id = $_POST['id'];