explode when use INNER JOIN

Question

Trying to explode id's into option values with INNER JOIN function Would you assist to find what is missing? Below is my code:

 <select name="productSize" class="span8 tip" required>
 <?php  $query=mysqli_query($con,"select size.id,size.sizeName from size 
        INNER JOIN products ON size.id=products.productSize where 
        products.id='$pid'");

while($row = mysqli_fetch_assoc($query)) {

    $size = $row['productSize'];
    $boom = explode(",", $size);
    foreach ($boom as $row){
        echo '<option value='.$row['id'].'>'.$row['sizeName'].'</option>'; 
    } ?>
  </select>

UPDATE: Thanks to guys pointing out on my mistakes, here is an updated code showing productSize comma-separated ID's as option values.

  <?php $query=mysqli_query($con,"select size.id, size.sizeName, 
         products.productSize from size INNER JOIN products ON 
         size.id=products.productSize WHERE products.id='$pid'");

         while($row = mysqli_fetch_assoc($query)) {

         $size = $row['productSize'];
         $boom = explode(",", $size);
         foreach ($boom as $row){

          echo '<option value='.$row['id'].'>'.$row['sizeName'].'</option>'; 
             }} ?>

However there is still an issue: need sizeName to be shown instead of ID's. P.S. I am new to php so excuse me for such simple questions :)

What’s missing - from the question - is the actual problem description. Please go read [ask], then edit your question accordingly. — misorude, Jan 10 '19 at 08:16
Do a `var_dump($row);` inside your loop, and you will see what the structure of that actually is. — misorude, Jan 10 '19 at 08:17
@misorude Only first value is reflected in option, all the rest after "," are missing — Yelena TsK, Jan 10 '19 at 08:19
You're overwriting the variable `$row` in the inner `foreach` loop. You're also missing the `}` for the outer loop. — Barmar, Jan 10 '19 at 08:25
@Barmar I thought about it, but fail to write it correctly.. I mean not selecting productSize — Yelena TsK, Jan 10 '19 at 08:26
You also shouldn't store comma-separated lists in database columns, you should normalize your data. See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Barmar, Jan 10 '19 at 08:27
You can use `GROUP_CONCAT()` to return it as a comma-separated list after joining with the table. — Barmar, Jan 10 '19 at 08:28
@misorude It should be array of sizeNames from productSize column (with size ids) of products table — Yelena TsK, Jan 10 '19 at 08:28
I did not ask you what it _should be_, but what it actually is! Again, use var_dump or similar to make debug outputs, and _verify_ such things. — misorude, Jan 10 '19 at 08:30
As @Barmar advised: You also shouldn't store comma-separated lists in database columns, you should normalize your data, I will not go on with comma-separated lists. Thank you a lot! How can I rate your answer? It really helped me. — Yelena TsK, Jan 10 '19 at 08:35
@YelenaTsK Why do you expect to get anything from the `productSize` column if you don't have `size.productSize` in the `SELECT` list? — Barmar, Jan 10 '19 at 08:37

score 0 · Answer 1 · answered Jan 10 '19 at 09:53

<?php
 $query=mysqli_query($con,"select size.id, size.sizeName, 
         products.productSize from size INNER JOIN products ON 
         size.id=products.productSize WHERE products.id='$pid'");

         while($row = mysqli_fetch_assoc($query)) {

         $size[] = $row['productSize'];

         }

         foreach ($size as $res){

          echo '<option value='.$res['size.id'].'>'.$res['sizeName'].'</option>'; 
             }

?>

score 0 · Answer 2 · answered Jan 10 '19 at 10:27

   <?php
$query=mysqli_query($con,"select size.id, size.sizeName, 
         products.productSize from size INNER JOIN products ON 
         size.id=products.productSize WHERE products.id='$pid'");

         while($row = mysqli_fetch_assoc($query)) {
         ?>
         <option value="<?php echo $row['size.id']; ?>"><?php echo $row['sizeName']; ?>/option>
         <?php


         }
?>

explode when use INNER JOIN

2 Answers2