Jump to content

Archived

This topic is now archived and is closed to further replies.

digitalgod

matching a user's input with db *SOLVED*

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?

Share this post


Link to post
Share on other sites
My first question needs to be why? This is possible (everything is), but its not going to be an easy fix.

Share this post


Link to post
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

Share this post


Link to post
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...

Share this post


Link to post
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]

Share this post


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

Share this post


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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

×

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.