matching a user's input with db *SOLVED*

[digitalgod]

Hey guys,

I was wondering how can I match a user's input with something in the DB, meaning if a user types 5, I want it to match it's input with the closest number so if in the db the're a row that has the value 4 and another one the value 8 it will assign it to 4 but if the values are 4 and 6 then it will match it to 6.

If that can't be done how can I just assign it to the closest highest number?

[digitalgod]

anyone?

[trq]

My first question needs to be why? This is possible (everything is), but its not going to be an easy fix.

[digitalgod]

well it's a table reservation script, so a user enters how many they'll be. In the database I have a list of tables like so

id  club_id  night_id  tbl_guests  tbl_x  min_price  full 
1        16          17              4      2        120  false
2        16          17              8      2        240  false

so tbl_guests is the number of people that can be seated on that table and tbl_x is the number of tables. As for min_price, it's the minimum amount they have to spend to be on that table.

so if a user says they'll be 5 then the script needs to know that they have to be seated on a table of 4 and tell him that he has to spend at least 120$. But if I had a table for 6 in there, the script has to reserve that table instead and charge the appropriate amount.

I hope you understand what I mean

[.josh]

okay there is probably a way better way of doing this, but this is what came off the top of my head:

[code]
<?php
  $num = 5;
  $sql1 = "select number from table where number is > '$num' limit 1";
  $sql2 = "select number from table where number is <= '$num' limit 1";
  $result1 = mysql_query($sql1) or die(mysql_error());
  $result2 = mysql_query($sql2) or die(mysql_error());
 
  if (mysql_num_rows($result1) == 1) {
      $number1 = mysql_fetch_array($result1);
      $diff1 = $number1['number'] - $num;
  }
 
  if (mysql_num_rows($result2) == 1) {
      $number2 = mysql_fetch_array($result2);
      $diff2 = $num - $number2['number'];
  }
 
  if (!$number1 && $number2) {
      $finalnum = $number2;
  } elseif (!$number2 && $number1) {
      $finalnum = $number1;
  } elseif ($number1 && $number2) {
      $finalnum = ($diff1 <= $diff2) ? $number1 : $number2;
  } else {
    // nothing in database...
  }
?>
[/code]

you will have to re-arrange the ternary operator, depending on your preference.  for example, if $num = 5 and $number1 = 3 and $number2 = 7, $finalnum will end up being $number2: 7.  The way i set it up, it chooses the higher number.  If you want it to pick the lower number in case of a tie, your ternary operator should be this:

$finalnum = ($diff2 <= $diff1) ? $number2 : $number1;

anywhoo... i'm sure that mysql probably has some kind of function to automatically do all that for you or something.  I bet fenway or barand would know. You might wanna ask over in the database forums, just to make sure.

actually, i see a flaw in my code already :( stay tuned...

[digitalgod]

lol ok thanks, I'll be waiting :P

[.josh]

(I deleted my previous post for clarity sake)

okay so i just didn't feel right about posting code without testing it, so i went and setup a test table and tried it out.  apparently there's a few more bugs in the above code, so I worked them out.  I tried $num as various numbers including a number higher than the highest number and lower than the lowest number in the table and it gave the correct result. 

Again though, I'm sure there is a much much easier way of doing this...

[code]
<?php
  $num = 5;
  $sql1 = "select column from table where column > '$num' order by column asc limit 1 ";
  $sql2 = "select column from table where column <= '$num' order by column desc limit 1";
  $result1 = mysql_query($sql1) or die(mysql_error());
  $result2 = mysql_query($sql2) or die(mysql_error());
 
  if (mysql_num_rows($result1) == 1) {
      $number1 = mysql_fetch_array($result1);
      $diff1 = $number1['column'] - $num;
  }
 
  if (mysql_num_rows($result2) == 1) {
      $number2 = mysql_fetch_array($result2);
      $diff2 = $num - $number2['column'];
  }
 
  if (!$number1 && $number2) {
      $finalnum = $number2['column'];
  } elseif (!$number2 && $number1) {
      $finalnum = $number1['column'];
  } elseif ($number1 && $number2) {
      $finalnum = ($diff1 <= $diff2) ? $number1['column'] : $number2['column'];
  } else {
      echo "nothing in db..";
  }
?>
[/code]

[Barand]

This will return the table record whose seating capacity is closest to $seats and in the event of a tie, return the table with most seats

[code]
SELECT * FROM club
ORDER BY ABS(tbl_guests - $seats), tbl_guests DESC
LIMIT 1
[/code]

[.josh]

i just knew barand would come in here and wave his magic wand  :-\

[digitalgod]

thanks guys, both of the scripts worked perfectly.

Barand can you please explain to me how does that query work?

does it just order by the lowest difference between tbl_guests and $seats?

[Barand]

[quote author=digitalgod link=topic=111526.msg452761#msg452761 date=1161024658]
thanks guys, both of the scripts worked perfectly.

Barand can you please explain to me how does that query work?

does it just order by the lowest difference between tbl_guests and $seats?

[/quote]
yes, but I used ABS() function so that the sign is ignored

ABS(4-5) is same as ABS(6-5) so both 4 and 6 are equally close to 5

Without the abs() function, -2 would sort before +1 even though there is a greater difference

[digitalgod]

k, thanks again!