updating records based on what they contain in a certain field

[dadamssg87]

I am storing products in my database. The table has id, price, title, description, and add_ons. The add_ons column stores id's of other products that can be purchased with it. These id numbers are separated by commas and there's no limit to how many id numbers can be in the column. So for instance a typical product row will look something like this

 

34|9.99|Box of Candy|Assorted bag of candy|31,32,56,30

 

Now i'm trying to figure out the best way to delete a product. If i'm deleting product item 30, i don't want that "30" to still be in the add_ons column of item 34.

 

I didn't know if there was a more efficient way of doing something like the following

<?php
$item_to_delete = 30;

$query = $this->db->query("SELECT * Products WHERE username = '$username' ORDER BY created DESC");	

foreach($query->result() as $row)
{
$add_ons = explode(",",$row->add_ons);
        
         foreach($add_ons as $key => $id)
         {
               if($id = $item_to_delete)
               {
                    unset($add_ons[$key]);

                    $add_ons = implode(",",$add_ons);

                    $this->db->query("Update Products SET add_ons = '$add_ons' WHERE id = '$row->id'");	
                }
          }
}

?>

[trq]

It's never a good idea to store data like this for this very reason. Instead you should have another table with a row for every relationship.

[dadamssg87]

dangit...you're definitely right