Updating field with new data, keeping old data

[HDFilmMaker2112]

I'm programming a store right now... I have three tables set-up; one for the products, one for search keywords and the other for customer reviews.

 

My keywords table has keyword_number aka keyword id, keyword, and product_ids.

 

each keyword will contain the product_id of the products that should go under that keyword. Now when I program my admin panel to add products, how do I update the specific product_ids field under the specific keyword.

 

For example say I have:

 

1 | audio | 1,3,5,7,19,30,53

2 | meter |2,5,6,8,20,35,46,53

3 | microphone | 1,3,6,20,46,53

4 | computer | 4, 9, 10,13,15,17

 

Now Say I add a new product, that is assigned a product_id of 54 under the products table... How do I update the keywords table, without wiping the current products in there? Say I add it under the keywords audio and microphone.

 

I need this:

1 | audio | 1,3,5,7,19,30,53

3 | microphone | 1,3,6,20,46,53

 

to become this:

1 | audio | 1,3,5,7,19,30,53,54

3 | microphone | 1,3,6,20,46,53,54

 

Do I have to pull the data out of the field and then replace all of it with the 54 concatenation onto the end?

Or can I some how write a MySQL statement to just concatenation 54 right on to the end of the field, without pulling the data out to replace it again later with the same info, with 54 added?

[mikosiko]

an almost identical issue was discussed [urlhttp://www.phpfreaks.com/forums/index.php?topic=332585.msg1566172#msg1566172]here[/url]  ... pay close attention to PFMaBiSmAd answer and suggestion regarding to the table structure

[HDFilmMaker2112]

Alright, I read over that other thread.

 

I'm curious what the difference would be between separating each item out like that, or keeping them in all in one field with commas, and then later exploding them out in php. It would seem to me doing the explode with php would reduce strain on the database system, but I may end up putting more pressure on the server to process the explode.

 

So speaking in using server resources as effectively as possible, which one would be better? Separated out database or a combined database field with a php explode.

 

So separated database entries versus this:

 

$sql1="SELECT * FROM $tbl_name WHERE keyword_number='1'";
$result1=mysql_query($sql1);


$row=mysql_fetch_row($result1);
$product_ids=$row['product_ids']

$product_ids=explode(",","$product_ids")

 

[mikosiko]

You are wise to ask these questions now, before you proceed further your database; in the long term it will save you a lot of troubles.

 

A good start is to read as much as you can about Relational Databases (RDB) and how they operate;  in escense they are efficient and flexibly as they do ONLY because they are based on strict mathematical set theory, developed by an IBM mathematician, Dr. Codd around 1970.

 

A RDB impose a set of formal rules that you should apply to reach the correct way (according to the RDB theory) to structure your tables; they are called the "Normal Forms", beginning with First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF). The main goal of the Database design is to meets the first 3 Normal Forms, and you will be assured of a solid structure, flexible for most likely every purpose. You can find many references online (search for database normal form). One of the requirements to meet 1NF is that every column (field) should be "atomic" or single-valued; that is, it should not be possible to divide the value into several components. That's the reason you should not ordinarily store first and last names in the same field or list like the one that you are using. It makes it awkward or sometimes impossible to do searches on components of a field, for example.

 

Another search you can do for help: data modeling.

 

Hope this give you a good help and a point to start and model your DB better.