Help with query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Help with query

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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;
?>
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Help with query

Post 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]'
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Help with query

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

or as an alternative, you could join on 'listing.city_id = city.id or listing.otherCity = city.name'
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Post Reply