0

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?

Michael Emerson
  • 1,774
  • 4
  • 31
  • 71

1 Answers1

3

You can simply do it like this:

WHERE nights <= <your_user_input> ORDER BY nights DESC LIMIT 1
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • P.S.: I'm not a programmer, so I might be wrong. But it looks like you are using unsafe code, prone to sql injection. Google this, if you haven't heard of it. – fancyPants Oct 24 '17 at 08:49
  • 1
    Famous last words – Strawberry Oct 24 '17 at 08:58
  • @MichaelEmerson Use parameterized statements using the msqli_*() functions. http://php.net/manual/de/mysqli.prepare.php (as far as I know, like I said, not a programmer) – fancyPants Oct 24 '17 at 09:31
  • It's a Wordpress site, and uses the in-built Wordpress database functions. – Michael Emerson Oct 24 '17 at 11:30
  • @MichaelEmerson I've never had anything to do with Wordpress. Sorry, can't help you more on this one, but a quick search revealed this one: https://stackoverflow.com/questions/2127009/using-wordpress-can-some-one-tell-me-the-best-way-of-sanitizing-input/2127191#2127191 The title looks promising :) – fancyPants Oct 24 '17 at 12:02