Professional Web Applications Themes

Odd Behaviour with select SQL_CALC_FOUND_ROWS - MySQL

I've got a web site in beta testing right now. I've come across a bug that is turning up some strange results. I'm doing a query "select SQL_CALC_FOUND_ROWS ml,City,ListPrice,TotalSQFT,NumberofBedrooms,Total Bathrooms,NumberofPhotos from residential where City like "McMinnville" OR City like "Yamhill" and ListPrice >= 1000 and ListPrice <= 500000 and NumberofBedrooms >= 1 AND TotalBathrooms >= 1 order by ListPrice desc LIMIT 10,10" But the return list is spitting back results that are out size of the range specified by listprice. You can view the qery and result here: [url]http://www.oregon-properties.net/cgi-bin/homesearch3.pl?city=McMinnville&city=Yamhill&low=1 000&high=500000&beds=1&baths=1&limit=10&offset=10& pre=1&sort=desc[/url] Any ideas where I may be going wrong?...

  1. #1

    Default Odd Behaviour with select SQL_CALC_FOUND_ROWS

    I've got a web site in beta testing right now. I've come across a bug
    that is turning up some strange results.

    I'm doing a query "select SQL_CALC_FOUND_ROWS
    ml,City,ListPrice,TotalSQFT,NumberofBedrooms,Total Bathrooms,NumberofPhotos
    from residential where City like "McMinnville" OR City like "Yamhill"
    and ListPrice >= 1000 and ListPrice <= 500000 and NumberofBedrooms >= 1
    AND TotalBathrooms >= 1 order by ListPrice desc LIMIT 10,10"

    But the return list is spitting back results that are out size of the
    range specified by listprice.

    You can view the qery and result here:

    [url]http://www.oregon-properties.net/cgi-bin/homesearch3.pl?city=McMinnville&city=Yamhill&low=1 000&high=500000&beds=1&baths=1&limit=10&offset=10& pre=1&sort=desc[/url]

    Any ideas where I may be going wrong?

    salvador Guest

  2. #2

    Default Re: Odd Behaviour with select SQL_CALC_FOUND_ROWS

    salvador wrote:
    > I've got a web site in beta testing right now. I've come across a bug
    > that is turning up some strange results.
    >
    > I'm doing a query "select SQL_CALC_FOUND_ROWS
    > ml,City,ListPrice,TotalSQFT,NumberofBedrooms,Total Bathrooms,NumberofPhotos
    > from residential where City like "McMinnville" OR City like "Yamhill"
    > and ListPrice >= 1000 and ListPrice <= 500000 and NumberofBedrooms >= 1
    > AND TotalBathrooms >= 1 order by ListPrice desc LIMIT 10,10"
    >
    > But the return list is spitting back results that are out size of the
    > range specified by listprice.

    You have a precedence issue. The ANDs are evaluated before ORs. Your query
    is effectively this:

    select SQL_CALC_FOUND_ROWS
    ml,City,ListPrice,TotalSQFT,NumberofBedrooms,Total Bathrooms,NumberofPhotos
    from residential where City like "McMinnville" OR (City like "Yamhill" and
    ListPrice >= 1000 and ListPrice <= 500000 and NumberofBedrooms >= 1 AND
    TotalBathrooms >= 1) order by ListPrice desc LIMIT 10,10"

    So if the City is McMinnville it will match regardless.

    When you probably want:

    select SQL_CALC_FOUND_ROWS
    ml,City,ListPrice,TotalSQFT,NumberofBedrooms,Total Bathrooms,NumberofPhotos
    from residential where (City like "McMinnville" OR City like "Yamhill") and
    ListPrice >= 1000 and ListPrice <= 500000 and NumberofBedrooms >= 1 AND
    TotalBathrooms >= 1) order by ListPrice desc LIMIT 10,10"

    Another issue is you are using like with no wildcard, you may as well use =
    instead.


    --
    Brian Wakem
    Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]
    Brian Wakem Guest

  3. #3

    Default Re: Odd Behaviour with select SQL_CALC_FOUND_ROWS


    Brian Wakem wrote:
    > salvador wrote:
    >
    > > I've got a web site in beta testing right now. I've come across a bug
    > > that is turning up some strange results.
    > >
    > > I'm doing a query "select SQL_CALC_FOUND_ROWS
    > > ml,City,ListPrice,TotalSQFT,NumberofBedrooms,Total Bathrooms,NumberofPhotos
    > > from residential where City like "McMinnville" OR City like "Yamhill"
    > > and ListPrice >= 1000 and ListPrice <= 500000 and NumberofBedrooms >= 1
    > > AND TotalBathrooms >= 1 order by ListPrice desc LIMIT 10,10"
    > >
    > > But the return list is spitting back results that are out size of the
    > > range specified by listprice.
    >
    >
    > You have a precedence issue. The ANDs are evaluated before ORs. Your query
    > is effectively this:
    >
    > select SQL_CALC_FOUND_ROWS
    > ml,City,ListPrice,TotalSQFT,NumberofBedrooms,Total Bathrooms,NumberofPhotos
    > from residential where City like "McMinnville" OR (City like "Yamhill" and
    > ListPrice >= 1000 and ListPrice <= 500000 and NumberofBedrooms >= 1 AND
    > TotalBathrooms >= 1) order by ListPrice desc LIMIT 10,10"
    >
    > So if the City is McMinnville it will match regardless.
    >
    > When you probably want:
    >
    > select SQL_CALC_FOUND_ROWS
    > ml,City,ListPrice,TotalSQFT,NumberofBedrooms,Total Bathrooms,NumberofPhotos
    > from residential where (City like "McMinnville" OR City like "Yamhill") and
    > ListPrice >= 1000 and ListPrice <= 500000 and NumberofBedrooms >= 1 AND
    > TotalBathrooms >= 1) order by ListPrice desc LIMIT 10,10"
    Yep. That was the problem.
    > Another issue is you are using like with no wildcard, you may as well use =
    > instead.
    The data is set up so that a user can also select "all cities", in
    which case the wildcard is used. I suppose I should rewrite the output
    from the conditional to be a little more graceful.

    Thanks a bunch.



    >
    >
    > --
    > Brian Wakem
    > Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]
    salvador Guest

Similar Threads

  1. Replies: 0
    Last Post: September 24th, 03:24 AM
  2. Replies: 0
    Last Post: September 11th, 11:26 AM
  3. Replies: 0
    Last Post: September 11th, 12:19 AM
  4. Replies: 6
    Last Post: August 29th, 04:37 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139