Problems filtering number in database

kumschick

+----+---------+
| id | version |
+----+---------+
|  1 | 100     |
|  2 | 100     |
|  3 | 100     |
|  4 | 100     |
|  5 | 100     |
|  6 | 100     |
|  7 | 105     |
|  8 | 110     |
|  9 | 110     |
|  9 | 110     |
| 10 | 111     |
+----+---------+

How can I search for a number smaller then the one I typed in?
If I type 102, the number that sould come out is 100.
If I type 110, the number that sould come out is also 110, because
the number is available, if not, then the number just under it sould come out!

How can I do that by using MySQL?

planetsim

This should be in the Database Forum however Ill take a crack at it

SELECT version FROM mytablename WHERE version = '110' OR version < '110' LIMIT 1

This is quite a simple query you will need to change it to make it usable. However it checks first if the version exists if so we use that if not we check to see if there is a result that is less then 110 and return only 1 row.

kburger

That's not necessarily going to work. It's just going to return the first value it finds that's less than or equal to 110, but not necessarily the largest value that's less than or equal to 110.

First of all, I have to assume that the 'version' field is of type integer, or some sort of numerical datatype, otherwise if it's character, like varchar you're SOL.

Here's a better query. Others may come up with a still better query.

SELECT version FROM table WHERE version <= '$searchnumber' ORDER BY version DESC LIMIT 1

kumschick

Oops, I'm sorry, I did not copy/paste the right database! there is one importent part missing!

Example:
if the user checks for updates, the only information that will be send is the version number! f.ex.: 1.0.3

First the version will be filtered like this 1.0.3 -> 103

Now the only update that will be shown is the version 105.
Once this update is installed, the user may install the version 111.

+----+---------+--------------+
| id | version | version_need |
+----+---------+--------------+
|  1 | 100     | 100          |
|  2 | 101     | 100          |
|  3 | 102     | 100          |
|  4 | 103     | 100          |
|  5 | 104     | 100          |
|  6 | 105     | 100          |
|  7 | 110     | 105          |
|  8 | 111     | 110          |
|  9 | 112     | 111          |
| 10 | 120     | 112          |
+----+---------+--------------+

thx

kburger

I guess maybe I'm not understanding what you're trying to do. If you're trying to find the 'version' that's less than or equal to your 'searchnumber', then trying to find the 'version_need' that corresponds to that 'version', then this should do it:

SELECT version_need FROM table WHERE version <= '$searchnumber' ORDER BY version DESC LIMIT 1

Weedpacket

Or instead of sorting the results and then limiting, just select the maximum. Obviously, like the others, this is not guaranteed to produce a result if $searchnumber is less than every version number in the table.