Jump to content

matching a user's input with db *SOLVED*


digitalgod

Recommended Posts

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?

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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...

Link to comment
Share on other sites

(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]
Link to comment
Share on other sites

[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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.