I am running the following query:
SELECT
...stuff...
FROM
properties LEFT JOIN property_photos ON
property_photos.property_id=properties.id,
states
WHERE
states.code='fl' AND
properties.state_id=states.id
LIMIT 0, 10
(not sure if the JOIN is correct at all...I usually just join with the WHERE
clauses, but I need to make sure that I get properties even when they do not
have a photo - maybe this should be an LEFT OUTER JOIN?)
"properties" is the main table (contains rental properties).
"states" contains the names and codes of states. The "properties" table
references the "states" table by an entry called "state_id" that, oddly
enough, contains the id number from the "states" table.
"property_photos" contains photos of the properties. There can be many
"property_photo" entries for each property. "property_photos" references the
"properties" table by the field "property_id". In this case, I only want one
of the property photos returned...the one with property_photos.ordinal=1
(the one the customer ordered as first). I didn't fool with this right now,
though, because there are NO photos in the database right now.
This code is supposed to be searching for properties (think rental
properties) that are in a particular state (florida in this case). The state
is sent to the search program as a state code ("fl" in this case), not the
id number of the state in the state table (I did this to make it look more
readable to the user if seen in the query string - i.e. search.php?state=fl
instead of state=10).
There are only 7 properties entered into the database, but I am getting 10
results. It's giving me properties with id's 1-7, and then starting over
again and giving me id's 1-3.
All 7 should be listed as results since all 7 of them are in florida...but
why is it repeating the result set and showing 10 results? When I take the
LIMIT clause out, it gives me 315 results...the same repeating pattern
again.
It's late, and table joins confuse me...could someone please help? Please
let me know if I should supply more information - I've tried to be thorough,
but it's after midnight, and I need sleep. :-P
Katie Dewees
Web Developer
E-mail: kdewees@xxxxxxxxxxxxx
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
