0

I have 2 tables in my DB - Categories and Products. In products table, I am using a foreign key field named category_id which contains comma separated category ids to which that product belongs to. Now I am using a multi select drop down using which the admin can select multiple categories for which that product belongs to. Now while editing the product, I want to display all the categories from categories table along with the selected categories for that particular product should be marked as selected. My query is something like below but my problem is that it repeats the categories values multiple times. I dont want to repeat the values. Any help would be appreciated. 

<?PHP
$query_product = "SELECT * from cmco_products where product_id=$product_id";
$res_product = mysqli_query($conn, $query_product) or die(mysqli_error($conn));                                 
$numrows = mysqli_num_rows($res_product); //check if the record existis into the DB
if($numrows > 0) //if the record exists into the DB
{   
  $row_product = mysqli_fetch_object($res_product);
  $cat_id = explode(",", $row_product->category_id); 
  ?>    
  <select class="form-control show-tick" name="multi_categories_id" id="multi_categories_id" multiple>
  <?php 
  $query_categories = "SELECT * from cmco_categories order by date_created desc";
  $res_categories = mysqli_query($conn, $query_categories) or die(mysqli_error($conn));
  $x = 1;
  while($row_categories = mysqli_fetch_array($res_categories))
  {
    foreach($cat_id as $key => $val)
    {
      $vals[$key] = trim($val)."<br />";
      $qry_cat = "SELECT * from cmco_categories where category_id =".trim($val);
      $res_cat = mysqli_query($conn, $qry_cat) or die(mysqli_error($conn)); 
      $row_cat = mysqli_fetch_array($res_cat);
      ?>
      <option value="<?php echo $row_categories['category_id']; ?>" <?php if($row_categories['category_id'] == trim($val)) { echo "selected";} ?>><?php echo $row_categories['category_name']; ?></option>
      <?php
    }
  }
  ?>
  </select>
  <?PHP
}
?>

Thanks

Geoffrey
  • 10,843
  • 3
  • 33
  • 46
Smith
  • 1
  • 2
  • 1
    Why not use joins ? –  Sep 23 '18 at 14:53
  • Have a read of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Nigel Ren Sep 23 '18 at 14:54
  • using JOINS is okay but I have another concern of using a delimiter list into the DB which I dont want to display repeatedly while fetching, – Smith Sep 23 '18 at 15:21
  • Your `while` is iterating through all categories. For each category, you are then getting all categories with the IDs in your list. That's a lot more looping than is needed... – Niet the Dark Absol Sep 23 '18 at 17:23
  • would really appreciate if you can please correct my code – Smith Sep 23 '18 at 17:31
  • I want to display all the categories in the multi select input field from the categories table along with selecting only those categories whose ids are sitting inside products table in a comma separated format. Like for eg, I have 1,2,3,4,5,6,7,8,9,10 categories inside categories table but a particular product (product_id) belongs to only 4 categories viz 2,5,6,9. So my multi select option box will show all categories from 1 to 10 but it will also show the select mark on 2,5,6,9. – Smith Sep 23 '18 at 17:41
  • I have now modified the table structure. Now instead of using delimiter list in products table, I am using a 3rd table named categories_products where I am inserting a product_id (eg, 3) with it's associated category_id (eg. 6,5,3,1) in separate rows. Can anybody help me now ? – Smith Sep 24 '18 at 10:11

0 Answers0