How to append to values already in column.

[theITvideos]

Hi there,

 

I am working on a page. That has all the products listed on it. Every product has a Shipping profile associated with it.

 

Whenever a new product is created, we assign a Shipping profile to that new Product. And its product ID is then inserted into the Shipping profile table.

 

Note: One Shipping Profile can have multiple Products under it.

 

The scenario is that when we create the 2nd product and assign Shipping Profile and insert the 2nd product ID to Shipping Profile, the old value gets replaced with the new ProductID.

 

We just need to somehow append the new ProductID to the existing ProductIDs in the Shipping Profile table.

 

We can do an Update query but that will replace the Old Product ID with the new ProductID. Here we just need to append the new Product ID to the Old product ID. eg

...............................

Shipping Profile Table|

``````````````````

ShippingProfileID      ProductID

1                            1,4,6

2                            3,7

 

Now at the moment, the ProductID gets stored but it just replaces the Old product ID. We just need to append to the existing IDs

 

Any comments or feedback are always welcomed.

 

Thank you!

 

 

[PFMaBiSmAd]

Your design needs to add a separate row for each separate piece of data -

 

ShippingProfileID      ProductID

1                            1

1                            4

1                            6

2                            3

2                            7

 

[theITvideos]

Your design needs to add a separate row for each separate piece of data -

 

ShippingProfileID      ProductID

1                            1

1                            4

1                            6

2                            3

2                            7

 

Thanks for the reply. Shipping profile is a primary key in the ShippingProfile Table. Our designs do not have relationship between tables.

 

And normally the values are stored in columns separated by | using the implode function i.e.

 

implode("|", $_varNam['testID']) . "|";

 

And then values get stored in the table as:

 

|5|7|8|11|

 

And later, the values as read as:

 

$myVar = explode("|", $_varNam['testID']);

 

It works fine. For example you have a listbox and select multiple values it gets stored this way.

 

But to have the ProductID stored this way....?

 

I am thinking that we somehow... get the existing ProductID ( I can get that), then using the Implode() to concatenate the New values with | the and then simply store in the database.

 

How does it sound.

 

I know it may not be the regular way of doing stuff but we have over crazy 200 hundred tables and all of them are designed this way using implode and explode

 

Whats your take on this?

 

Cheers!

 

 

 

 

 

[PFMaBiSmAd]

200 hundred tables

 

LOL

[mikosiko]

validate if your ShippingId exist or not in your Shipping profile table...

 

if exist do an update concatenating then old value with the new addition ... you don't need to implode anything in my opinion

 

    UPDATE ShippingProfile        SET ProductId = CONCAT_WS('|', ProductId, $newproductid)       WHERE ShippingProfile = $theprofile

 

else do an Insert

 

 

I don't like your design... but that is the gun that you chose ... and 200 hundred?... man you have a lot of tables

[ignace]

200 hundred tables

 

A new normal form?

[ignace]

INSERT INTO    ShippingProfile (ShippingProfileID, ProductID)VALUES    ($shippingProfileID, $productID)ON DUPLICATE KEY UPDATE    ProductId = CONCAT_WS('|', ProductId, $productID)