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

Question

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!

No, no, no, no, no. Don't do this. Never store multiple values in a single column. Add another table to make the relation. — juergen d
– juergen d, Commented Jan 31, 2015 at 15:42

Cristian Greco · Accepted Answer · 2015-01-31 15:59:07Z

1

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.

edited Jan 31, 2015 at 15:59

answered Jan 31, 2015 at 15:50

Cristian Greco

2,6161 gold badge21 silver badges24 bronze badges

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

Comments

user3030212 · Accepted Answer · 2015-01-31 15:58:12Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related