MySQL query string (WHERE OR) problem...Help!
Posted: Tue Mar 04, 2003 9:25 am
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)
but I started to get problems once I tried to expand the query to the following:
Second example: (This gives me the problem)
First example: (This works for what I needed origonally)
Code: Select all
<?phpCode: 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";
?>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";
?>