MySQL query string (WHERE OR) problem...Help!

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
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

MySQL query string (WHERE OR) problem...Help!

Post by mikebr »

I am working on my first database using php and mysql and have come upon a stumbling block. I am trying to run a query on the database but am not getting the expected result. The query looks for data from two tables in the same db. The "sales_currency" column holds the currency of a price entered by the user and the "sales_price" column holds the price. I am trying to return price entries from the sales_price column that match (are between) the values selected from the search, but if the user selects say "USD" as a currency then I want to get the "USD" (sales_currency tells me this) entries from the sales_price columns and convert them using an exchange rate, do the same for euros "EUR" and "GBP" then build the query string, but it gives odd results, like maybe an entry would be returned for values of between 5000 and 10000 but then nothing would be returned for values of between 5000 and 20000, also some of the values are not in the range. The basic query works (First example below) as I was using it so the join for the clients tables and sales table is fine, the problem started when I tried to expand on this (Second example below) to get the various values for the converted price rates, would be thankfull if anyone can show me where I am going wrong.

First example: (This works for what I needed origonally)

Code: Select all

<?php

Code: Select all

query = "SELECT fs.*, c.email, c.phone, c.fax, c.website FROM forsale fs LEFT JOIN clients c ON fs.client_ID = c.client_ID WHERE fs.sales_country='Spain' AND fs.sales_price BETWEEN '4588' AND '27529380' ORDER BY 'sales_town' LIMIT 0, 5";
?>
but I started to get problems once I tried to expand the query to the following:

Second example: (This gives me the problem)

Code: Select all

<?php
query = "SELECT fs.*, c.email, c.phone, c.fax, c.website FROM forsale fs LEFT JOIN clients c ON fs.client_ID = c.client_ID WHERE fs.sales_country='Spain'  AND (fs.sales_currency = 'EUR' AND fs.sales_price BETWEEN '4588' AND '27529380') OR (fs.sales_currency = 'GBP' AND fs.sales_price BETWEEN '3161' AND '18963390') OR (fs.sales_currency = 'USD' AND fs.sales_price BETWEEN '5000' AND '30000000')   ORDER BY 'sales_town' LIMIT 0, 5";
?>
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

Perhaps you could try adding another layer of parentheses and removing the quotes around the numerical values:

Code: Select all

query = "SELECT fs.*, c.email, c.phone, c.fax, c.website FROM forsale fs LEFT JOIN clients c ON fs.client_ID = c.client_ID WHERE fs.sales_country='Spain'  AND ((fs.sales_currency = 'EUR' AND fs.sales_price BETWEEN 4588 AND 27529380) OR (fs.sales_currency = 'GBP' AND fs.sales_price BETWEEN 3161 AND 18963390) OR (fs.sales_currency = 'USD' AND fs.sales_price BETWEEN 5000 AND 30000000))   ORDER BY 'sales_town' LIMIT 0, 5";
You might get curious results if you try to treat numbers as strings, because then it will compare the values as strings and not numbers. Note that:
'1000' < '200' < '30'
because '1' < '2' and in string comparisons that's as far as you need to compare, regardless of what follows in the strings.
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

Cheers Rob, this seems to have done the job. I should have realised the string and numbers mistake, it's one of those things that get written without any thought, bit like = and ==.

Thank's for the solution on the query.
Post Reply