CODEWITHSUNDEEP
mysql
Michael Emerson
Michael Emerson

Reputation: 1813

Selecting the nearest number in a table below a given result in mySQL

I am working on a booking system which has a set of rates based on length of stay (i.e. nights booked) and I need to use this data to retrieve an accurate cost of the booked stay to present to the customer.

However, my client has rates named "Two Weeks +" and "Four Weeks +" meaning anything above two weeks but BELOW four weeks and anything above four weeks etc. However, my statement for retrieving the rates is as follows:

SELECT id, display_name FROM rate_type WHERE nights = ' . $nights;

where $nights is the number of nights chosen by the user on the front end. But I have come across a problem, since there isn't always a match using this method. The database entries are like:

| display_name  | nights |
--------------------------
|  Single       |    1   |
|  Two Nights   |    2   |
|  Three Nights |    3   |
etc...
| Two Weeks +   |   14   |
| Four Weeks +  |   28   |
--------------------------

Thus my problem is, if the user books anything between 15 and 27 nights there is no match and therefore no rate can be obtained. In the case of a user booking between 15 and 27 nights, I'd want to use the 14 night rate (opposed to the 28 night rate) so my question is, how can I write a statement that takes the nights chosen by the user and finds the next LOWEST number of nights in the database to grab the relevant rate?

Upvotes: 0

Views: 23

Answers (1)

fancyPants
fancyPants

Reputation: 51888

You can simply do it like this:

WHERE nights <= <your_user_input> ORDER BY nights DESC LIMIT 1

Upvotes: 3

Related Questions