CODEWITHSUNDEEP
phpmysql
Azizul Islam
Azizul Islam

Reputation: 21

Number Range in PHP

I am facing problem number range or price range.

product_id  starts_at   price
1815         1          5.95
1815        36          5.62
1815        96          5.00

SELECT *
FROM price_tiers
WHERE product_id = '$productid' AND store_type='W'
ORDER BY `starts_at` ASC

I want to show

1 to 35 = 5.95
36 to 95 = 5.62
96 to more = 5.00

My Code

<?php
$price_tiers = mysql_query("SELECT * 
                            FROM price_tiers 
                            WHERE product_id = '$productid' 
                              AND store_type='W' 
                            ORDER BY `starts_at` ASC ")  
                     or die (mysql_error());

while ($myrow = mysql_fetch_assoc($price_tiers)) 
{
    echo $starts_at = $myrow['starts_at']. 
                      " to " . "$". 
                      $product_price  =  $myrow['price'].
                      "<br>";
}
?>

Your help would be very much appreciated.

Upvotes: 0

Views: 58

Answers (2)

Arth
Arth

Reputation: 13110

You can do it with user variables, but you have to order twice to get the info you need:

  SELECT product_id,
         starts_at,
         ends_at,
         price 
    FROM ( 
      SELECT product_id,
             starts_at,
             @prev_start - 1 ends_at,
             price,
             @prev_start := starts_at 
        FROM price_tier
        JOIN (SELECT @prev_start := NULL) init 
    ORDER BY starts_at DESC
         ) tiers
ORDER BY starts_at ASC

You can see it in action on this SQLfiddle

Upvotes: 0

Marc B
Marc B

Reputation: 360572

You'll need something like this:

$current = mysql_fetch_assoc($price_tiers);
do {
    $next = mysql_fetch_assoc($price_tiers);
    echo $current['starts_at'];
    echo " to ";
    echo $next['starts_at'] - 1;
    echo $current['price'];
    $current = $next;
while ($next !== false);

This won't work perfectly as-is, but should get you started.

Upvotes: 1

Related Questions