how to find B when i write A-C

[localhobo]

Hi,

 

sorry if there already is a topic regarding my question.. I probably didn't find the right words to describe it properly.. would appreciate a link if anybody knows

 

I'm making a search form for an archive and my problem is that the labeling says e.g. "1400-3500 A-C".. Now i don't want to make thousands of entrys for 1 file.. How do i find that one file while searching for a number in between, like "1420" or "B"?

 

Thanks in advance!

 

Greetings localhobo

Edited October 13, 2014 by localhobo

[Barand]

Don't build field values out of multiple values. There are four separate values there so use four fields

 

minA | maxA | minB | maxB

 

Then your search is

 

...WHERE searchval BETWEEN minA AND minB

[localhobo]

Thanks alot Barand

[localhobo]

Don't build field values out of multiple values. There are four separate values there so use four fields

 

minA | maxA | minB | maxB

 

Then your search is

 

...WHERE searchval BETWEEN minA AND minB

 

I just don't get it to work properly.. what am i doing wrong here ?

my table contains minB/maxB as VARCHAR and minZ/maxZ as INT..

there are some entries where i know the number and some where i know the letter.. so e.g. if I've got minB = A  /  maxB = F  and leave the rest blank, it fills minZ/maxZ with zeros

 

my query looks like this:

 

SELECT * FROM archive WHERE '$searchfield' BETWEEN minB AND maxB OR ($searchfield BETWEEN minZ AND maxZ)

 

the output for $searchfield = b looks like this:

 

id year zone error art    signature                minZ maxZ minB maxB month monthfrom monthto  

1 2009 AH             ktr    01519-01699 a-f    1519 1699  a        f         0           0                 0

2 2009 AH             kfz                                   0       0                           0           0                 0  

4 2007 AH             roto  d-j                           0       0       d        j         0           1                 3

5 2001 AH    leak  plo                                   0       0                           0           0                 0  

 

it does show me the right dataset for minB/maxB but also every single one where minZ / maxZ is 0

 

I would really appreciate any help..

Edited October 14, 2014 by localhobo

[localhobo]

sorry, the query of the example looks like this: SELECT * FROM archive WHERE 'b' BETWEEN minB AND maxB OR ('b' BETWEEN minZ AND maxZ)

[Barand]

Try making the the default values  NULL instead of zero

mysql> select * from archive;
+----+------+------+------+------+
| id | minZ | maxZ | minB | maxB |
+----+------+------+------+------+
|  1 | 1519 | 1699 | a    | f    |
|  2 | NULL | NULL | NULL | NULL |
|  3 | NULL | NULL | d    | j    |
|  4 | 1519 | 1699 | g    | z    |
|  5 | 1700 | 1850 | g    | z    |
|  6 | 1700 | 1850 | NULL | NULL |
+----+------+------+------+------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM archive
    -> WHERE 'b' BETWEEN minB AND maxB
    ->     OR ('b' BETWEEN minZ AND maxZ);
+----+------+------+------+------+
| id | minZ | maxZ | minB | maxB |
+----+------+------+------+------+
|  1 | 1519 | 1699 | a    | f    |
+----+------+------+------+------+
1 row in set, 5 warnings (0.00 sec)

[localhobo]

Thank you so much!