Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    13

    Unanswered: between query not working

    Hi everyone,

    I am using codeigniter 2.03 with wamp ( php 5.3.5, apache 2.2.17, mysql 5.1.54 ). I
    am having problems with some queries. I have made the
    following table:

    CREATE TABLE `zipcodes` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `zip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `averageprice` double DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8
    COLLATE=utf8_unicode_ci


    when I plug 'SELECT * FROM zipcodes WHERE averageprice BETWEEN 323606.93719128 AND 110864.97021613' into phpmyadmin I get :

    MySQL returned an empty result set (i.e. zero rows).

    There are definitely values between 323606.93719128 AND 110864.97021613 for averageprice column.

    how do I get the right answer

    Thanks in advance,

    Bill

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kc1 View Post
    There are definitely values between 323606.93719128 AND 110864.97021613 for averageprice column.
    how do you know?

    and what does a SHOW CREATE TABLE command produce?


    p.s. one of your values contains the digits "937" ... coincidence? i don't think so
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2008
    Posts
    17
    Quote Originally Posted by kc1 View Post
    Hi everyone,

    I am using codeigniter 2.03 with wamp ( php 5.3.5, apache 2.2.17, mysql 5.1.54 ). I
    am having problems with some queries. I have made the
    following table:

    CREATE TABLE `zipcodes` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `zip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `averageprice` double DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8
    COLLATE=utf8_unicode_ci


    when I plug 'SELECT * FROM zipcodes WHERE averageprice BETWEEN 323606.93719128 AND 110864.97021613' into phpmyadmin I get :

    MySQL returned an empty result set (i.e. zero rows).

    There are definitely values between 323606.93719128 AND 110864.97021613 for averageprice column.

    how do I get the right answer

    Thanks in advance,

    Bill
    323606.93719128 is larger than 110864.97021613
    I think BETWEEN needs to have the minimum value first

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TonyF123 View Post
    323606.93719128 is larger than 110864.97021613
    I think BETWEEN needs to have the minimum value first
    omg i can't believe i missed that

    :blush:

    well spotted, tony
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2011
    Posts
    13
    Thanks for answering guys. You were right. It works now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •