query to sort an column containing comma separated values in mysql

Question

Am creating an ecommerce project for a client who wants to sell groceries online

and am having a price column which will contain comma separated price of the product for the respective quantity. The problem is how I can give sort by 'price high to low' or 'price low to high' feature.

This is the example table

    **id**    **Price**         **Quantity**           Name
       1          20,30,50         250mlgm,500ml,1lt     Coca-Cola
       2          40,60,70         250mlgm,500ml,1lt     Pepsi

Now the question is what the query would look like if I want to sort the price column from low to high or high to low

ORDER BY Price DESC or ORDER BY Price ASC doesn't work in my case

Please guide me on this

Am populating the price and quantity of each product in select tag by exploding the commas and saving them in an array like this

 <select ><option style='font-size:1vw;' value="Select Unit" >Select Unit</option>  

  <?php
 $drq=$rows['Quantity'];
$drp=$rows['Price'];
  $degh=explode(',',$drp);
 $tryGH=explode(',',$drq);
  for($ij=0;$ij<count($tryGH);$ij++){
 echo "<option style='font-size:1vw;' value=$degh[$ij]  >$tryGH[$ij]&nbsp;-&nbsp;$degh[$ij]</option>";  
  }
  ?> </select>

the result looks like this

Answer 1

Try using convert to convert the string to number and then sort it

SELECT id, Price, Quantity FROM Products ORDER BY CONVERT(Price, UNSIGNED INTEGER) ASC

Answer 2

You could arrange your DB a few ways. One way would be

**id**    **Price**         **Quantity**           Name
       1          20         250mlgm            Coca-Cola
       2          30         500ml              Coca-Cola 
       3          50         1lt                Coca-Cola

Then in your code you could output to the client to select the value with

<select name="unit" >
    <option style='font-size:1vw;' value="Select Unit" >Select Unit</option>
<?php
$query = 'select Quantity, Price, Id, Name from Products ORDER BY name asc, Price DESC';
//execute the query, I don't know your driver
while($row = FETCH_FROM_YOUR_DRIVER) {
    $quantitY = $row['Quantity'];
    $price = $row['Price'];
    $id = $row['Id'];
    $company = $row['Name'];
    echo "<option style='font-size:1vw;' value='$id'>$quantity&nbsp;-&nbsp;$price&nbsp;$company</option>";  
}?>
</select>

Then you should take the ID they select and query the price from your DB. In your current code I think you are taking the price directly from the client. The client however could manipulate that and send you a price of 0.

Update:

<style type="text/css">
.container {
    display:block;
    float:left;
    margin:15px;
}
</style>
<?php
$query = 'select Quantity, Price, Id, Name from Products ORDER BY name asc, Price DESC';
//execute the query, I don't know your driver
$count = 0;
while($row = FETCH_FROM_YOUR_DRIVER /*again not sure of your driver*/) {
    $products[$count]['id'] = $row['Id'];
    $products[$count]['price'] = $row['Price'];
    $products[$count]['quantity'] = $row['Quantity'];
    $products[$count]['name'] = $row['Name'];
    $count++;
}
/* Can be removed, I haven't set up a DB so this is how I test
$products = array(array('id' => 2, 'price' => 30, 'quantity' => '500ml', 'name' => 'Coca-Cola'),
            array('id' => 3, 'price' => 50, 'quantity' => '1lt', 'name' => 'Coca-Cola'),
            array('id' => 4, 'price' => 20, 'quantity' => '25mlgm', 'name' => 'Pepsi'),
            array('id' => 5, 'price' => 30, 'quantity' => '500ml', 'name' => 'Pepsi'),
            array('id' => 6, 'price' => 50, 'quantity' => '1lt', 'name' => 'Pepsi'));
*/
$sameproduct = false;
foreach($products as $key => $product) { 
    if ($key == 0 || $products[($key - 1)]['name'] != $product['name']) { 
        if ($key != 0) { ?> 
            </select>
            </div>
        <?php } ?>
    <div class="container">
        <h4><?php echo $product['name'];?></h4>
        <img src="/images/LOCATION/<?php echo strtolower(str_replace(array(' ', '-'), '_', $product['name']));?>.jpg" /> <br />
    <select name="unit" >
    <?php } ?>
        <option style='font-size:1vw;' value='<?php echo $product['id'];?>'><?php echo $product['quantity'];?> - <?php echo $product['price'];?></option>
<?php } ?>

This requires your names are consistent and also assumes the name in the db matches the name of the image in your file system. It replaces spaces and dashes in the name with underscores.