CODEWITHSUNDEEP
mysql
Frank
Frank

Reputation: 624

MySQL: adding a string with a comma to an existing field

In my Users table I have a row called "products_in_sale" that contains the product ids of the products the user sells.

For one user it looks like this. "33" <-- therefore this very user sells only one product with the id 33.

Now, since users can sell more than one product, I have to join another number (preferably with a "," in between) to this field when a user creates a new product.

How does the necessary UPDATE statement have to look?

maybe 'UPDATE Users SET products_in_sale = products_in_sale + ", '.$id.'"'; ?

thanks for your help!

Upvotes: 0

Views: 132

Answers (2)

Cristian Greco
Cristian Greco

Reputation: 2596

Use the CONCAT_WS function:

UPDATE Users SET products_in_sale = CONCAT_WS(',', products_in_sale, newProductId) where userId = ?

This query should work also for the first insert, if your products_in_sale column defaults to NULL.

Anyway, as suggested by juergen, using another table would be a better option.

Upvotes: 1

user3030212
user3030212

Reputation: 439

Never never store multiple values in a single cell, this is a violation of first normal form (1NF). Read more: http://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html

Every value (e.g. product_id) should have its cell in a relational database table. In your case, there should be a table say "user_product" with at least 2 fields - "user_id" and "product_id", and both are composite primary key. Of course, there should be a "user" table storing user details which would have a "user_id" field linking to the "user_id" field of the "user_product" table. Likewise, there should be a "product" table storing product details which would have a "product_id" field linking to the "product_id" field of the "user_product" table.

Upvotes: 1

Related Questions