Jump to content

Updating field with new data, keeping old data


HDFilmMaker2112

Recommended Posts

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?

Link to comment
Share on other sites

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")

 

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.