Hello,
I have an application that does searches against a database of homes.
A summary of initial search results is displayed by showing a few
columns of information about each home. When the user clicks on one
of the listings, it retrieves additional information (some from other
tables) and displays more detailed information about the house.
The summary listings are generated using a normal MySQL query. The
detailed views are implemented by specifying which result row to
display using a LIMIT clause. For example, if the user clicks on the
3rd listing on a page, the query will use this LIMIT clause:
LIMIT 2,1
We do this instead of specifying a value for the primary key so we can
have a "Next" and "Previous" button that will move between detailed
listings. These result rows may pull information in from other tables
for display. Sometimes the homes are sorted according to a particular
column, and sometimes they aren't.
Obviously this whole scheme depends on the homes staying in the same
order between the summary queries and the detail queries, even if the
ordering is ambiguous.
We've had this running for several years, and it's always worked
fine. We're now seeing some problems with it, possibly because of a
move from a server running MySQL 3.x to one running 4.x.
The problem we're seeing is that when additional tables are pulled in
for the detailed view, the order is different from the summary view,
so the wrong homes are displayed. Here's a simplified example. A
summary query might ask:
mysql> SELECT lutar_homes.mls_num, lutar_images.num_images,
lutar_homes_supplemental.address,
lutar_homes.listdate
FROM lutar_homes, lutar_homes_supplemental
LEFT JOIN lutar_images ON lutar_homes.mls_num = lutar_images.mls_num
WHERE ((lutar_homes.listdate >= (NOW() - INTERVAL '14 00:00:00'
DAY_SECOND)))
AND lutar_homes.mls_num = lutar_homes_supplemental.mls_num
ORDER BY lutar_homes.listdate DESC
LIMIT 1;
+---------+------------+-------------------------+------------+
| mls_num | num_images | address | listdate |
+---------+------------+-------------------------+------------+
| 051768 | 1 | 7540 Country Pride Lane | 2005-05-31 |
+---------+------------+-------------------------+------------+
1 row in set (0.00 sec)
When I add one more LEFT JOIN clause (the second one below) to get
additional fields for the detailed view, I get a different first home,
even though none of the search parameters have changed, and the table
hasn't changed:
mysql> SELECT lutar_homes.mls_num, lutar_images.num_images,
lutar_homes_supplemental.address,
lutar_homes.listdate
FROM lutar_homes, lutar_homes_supplemental
LEFT JOIN lutar_images ON lutar_homes.mls_num = lutar_images.mls_num
LEFT JOIN lutar_homes_stats ON lutar_homes.mls_num =
lutar_homes_stats.mls_num
WHERE ((lutar_homes.listdate >= (NOW() - INTERVAL '14 00:00:00'
DAY_SECOND)))
AND lutar_homes.mls_num = lutar_homes_supplemental.mls_num
ORDER BY lutar_homes.listdate DESC
LIMIT 1;
+---------+------------+------------------+------------+
| mls_num | num_images | address | listdate |
+---------+------------+------------------+------------+
| 051770 | 9 | 9149 Frankenmuth | 2005-05-31 |
+---------+------------+------------------+------------+
1 row in set (0.02 sec)
This change in ordering screws up my system, since if the user clicked
on the first result in the summary view, the detailed view may display
a completely different home.
So my question is: Is this normal behavior for MySQL, or is it a bug?
Any suggestions for dealing with it?
Thanks! Please let me know if any of this is confusing, and I'll
clarify.
---ScottG.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
