0

i have table like this :

cat_id  sub_cat_id   cat_name    price_net  promo_price  
   1         1        tshirta       25         20
   2         1        tshirtb       35
   3         1        tshirtc       45         10

how i can find min value from this table price_net including promo_price

in php if i use

$query_net="select * from product 
            where sub_cat_id='".$sub_cat_id."' 
            order by price_net asc 
            limit 0,1";

it only show 25 not 10 promo_price not including as min price

in my webit's look like : Tshirt start from USD 10

it's similar with this question : Select Smallest Value From Multiple Columns with PHP/MySQL

hi after read other question i got what i want, i use this 

$query = ("SELECT LEAST(MIN(net_price),MIN(promo_price)) FROM product WHERE (net_price     != '' or promo_price!= '')and sub_cat_id=".$sub_cat_id." ");
$result=mysql_query($query);
 if (!$result) {
     die('Invalid query: ' . mysql_error());
 }
 $num=mysql_numrows($result);
 $i=0;
 while ($i < $num) 
    {
    $pricing[$i]=mysql_result($result, $i);
     $i++;
     }
     sort($pricing);
    $lowest_price = $pricing[0]; //lowest price
    echo" $lowest_price";
 ?>

thank for any help case closed

Community
  • 1
  • 1
Cute Chenz
  • 11
  • 4

1 Answers1

0

Did you mean this?:

UPDATED:

SELECT LEAST(MIN(price_net), MIN(promo_price))
FROM price_net;
Jason Heo
  • 9,956
  • 2
  • 36
  • 64
  • not it's will show two min value, price_net 25 and promo_price 10 i just wan't show only one min value from that table is 10 – Cute Chenz Aug 17 '14 at 09:47
  • @CutecenzAcenz not clear what you're asking. `ORDER BY price_net ASC LIMIT 1` means `MIN(price_net)`. – Jason Heo Aug 17 '14 at 09:50
  • @CutecenzAcenz I've added `LEAST()` function. How about this? – Jason Heo Aug 17 '14 at 09:52
  • yes it's not clear because there is promo price if there is no promo price i can use $query_net="select * from product where sub_cat_id='".$sub_cat_id."' order by price_net asc limit 0,1"; – Cute Chenz Aug 17 '14 at 09:54