SQL Query to workout values between two prices

[The_FedEx_Guy]

Hi all,
I'm making an application to create a search page that has a drop down box with prices such as:

£100-£1000
£2000-£4000
etc..

How does this work?

[Newsgroup_User]

The_FedEx_Guy wrote:
> Hi all,
> I'm making an application to create a search page that has a drop down box with prices such as:
>
> ?100-?1000
> ?2000-?4000
> etc..

SELECT * FROM products
WHERE price >= 100
AND price <= 1000

Use variables to change the values according to what is selected from
the drop-down.

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

[The_FedEx_Guy]

Hi how about say if I wanted to select the first part of a postcode

example: WS9 from the database, that holds WS9 9PP?

[Newsgroup_User]

The_FedEx_Guy wrote:
> Hi how about say if I wanted to select the first part of a postcode
>
> example: WS9 from the database, that holds WS9 9PP?

You need to say which database and server-side language you're working
with. In MySQL, it would be:

SELECT * FROM addresses
WHERE post_code LIKE 'WS9%'

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

[The_FedEx_Guy]

Thanks, all is working well.

[The_FedEx_Guy]

The SQL statement I was using was working fine lastnite but has now stopped working.

I cannot get any values to be returned once I put the search thru.

Can anyone help? I've tried to post this as a new message but the site kept telling me that this page does not exsist.

SELECT PropID, Address, Area, Postcode,PropType, Description, NoBeds, Garden, Lrg_Image, Image2, Cost
FROM property
WHERE Area = '$Area' AND PropType = '$PropType' AND NoBeds = '$beds' AND Garden = '$garden' AND Accepted = 'Yes' AND Postcode LIKE '%$postcode%' AND Cost >= '$cost'
ORDER BY PropID DESC

[Newsgroup_User]

The_FedEx_Guy wrote:
> The SQL statement I was using was working fine lastnite but has now stopped
> working.

If it was working fine last night, it cannot simply stop working unless
you have made other changes to the code. Your SQL query uses a lot of
conditions in the WHERE clause. *All* conditions must be met for the
query to return any results. It's perfectly possible that your query is
working, but that there are no records that meet all the criteria.

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

[The_FedEx_Guy]

Could it be a problem with the way my tabble is structured?
As I have a cost field in the database with the attributes Currency (5,2) But say I put in 6500.00 it only shows 65.00

That could possible be the problem. But I need to show house prices like £350.000

[Newsgroup_User]

On 23 Oct 2006 in macromedia.dreamweaver.appdev, The_FedEx_Guy wrote:

> As I have a cost field in the database with the attributes Currency
> (5,2) But say I put in 6500.00 it only shows 65.00

It's trying to tell you something. Don't do that!

What kind of database? In any case, use either an integer or decimal
data type; don't muck around with any other types. I'd prefer integer;
you just have to remember that your items are priced in 1/100 of a
currency unit (pennies). Or if your prices are all even units of
currency, integer is the obvious choice. Then do any math, and format
the output, using either SQL or your page programming language.

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/email.php

[The_FedEx_Guy]

Hi Joe,
Its actually set to decimal (5,2) shall I get rid of the attribute?

[Newsgroup_User]

On 23 Oct 2006 in macromedia.dreamweaver.appdev, The_FedEx_Guy wrote:

> Its actually set to decimal (5,2) shall I get rid of the attribute?

What's the database?

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/email.php

[The_FedEx_Guy]

Its a Property search dbasesearches a company's dbase to see what they have available.

The search is meant to be mainly using the postcode.

I made it in MySQL

[Newsgroup_User]

On 23 Oct 2006 in macromedia.dreamweaver.appdev, The_FedEx_Guy wrote:

> Its a Property search dbasesearches a company's dbase to see what
> they have available.
>
> The search is meant to be mainly using the postcode.
>
> I made it in MySQL

Here's what MySQL has to say about the decimal data type:

http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html#id3229010

Without doing any experimentation, it looks like you would need to
include all the digits in a number you're entering to a decimal field.
So to enter [currency sign]6,500[.00 implied] into a decimal(10,2)
field, you'd need to enter 650000.

Are you actually carrying decimal information in this field? If not,
just use an integer.

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/email.php

[Newsgroup_User]

Joe Makowiec wrote:
> Without doing any experimentation, it looks like you would need to
> include all the digits in a number you're entering to a decimal field.
> So to enter [currency sign]6,500[.00 implied] into a decimal(10,2)
> field, you'd need to enter 650000.

Not correct.

However, DECIMAL is generally regarded as being pretty useless. Its
purpose is to prevent the type of rounding errors that you frequently
get with FLOAT or DOUBLE. The problem is that is stores numbers as a
string, which means you cannot perform any calculations with a DECIMAL
data type.

When dealing with currencies in a database, it's best, as Joe mentioned
earlier, to use INT. If you need pounds & pence (dollars/euros & cents),
store everything as pence or cents. It sounds as though the database is
being used for real estate, so pounds, dollars, or euros on their own
are probably more appropriate.

Bottom line: Don't use DECIMAL in MySQL. It's close to useless.

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

[Newsgroup_User]

Joe Makowiec wrote:
> Without doing any experimentation, it looks like you would need to
> include all the digits in a number you're entering to a decimal field.
> So to enter [currency sign]6,500[.00 implied] into a decimal(10,2)
> field, you'd need to enter 650000.

Not correct.

However, DECIMAL is generally regarded as being pretty useless. Its
purpose is to prevent the type of rounding errors that you frequently
get with FLOAT or DOUBLE. The problem is that is stores numbers as a
string, which means you cannot perform any calculations with a DECIMAL
data type.

When dealing with currencies in a database, it's best, as Joe mentioned
earlier, to use INT. If you need pounds & pence (dollars/euros & cents),
store everything as pence or cents. It sounds as though the database is
being used for real estate, so pounds, dollars, or euros on their own
are probably more appropriate.

Bottom line: Don't use DECIMAL in MySQL. It's close to useless.

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

[The_FedEx_Guy]

The SQL statement still returns nothing, can I use another operator instead of AND?