An issue when update my table PHP/MySQL

I hope my question will be clear and perfect enough to not get down votes - or even the closed status.

I have simple HTML form:

<form ..>
  <开发者_StackOverflow社区input type="hidden" name="user_id" value="1" />

  <input type="text" name="tag[]" value="css" />
  <input type="text" name="tag[]" value="php" />
  <input type="text" name="tag[]" value="sql" />

  ...

  <input type="text" name="email" value="love@stackoverflow.com" />

</form> 

MySQL Schema:

 id 
 user_id
 tag 
 .... 

I want to update tag for the user_id = 1, So I write this code:

Note: I have just one user in the table.

$user_id = $_POST['user_id'];
$tag     = mysql_escape_string($_POST['tag']); // $tag is an array - print $tag
$email   = $_POST['email'];

foreach($tag as $value) {
    $DB = "UPDATE table SET tag = '$value' AND email = '$email' 
           WHERE user_id = '$user_id'";
    .... // run the query 
}

I Get:

 id   user_id   tag
 1    1         sql
 2    1         sql
 3    1         sql

Instead of :

 id   user_id   tag
 1    1         css
 2    1         php
 3    1         sql

I ask If I missed something in the PHP code?

In the first update query, you set all records with a user_id of 1 (all 3 of them) to "css", then "php", and the finally "sql". After the foreach loop is finished, they are all "sql"

You could try this:

$i = 1;
foreach($tag as $value) {
    $DB = "UPDATE table SET tag = '$value' WHERE user_id = '$user_id' AND id=$i";
    .... // run the query 
    $i++;
}

The problem is that you're updating ALL the records where the user_id is a particular value. So if you were to interrupt the script at each stage in your foreach loop, you'd see 3 css tags listed, then 3 php tags, and ending up with 3 sql tags.

For this kind of thing, I tend to just go the easy route - start a transaction, delete the old tags, and insert the new ones, commit, and done. Otherwise you're stuck with having to determine which tags are new, which are stale, and do the appropriate insert/delete commands to get things synched up.

So basically, you'd do something like this:

mysql_query("start transaction;") or die(mysql_error());
mysql_query("Delete from yourtable WHERE user_id=$user_id") or die(mysql_error());

$newtags = array();
foreach ($_POST['tag'] as $tag) {
    $escaped = "($user_id, " . mysql_real_escape_string($tag) . ")";
}
if (count($escaped) > 0) {
   $values = implode(',', $escaped);
   $sql = "INSERT INTO yourtable (user_id, tag) VALUES $values";
   mysql_query($sql) or die(mysql_error());
}
mysql_query("commit;") or die(mysql_error());

Of course, this presumes that the table being used holds ONLY the tag information. If it doesn't, then... well... don't do this.

No, you missed something in the SQL. Your condition only filters on user_id, so your UPDATE changes every record with user_id = 1.

You need to add a column for each tag type in the array, the way you have it structured, your going to need two selectors

ex:

$DB = "UPDATE table SET tag = '$value' WHERE user_id = '$user_id' AND tag_type='1'";

tag type would have a different value depending on the array item

Assuming your table is properly indexed, you can do these queries:

INSERT IGNORE INTO `table` (user_id, tag)
VALUES (1, 'css'), (1, 'php')

... and:

DELETE FROM `table`
WHERE user_id=1
AND tag NOT IN ('css', 'php')

I won't comment on your code since it's obviously not real but please don't forget to escape input data properly.