integer to integer range search problems - please help

garethj

Hello,

i'm trying to perform a search on 'price low range' to 'price high range', everthing appears to work until i use the less than or equal to '<=' or '>=' parameters. if i use just '=' the results are returned correctly.

any thoughts?

here's my code:

$result2 = mysql_query ("SELECT * FROM table
WHERE avail='1' AND pricef2 >= $lowrange
AND pricet2 <= $highrange order by timestamp desc ");

the variable values ($highrange, $lowrange) are sent from the previous page (html form)

in case the problem is w/ the mysql columns - here's are my column name and types etc.,

thanks a lot.

pricef2 int(10) default NULL,
pricet2 int(10) default NULL,

integers w/ a max length of '10' and default values of 'NULL'

leatherback

Hi,

I am not sure:

Two options <?>: Are the values parsed from the last page string or int values? (Just it looking like a number doens't always mean it is a number (I think this is true in PhP too, else I am confused with other languages), maybe you have to convert the variable type)
I get sometimes problems, using GT and LT instead of >= helped me out a few times.

garethj

thanks for your reply,

I have them as 'type=text', is there a number type like 'type=integer'?

here's the html form code

<td valign=top><font size="-1">Price from:
$</font><input type=text name="lowrange" size=4 maxlength=5 value="">
</td>

<td valign=top><font size="-1">Price to:
$</font><input type=text name="highrange" size=4 maxlength=5 value="">
</td>

leatherback

Hi,

There is not a submit type integer (That I know of). But you can change the variable type. I am not sure of the exact command, but browse the PhPbuilder documentation site.

(See for instance: http://www.phpbuilder.com/manual/function.settype.php)

tomhath

Mysql doesn't know anything about the php variable types.

Put the query into a variable and echo it out before you send it to mysql_query.

My guess is that there's a logic problem with your comparisons of pricef2, pricet2, $lowrange, and $highrange. What are some examples of the data values for all of these?

garethj

hi,

the data values are prices for apartment rentals - could be anything like '1000' or '850' etc...

I would like the user to be able to enter a range - retreive every row where the price values are between a certain range.

so they could enter 850 to 1000 (where $850 would be the lowrange and $1000 would be the highrange) the search would then display all results in between those figures - like every instance of $900 or $950 etc.,

here are the column types that i'm using in Mysql

pricef2 int(10) default NULL,
pricet2 int(10) default NULL,

here's the html form code that allows the user to enter a value:

Price from: $<input type=text name="lowrange" size=4 maxlength=5 value="">

Price to: $<input type=text name="highrange" size=4 maxlength=5 value="">

and finally, here's the php code that should select these ranges from the mysql table:

$result2 = mysql_query ("SELECT * FROM table
WHERE avail='1' && pricef2 >= $lowrange
&& pricet2 <= $highrange order by timestamp desc ");

thanks for your help..

tomhath

I still suspect there is a logic problem.

Suppose pricef2 (low range) in database is $800 and pricet2 (high range) is $1000.

Now suppose they request something from $850 to $950.

Your query won't find it. Because you want

pricef2 >= $lowrange -- $800 >= $850? Nope, fails.
&& pricet2 <= $highrange -- $1000 <= $950? Nope, also fails.

Or am I misunderstanding what is in those fields/variables? In order to find the $800-1000 row, they would have to enter a range like $700 to $1100. Is that what you want?

garethj

huh, but isn't $850 <= $1000 ?

and $1000 <= $1000?

I need them to find all instances of '$850' and also everything over '$850'

and then everything under or equal to '$1000'.

thanks,

garethj

ok, your right, i see what you mean w/ the logic - got confused,

thanks.