Page 1 of 1
Help with query
Posted: Thu Aug 31, 2006 10:44 pm
by shiznatix
Ok heres the situation.
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:
Code: Select all
l.OtherCounty LIKE 'user_input'
OR
if there is an entry in the cities table for the 'user_input' then l.fkCityId = the Id for that entry
see what I mean? Hows that done in the WHERE section of the query?
Posted: Thu Aug 31, 2006 11:43 pm
by RobertGonzalez
Code: Select all
<?php
$where = '';
if (!empty($_POST['list_field']))
{
$where = ' WHERE `id_field` = ' . $_POST['list_field'];
}
if (!empty($_POST['manual_field']))
{
if (empty($where))
{
$where = " WHERE `text_field` LIKE '%{$_POST['manual_field']}%'";
}
else
{
$where .= " AND `text_field` LIKE '%{$_POST['manual_field']}%'";
}
}
$sql = 'SELECT * FROM `mytable`' . $where;
?>
Posted: Fri Sep 01, 2006 1:46 pm
by shiznatix
err i definatly did not explain my problem correctly. Let me try again
I want to do something like this:
Code: Select all
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?
Re: Help with query
Posted: Fri Sep 01, 2006 1:57 pm
by Weirdan
Hmm... probably something like this:
Code: Select all
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]'
Re: Help with query
Posted: Fri Sep 01, 2006 3:39 pm
by shiznatix
Weirdan wrote:Hmm... probably something like this:
Code: Select all
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
Posted: Fri Sep 01, 2006 5:32 pm
by Weirdan
The easiest way would be to use second query:
Code: Select all
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
Posted: Fri Sep 01, 2006 5:33 pm
by Weirdan
or as an alternative, you could join on 'listing.city_id = city.id or listing.otherCity = city.name'
Posted: Sat Sep 02, 2006 7:01 pm
by RobertGonzalez
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.