People can add a 'listing'. They specify the city the listing is in. They can either select it from the list or they can manually add a name. If they select it from the list then the Id of that city is stored in the 'fkCityId' field, if they manually enter it then the name they entered is stored in the 'OtherCity' field. Good.
Now when someone comes along and searches the user can either select the city from a list or manually enter it. Depending on which they use I search the required field in the table. That is the problem.
I want it to always search on both fields. The problem is the one field is an Id of a entry in another table (cities) while the other is a varchar whos value may or may not exist in the cities table. So how would I do something like this theory:
l.fkCityId = 'USER_SUPPLIED_NUMBER'
OR
(SELECT CityName FROM cities WHERE Id = 'USER_SUPPLIED_NUMBER'; ***now somehow get that CityName out and...*** l.OtherCity = 'THE_CITY_NAME_FROM_THAT_QUERY_WITHIN_THE_QUERY')
See the problem is I need the CityName for the specified Id, then I need to check if that name against the l.OtherCity. But I can't run the query to get the name within the WHERE of the main query and do all that fancy stuff so How would I do it?
select
listing.*
from
listing
left join /* has to be 'left' since we need rows from listing with no matching rows in cities too */
city
on
listing.city_id = city.id
where
/* either */
city.id = $_POST[id]
or city.name = '$_POST[name]'
or listing.otherCity = '$_POST[name]'
select
listing.*
from
listing
left join /* has to be 'left' since we need rows from listing with no matching rows in cities too */
city
on
listing.city_id = city.id
where
/* either */
city.id = $_POST[id]
or city.name = '$_POST[name]'
or listing.otherCity = '$_POST[name]'
not quite what i mean. What I mean is if there is no $_POST[name] only $_POST[id]. How do I get the 'name' for that Id (from the city table), then check if it is in the listing.otherCity field?
edit: post 2006, the only time my post count will equal the year that i posted it in
select @name_from_id := name from city where id=$_POST[id];
SELECT
listing.*
FROM
listing
LEFT JOIN /* has to be 'left' since we need rows from listing with no matching rows in cities too */
city
ON
listing.city_id = city.id
WHERE
/* either */
city.id = $_POST[id]
OR city.name = '$_POST[name]'
OR listing.otherCity = '$_POST[name]'
or listing.otherCity = @name_from_id
You could run two queries. One (if the ID was used) to check if the name is in the database and, if not, do whatever. The second that uses either entered city name, or, alternatively, the city name returned by the id search.