Member Avatar for weeon

Hi All....
how to search by price range

----------------------------------------
id price  |   range_type  |  min  |  max
----------------------------------------
1         |  A1           | 1     | 100
2         |  A2           | 101   | 200
----------------------------------------
3         |  B1           | 151   | 250
4         |  B2           | 251   | 400
-----------------------------------------

Ex:
if the numbers are in included in the search is

Example : $search = 50
if $search > 1 and < from 100 "output range_type = A1"
-------------------------------------------------------
Example : $search = 160
output = range_type = A2 and B1
--------------------------------------------
Example : $search = 201
output  = range_type = B1
-----------------------------------------------

Th'x

Edited by weeon because: n/a
  • 3 Contributors
  • 3 Replies
  • 117 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by weeon

Dani AI

Generated

Quick summary: the correct approach is to test whether the search price falls inside each row's stored bounds — pointed this out. Decide whether the bounds are inclusive or exclusive and be consistent (that determines whether a number on a boundary returns one or both ranges). If the data lives in PHP (as asked) you can loop and test each array entry; if it lives in SQL, let the database filter rows.

If ranges may overlap and you want to detect or fix that, run a self-join to list intersecting pairs. Example SQL to find overlapping intervals:

SELECT r1.id, r1.range_type, r1.min, r1.max, r2.id AS overlap_with
FROM ranges r1
JOIN ranges r2
  ON r1.id < r2.id
  AND r1.min <= r2.max
  AND r2.min <= r1.max;

Use a numeric type suited for money (DECIMAL with a fixed scale, not FLOAT) and enforce valid rows (min <= max) with a CHECK constraint or application/triggers on insert/update if your MySQL version does not enforce checks.

For safety and performance: always bind the user value with a prepared statement rather than concatenating it into SQL. Test the query plan with EXPLAIN on large tables. Interval containment queries can cause scans; create sensible indexes and benchmark. For heavy interval workloads consider a data structure in-app (interval tree) or a DB that supports range types and GiST/GIN indexes (PostgreSQL) for scalable interval searches.

Tie this back to 's use case: if multiple matches are acceptable, return them. If a single bucket is required, either make ranges non-overlapping or add a priority rule and pick the top-ranked match.

Recommended Answers

All 3 Replies

Member Avatar for pritaeas

What are you searching, some PHP array, or a sql table ?

Member Avatar for faroukmuhammad

Use this query;

SELECT range_type  FROM tablename WHERE $var >= min AND $var <= max

where;

  • tablename is name of your table
  • $var is the variable containing the users input
Member Avatar for weeon

Ok...Thank's

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.