Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
papa
Forum Regular
Posts: 958 Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm
Post
by papa » Wed Aug 19, 2009 9:05 am
Hello,
Say I have a table with
id | points
1 | 1999
2 | 2000
3 | 3000
4 | 4000
The user submits a point value. Say for example he/she submits 250, how do I match that with my id 1. Or maybe the user submits 3700, then I want id 3 to be fetched and not 4.
How do I go about this?
thanks
edit:
What I got so far works, but if you submit 2800 for example. It fetches id 3 and not 2.
Code: Select all
$sql = "SELECT ABS(points - $points) as distance, max_tot, lords, heroes, core, special, rare
FROM a_army_size ORDER BY distance LIMIT 1";
lord_webby
Forum Commoner
Posts: 44 Joined: Wed Aug 19, 2009 9:01 am
Post
by lord_webby » Wed Aug 19, 2009 9:29 am
How about:
Id use a php switch and forget the sql comparison. Something like:
Code: Select all
<?php
$points = 370;
switch ($points) {
case ($points < 2000):
$i =1;
break;
case (1999 < $points < 3000):
$i =2;
break;
case (2999 < $points < 4000):
$i =3;
break;
Default:
$i =4;
break;
}
?>
Then:
Code: Select all
$sql = "SELECT * FROM a_army_size WHERE ID = '$i' ORDER BY distance LIMIT 1";
Does that help?
papa
Forum Regular
Posts: 958 Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm
Post
by papa » Wed Aug 19, 2009 9:31 am
Thanks for your answer, though I'd prefer using mySQL for this as I'd need to change in several places if I did an update in the mysql table for example.
jackpf
DevNet Resident
Posts: 2119 Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK
Post
by jackpf » Wed Aug 19, 2009 9:36 am
I'm not entirely sure how to do this, but you could subtract the value from the field, and then select whichever one is closest to 0.
So, say the value is 2200, 2000 will become -200 which is closest to 0.
Although, you'll have to make some sort of case for two values equidistant from 0. I suppose you could combine it with MAX() to select the highest value, closest to 0...so it'll round up.
This may not be the best way of doing it...or even possible, but that's all I can think of.
pickle
Briney Mod
Posts: 6445 Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:
Post
by pickle » Wed Aug 19, 2009 9:50 am
So you want to round up if the value is smaller than the smallest value in the DB, and round down otherwise?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Wed Aug 19, 2009 11:00 am
Like this:
[sql]SELECT *FROM a_army_sizeWHERE points = COALESCE ( (SELECT max(points) FROM a_army_size WHERE points < 2700), (SELECT min(points) FROM a_army_size) )LIMIT 1[/sql]
?
There are 10 types of people in this world, those who understand binary and those who don't
papa
Forum Regular
Posts: 958 Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm
Post
by papa » Thu Aug 20, 2009 2:40 am
pickle wrote: So you want to round up if the value is smaller than the smallest value in the DB, and round down otherwise?
Jepp correct.
@Vlad: Unfortunately I get an errror: "FUNCTION army_builder.COALESCE does not exist"
I'm running Mysql 5.
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Thu Aug 20, 2009 2:49 am
There are 10 types of people in this world, those who understand binary and those who don't
lord_webby
Forum Commoner
Posts: 44 Joined: Wed Aug 19, 2009 9:01 am
Post
by lord_webby » Thu Aug 20, 2009 2:53 am
Maybe:
Code: Select all
SELECT * FROM army_builder WHERE points < '$points' AND (('$points' - points) < 1000) ORDER BY ID DESC LIMIT 1
But thinking on it, it won't work for any values under 999.
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Thu Aug 20, 2009 3:08 am
papa wrote: @Vlad: Unfortunately I get an error: "FUNCTION army_builder.COALESCE does not exist"
I'm running Mysql 5.
Blah!!!
Remove the new line before the opening bracket after COALESCE like this:
[sql]SELECT *FROM a_army_sizeWHERE points = COALESCE( (SELECT max(points) FROM a_army_size WHERE points < 2700), (SELECT min(points) FROM a_army_size) )LIMIT 1[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
papa
Forum Regular
Posts: 958 Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm
Post
by papa » Thu Aug 20, 2009 3:34 am
Thanks Vlad!
It works almost perfectly.
id points max_tot lords heroes core special rare
1 1999 3 0 3 2 3 1
2 2000 4 1 4 3 4 2
3 3000 6 2 6 4 5 3
4 4000 8 3 8 5 6
If I put in 2000 points i still display records from id 1. If points is 2001 it works. Same for 3000 and 4000.
Code: Select all
$sql = "SELECT
*
FROM
a_army_size
WHERE
points = COALESCE(
(SELECT max(points) FROM a_army_size WHERE points < $points),
(SELECT min(points) FROM a_army_size)
)
LIMIT 1";
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Thu Aug 20, 2009 3:35 am
So ... what do you suggest
C'mon - that's too easy!
There are 10 types of people in this world, those who understand binary and those who don't
papa
Forum Regular
Posts: 958 Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm
Post
by papa » Thu Aug 20, 2009 3:46 am
Haha sorry, got lazy
<=
Thanks again!