MySQL IN function?

[kernelgpf]

$query=mysql_query("select cities.description,cities.name,city_buildings.building_title,city_buildings.building_type,city_buildings.ID from cities,city_buildings where cities.name='$row[location]' and cities.name IN (city_buildings.cityname)")or die(mysql_error());

 

"cityname" is either one city or a list of cities (city1, city2, etc.), but how do I make it work with this query? This query only brings back the buildings with individual city names in "cityname".

[MadTechie]

where cities.name='$row[location]'

should be

where cities.name IN ($row[location])

 

BUT $row[location] would need to be either 

'cityname'

or

'city1','city2','city3','city4','city5'

 

IF $row[location] is either

cityname

or

city1,city2,city3,city4,city5

you could use

$row['location'] = "'".str_replace(",","','",$row['location'])."'";

to make it fix (this probably isn't the best route but with the data supplied it should work

[kernelgpf]

and cities.name IN (city_buildings.cityname)

 

That's the problem, not the first part.

[Ken2k7]

This is a very odd query. If you're checking that cities.name is equal to $row['location'], why would you need MySQL IN? I mean you know the value. Just replace the IN part with AND cities.name = city_buildings.cityname and it should work. My point is a value can't be two things at once. So if cities.name is Boston, then no matter what's in the IN clause, you only care for Boston. No need for IN if you only have one thing to look for.

[Maq]

and cities.name IN (city_buildings.cityname)

 

That's the problem, not the first part.

 

Why don't you use:

 

 WHERE cities.name = city_buildings.cityname

 

I don't think IN should be used like that, but I could be mistaken.

[kernelgpf]

Because: $row[location] is ONE city, always. "cityname" is a list, so I need to check if $row[location] is in "cityname" somewhere.

[Ken2k7]

Just do this

 

$query = mysql_query('SELECT c.description, c.name, cb.building_title, cb.building_type, cb.ID FROM cities c INNER JOIN city_buildings cb ON (c.name = cb.cityname) WHERE c.name = "' . $row['location'] . '";') or die(mysql_error());

 

Give it a try.