[SOLVED] query from database....between(price)

[dannybrazil]

Hello

Im trying to get from my database only the data that is within the range typed by the user

for example :

 

price range :

_________ till ______________

 

lets say the user typed : 10,000 till 30,000

 

now in the data base there is JUST the price of the house.

 

what will be the php query code in this case?

 

i was trying something like :

SELECT * FROM $tbl_name WHERE cidade='$cidade' AND bairro='$bairro' AND category='$category' AND quartos='2'||'3' AND '$preco1'>=preco<='$preco2'  

 

the important part is : AND '$preco1'>=preco<='$preco2'

 

any help ? Thanks a lot

 

Danny

 

p.s.

as well i wanted to know if the query in general is ok

will it show the user only the data base entries between the price range ?

[BLaZuRE]

MySQL has a BETWEEN keyword to accomplish just that, get values from a to b, inclusively.

Try:

SELECT * FROM table WHERE preco BETWEEN '$preco1' AND '$preco2'

 

By the way, I doubt any programming language can do a < b < c.  That's just a special notation used in math, computers are more linear.  You have to separate it out by doing a<b AND b<c.

 

More info:

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between

[dannybrazil]

sounds good , i added it to the rest of the code but it isnt working well

SELECT COUNT(*) as num FROM $tbl_name WHERE cidade='$cidade' AND bairro='$bairro' AND category='$category' AND quartos='2' || '3' AND preco BETWEEN '$preco1' AND '$preco2' 

 

[dannybrazil]

another thing

 

the quartos piece of code... AND quartos='2'||'3'

 

i want it to show all the options in the "quartos" column ( 1 , 2 , 3, 4)

 

 

[dannybrazil]

i did like i was told to

$data = mysql_query("SELECT  * FROM _Form_Nr_2 WHERE preco BETWEEN '$preco1' AND '$preco2' ") or die(mysql_error());

 

 

but it doesnt work....im trying to understand why for hours

 

if im running this command in phpmyadmin it is working

 

any help ?

 

 

[GingerRobot]

Have you tried making sure that your variables are actually set and contain the correct data? It's a pretty common error if things are working OK when you use phpMyAdmin...

 

$sql = "SELECT  * FROM _Form_Nr_2 WHERE preco BETWEEN '$preco1' AND '$preco2' ";
echo "<br/>Query: $sql <br/>";

[dannybrazil]

Ok lets start from scretch

 

i created a table name class

with 3 fields : cidade , bairro , preco (all got varchar(100))

 

i inserted 2 rows with 2 different prices (100000 and 150000)

*btw, is it important if its like that (100,000 and 150,000 - here in brazil the separators are "," and not ".")?

 

now the sql :

$data = mysql_query("SELECT  * FROM class WHERE preco BETWEEN '$preco1' AND '$preco2' ") or die(mysql_error()); 

 

i even added this line:

echo "<br/>Query: $data <br/>";

 

now even in phpmyadmin it doesnt wrok

 

any idea what the hell is going on ?

 

Thanks

[GingerRobot]

i inserted 2 rows with 2 different prices (100000 and 150000)

*btw, is it important if its like that (100,000 and 150,000 - here in brazil the separators are "," and not ".")?

 

Yes it is important. You shouldn't be storing values in a varchar field. Use an INT field. You can format the values as you like when you output the data( e.g. number_format)

 

If you store numerical values in a varchar field, any ordering on them will be done lexicographically. It won't be a "natural" order (that is, the way in which a human might order them) and so you're going to get odd results.

[dannybrazil]

Thanks for the REALY helpful info

 

i will update you as soon as i will manage to apply all the new info

 

Danny