6 Replies - 692 Views - Last Post: 16 February 2009 - 07:49 AM Rate Topic: -----

#1 chili5  Icon User is offline

  • D.I.C Lover

Reputation: 20
  • View blog
  • Posts: 1,144
  • Joined: 28-December 07

Query returning empty

Posted 15 February 2009 - 11:47 AM

I'm running this query:

SELECT * FROM coffees WHERE price = 7.99


on the attached table, and it keeps returning an empty rowset. I am expecting it to return the rowset that looks like:

Colombian 101 7.99 0 0

I've tried running my query on the command line, in a Java, and in a PHP program and all three returned me an empty rowset.

I know that I have a connection to the appropriate database and the server. Any ideas as to why this isn't returning what I want it too?

Attached image(s)

  • Attached Image

This post has been edited by chili5: 15 February 2009 - 11:48 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Query returning empty

#2 Hary  Icon User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

Re: Query returning empty

Posted 15 February 2009 - 12:38 PM

What is the data format of the 'price' column? If its a Varchar, you need to use "7.99"
Was This Post Helpful? 0
  • +
  • -

#3 chili5  Icon User is offline

  • D.I.C Lover

Reputation: 20
  • View blog
  • Posts: 1,144
  • Joined: 28-December 07

Re: Query returning empty

Posted 15 February 2009 - 12:46 PM

It is a "double".
Was This Post Helpful? 0
  • +
  • -

#4 Hary  Icon User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

Re: Query returning empty

Posted 15 February 2009 - 01:20 PM

Quote

MySQL treats DOUBLE as a synonym for DOUBLE PRECISION (a non-standard extension)


Double is not a native MySQL type, and I guess you should use " as well.

Source
Was This Post Helpful? 0
  • +
  • -

#5 chili5  Icon User is offline

  • D.I.C Lover

Reputation: 20
  • View blog
  • Posts: 1,144
  • Joined: 28-December 07

Re: Query returning empty

Posted 15 February 2009 - 01:52 PM

I just checked phpmyadmin and the column is of type "float" not "double".

idk I'm so confused. :(
Was This Post Helpful? 0
  • +
  • -

#6 Hary  Icon User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

Re: Query returning empty

Posted 16 February 2009 - 12:50 AM

CREATE TABLE `dic`.`coffees` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` INT NOT NULL ,
`price` FLOAT NOT NULL
) 

insert into coffees (name, price) values ("DE", "2.55"),("Cheap", "1.00"),("Expensive", "9.99")

SELECT * FROM Coffees where price = 2.55
> Zero rows returned


SELECT * FROM Coffees where price = "2.55"
> Zero rows returned

SELECT * FROM Coffees WHERE price <3
> 2 rows


I Guess this is because of the floating point storage. Because the floating point is stored as an approximation, it does not exacly match 2.55.

You could better use Decimal(2,2) (##.##)
Was This Post Helpful? 1
  • +
  • -

#7 chili5  Icon User is offline

  • D.I.C Lover

Reputation: 20
  • View blog
  • Posts: 1,144
  • Joined: 28-December 07

Re: Query returning empty

Posted 16 February 2009 - 07:49 AM

Thanks
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1