Hi All,
I want to know wheather I can update (add new data) to a row in mysql database table without replacing existings data.
Ex : There is a row that contains,
ID Name City Groups
1 John Colombo gamers
I want to add 'programmers' to John's Groups.
ID Name City Groups
1 John Colombo gamers ; programmers
How can I do this with php & mysql?
Yes, you can by using concat_ws() MySQL function, for example:
UPDATE table1 SET color = concat_ws(',', color, 'green') WHERE id = XX;
But it leads to other problems, for more information read this:
Making a list like this may make searching difficult (see link) - FIND IN SET could work for you (but separators need to be a comma not semicolon) or the *LIKE*. Both of these can be a bit slow.
Creating a link table may be a better option. I think this is discussed in the link provided by cereal.
users
ID (PK)| Name | City | ...otherfields...
groups
ID (PK)| Label | ...otherfields...
user_groups
UserID (FK) | GroupID (FK) - both fields make a PK
You should normalise your database.
create a second table, called groups, with field id and group
id is the persons id, group is the group they belong to.
The table is then created with a joint primary key of id, group
then each row is a unique entry
eg
1,gamer
1, programmer
2,chemist
2, Programmer
2, flying instructor
3, brain surgeon
4, gamer
4, brain surgeon
4, instructor
5, sailing instructor
etc
Having a list of properties in a single field is generally the wrong approach as searching for an item becomes harder, and all "fixes" to search when two or more properties are in the one field are likely to go wrong at some point, as they use LIKE.
For normalization - see a (very) brief discussion on DW Tutorial: Common Issues with MySQL and PHP - #8: Splitting the Big Table: Normalization. For more info, there is a section, "Further Reading" at the bottom of the article.
Briefly, the 1st Normal Form insists on 'atomic data', i.e. one discrete piece of data per 'cell'.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.