comparing integers in mySQL query doesn't work

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mis2o
Forum Newbie
Posts: 10
Joined: Sat Dec 13, 2003 2:04 pm

comparing integers in mySQL query doesn't work

Post by mis2o »

hi, i have a problem comparing some integer values in query. i get no error but php "mysql_num_rows" always returns zero despite it shouldn't.
i need to do a search in table, filtering things like region, type, price etc. Filtrering things which have "=" in query works, but using "<" and similar fails to work.
here is a part of HTML form i use to set the filters
(the part that couses problems in the query):

Code: Select all

<select name="constarea" size="1">
		<option value="0"> <?php if ($_SESSION&#1111;'lang'] =="svk") &#123; echo'&nbsp;zobraz všetky' ; &#125; if ($_SESSION&#1111;'lang'] == "eng") &#123; echo'&nbsp;&nbsp;&nbsp;&nbsp;show all'; &#125; ?>
		<option value="30"> > 30 m2
		<option value="50"> > 50 m2
		<option value="100"> > 100 m2
		<option value="200"> > 200 m2
		<option value="500"> > 500 m2
		</select>
		</td>
		</tr>
		<tr>
		<td align="left">
		<span class="formstyle">
		<?php 
		if ($_SESSION&#1111;'lang'] == "svk") &#123; echo'cena:'; &#125; 
		if ($_SESSION&#1111;'lang'] == "eng") &#123; echo'price:'; &#125;
		?>
		</span>
		</td>
		<td align="right">
		<select name="price" size="1">
		<option value="0"> <?php if ($_SESSION&#1111;'lang'] == "svk") &#123; echo'&nbsp;zobraz všetky'; &#125; if ($_SESSION&#1111;'lang'] == "eng") &#123; echo'show all'; &#125; ?>
		<option value="500"> < 500 &#8364
		<option value="1000"> < 1.000 &#8364
		<option value="10000"> < 10.000 &#8364
		<option value="100000"> < 100.000 &#8364
		<option value="1000000"> < 1.000.000 &#8364
		<option value="1000001"> > 1.000.000 &#8364
		</select>
the values are stored in variables and then this query is called:

Code: Select all

$query1 = "SELECT type,region,city,price,const_area,bedrooms,bathrooms,sm_pic1,short_desc_slovak,short_desc_english,sh  ort_desc_spanish FROM buy WHERE type = $type AND region = $region AND (price < $price) AND (const_area > $constarea)";
when i select for example "100.000" as price, query returns nothing despite there are things in db with price lower than 100.000.
(the same happens with "const_area")
in "buy" table structure "price" and "const_area" are of type BIGINT.

i can't find out what is wrong, please help me if you can.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

if region is of type varchar you should have region='$region'
mis2o
Forum Newbie
Posts: 10
Joined: Sat Dec 13, 2003 2:04 pm

Post by mis2o »

type and region are of type TINYINT(as the site is supposed to work in multiply languages and each integer stands for a different type, region). this doesn't cause the trouble, i think "<", ">" comparison doesn't work for some reason.
penguinboy
Forum Contributor
Posts: 171
Joined: Thu Nov 07, 2002 11:25 am

Post by penguinboy »

Test this first.

Code: Select all

SELECT `price` FROM `buy` WHERE `price`<'100.000';
If that works, slowly build your query, until you find your error.

Also to prevent headaches;
it's best to use ' around values and ` around field & table names.
Post Reply