Avoid repeating results from php+mySQL query

Question

I have an item table from where I'm fetching the values under 10000 of the price column

This is my query in the simplest way:

$sql_range_price = "SELECT c.price FROM items WHERE c.price < 10000 AND c.item LIKE '$anyitem' ORDER BY c.price ASC";

$rs_range_price = mysqli_query($db_conx,$sql_range_price);

Let's say the column fetched has these values:

4495
1995
1500
1399
995
890
798

And this is how I'm playing with them in PHP:

while($row_range_price = mysqli_fetch_array($rs_range_price, MYSQLI_ASSOC)) {
  $rprices = $row_range_price["price"];
  switch (true) {
      case $rprices > 9000 && $rprices <= 10000:
          echo "Items between $9000 and $10000";
          break;
      case ($rprices > 8000 && $rprices <= 9000):
          echo "Items between $8000 and $9000";
          break;
      case ($rprices > 7000 && $rprices <= 8000):
          echo "Items between $7000 and $9000";
          break;
      case ($rprices > 6000 && $rprices <= 7000):
          echo "Items between $6000 and $7000";
          break;
      case ($rprices > 5000 && $rprices <= 6000):
          echo "Items between $5000 and $6000";
          break;
      case ($rprices > 4000 && $rprices <= 5000):
          echo "Items between $4000 and $5000";
          break;
      case ($rprices > 3000 && $rprices <= 4000):
          echo "Items between $3000 and $4000";
          break;
      case ($rprices > 2000 && $rprices <= 3000):
          echo "Items between $2000 and $3000";
          break;
      case ($rprices > 1000 && $rprices <= 2000):
          echo "Items between $1000 and $2000";
          break;
      case ($rprices > 0 && $rprices <= 1000):
          echo "Items between $0 and $1000";
          break;
      default:
      {
        echo "No items in price range.";
        break;
      }
    }

This would print:

Items between $0 and $1000
Items between $0 and $1000
Items between $0 and $1000
Items between $1000 and $2000
Items between $1000 and $2000
Items between $1000 and $2000
Items between $4000 and $5000

What I would like it prints is:

Items between $0 and $1000
Items between $1000 and $2000
Items between $4000 and $5000

My questions now are:

1) How to avoid repeating Items between $0 and $1000 for example. Should I work on the SQL Query or the PHP part?

2) With if conditional it takes virtually the same amount of code. Is that ok doing it using switch? or is there a better or more efficient way?

Thanks a lot for any help or advice.

P.S. I'm not a php or mySQL guru. Just saying this because last time I asked something, I I got negative answers from people assuming I should know everything about the subject before asking.

UPDATE SOLUTION: This is the new QUERY which works now thanks to your help:

SELECT 
  CASE 
    WHEN c.price > 0 AND c.price <= 1000 THEN '1000'
    WHEN c.price > 1000 AND c.price <= 2000 THEN '2000'
    WHEN c.price > 2000 AND c.price <= 3000 THEN '3000'
    WHEN c.price > 3000 AND c.price <= 4000 THEN '4000'
    WHEN c.price > 4000 AND c.price <= 5000 THEN '5000'
    WHEN c.price > 5000 AND c.price <= 6000 THEN '6000'
    WHEN c.price > 6000 AND c.price <= 7000 THEN '7000'
    WHEN c.price > 7000 AND c.price <= 8000 THEN '8000'
    WHEN c.price > 8000 AND c.price <= 9000 THEN '9000'
    WHEN c.price > 9000 AND c.price <= 10000 THEN '10000'
 END AS rprice
FROM mytable
WHERE c.price <= 10000 AND c.item LIKE '$item' GROUP BY rprice ASC";

I didn't change anything else.

Answer 1

If your price bands are irregular you could use a case statement:

SELECT DISTINCT 
 CASE 
 WHEN price <= 1000 THEN 1000 
 WHEN PRICE <= 5000 THEN 5000
 ELSE 999999
 END
 AS price_band
 ...

If your price bands are all exactly the same you could use:

SELECT DISTINCT 
 TRUNCATE(1000 - 1, -3) + 1 AS price_from, 
 TRUNCATE(1000 - 1, -3) + 1000 AS price_to ...

As an alternative to using SELECT DISTINCT to elimiate duplicate rows you could use COUNT(*) and GROUP BY to get the number of items in each band.

you could do a similar thing in PHP but using the SQL statment to give you the bands reduces the number of records that have to be passed to PHP.

Answer 2

Try using SELECT DISTINCT price un your query