An issue when update my table PHP/MySQL

Question

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 ..>
  <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="[email protected]" />

</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?

Nick Rolando · Accepted Answer · 2011-07-27 17:10:40Z

1

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++;
}

answered Jul 27, 2011 at 17:10

Nick Rolando

26.2k13 gold badges84 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc B · Accepted Answer · 2011-07-27 17:09:40Z

0

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.

answered Jul 27, 2011 at 17:09

Marc B

362k44 gold badges433 silver badges508 bronze badges

2 Comments

Marc B Over a year ago

You need to redesign your tables to be more normalized. You're storing multiple copies of the same data for each user, which wastes space and can lead to some nasty data inconsistencies down the road.

Cheerio Over a year ago

Yes I should do that - thank you again for your amazing help I appreciate it

wonk0 · Accepted Answer · 2011-07-27 17:10:23Z

0

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

answered Jul 27, 2011 at 17:10

wonk0

14.1k1 gold badge23 silver badges15 bronze badges

1 Comment

Cheerio Over a year ago

my bad. so i need to send each id with the each tag, then update them ?

Johnny Craig · Accepted Answer · 2011-07-27 17:15:45Z

0

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

answered Jul 27, 2011 at 17:15

Johnny Craig

4,9902 gold badges28 silver badges28 bronze badges

Comments

Álvaro González · Accepted Answer · 2011-07-27 17:18:23Z

0

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.

answered Jul 27, 2011 at 17:18

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Collectives™ on Stack Overflow

An issue when update my table PHP/MySQL

5 Answers 5

Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related