Jump to content


Photo

matching a user's input with db *SOLVED*


  • Please log in to reply
11 replies to this topic

#1 digitalgod

digitalgod
  • Members
  • PipPipPip
  • Advanced Member
  • 374 posts

Posted 15 October 2006 - 01:37 AM

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?



#2 digitalgod

digitalgod
  • Members
  • PipPipPip
  • Advanced Member
  • 374 posts

Posted 15 October 2006 - 06:21 AM

anyone?

#3 trq

trq
  • Staff Alumni
  • Advanced Member
  • 31,041 posts

Posted 15 October 2006 - 06:25 AM

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

#4 digitalgod

digitalgod
  • Members
  • PipPipPip
  • Advanced Member
  • 374 posts

Posted 15 October 2006 - 06:37 AM

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


#5 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 15 October 2006 - 07:10 AM

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

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

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


Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#6 digitalgod

digitalgod
  • Members
  • PipPipPip
  • Advanced Member
  • 374 posts

Posted 15 October 2006 - 07:14 AM

lol ok thanks, I'll be waiting :P

#7 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 15 October 2006 - 08:05 AM

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

<?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..";
   }
?>

Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 15 October 2006 - 08:32 AM

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

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#9 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 15 October 2006 - 08:40 AM

i just knew barand would come in here and wave his magic wand  :-\
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#10 digitalgod

digitalgod
  • Members
  • PipPipPip
  • Advanced Member
  • 374 posts

Posted 16 October 2006 - 06:50 PM

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?



#11 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 16 October 2006 - 07:13 PM

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?

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#12 digitalgod

digitalgod
  • Members
  • PipPipPip
  • Advanced Member
  • 374 posts

Posted 16 October 2006 - 07:13 PM

k, thanks again!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users