1

Using SQL I would like to create something like a loop on a database which has city names and search for each city in another database and if it matches than I want the corresponding latitude and longitude to be added to the first database.

I don't know how to do that using SQL alone without PHP.

One of the databases is a wordpress database, inside it there is a table called wp_postmeta has 4 columns :

meta_id, post_id, meta_key, meta_value.

So here I want all the meta key cities to loop though them like :

SELECT meta_value FROM wp_postmeta WHERE meta_key = "city"

And I want to search with this values inside another database in the same server called GEO :

ID, city_name, lat, long.

So if city_name in second db matches city I want the lat and long from the second db to be added to the first one.

Any help would be appreciated.

Hamza Abd Elwahab
  • 153
  • 1
  • 8
  • 16
  • What database system are you using? You tagged this with MySql and SQL Server, but those are totally different systems. Also, you need to provide information like the table structures in question, whether the databases are on the same server, and whether a single user has access to both. Also, make sure you distinguish between databases and tables; the distinction is very important. – elixenide Oct 12 '15 at 11:59
  • Different databases??!! This questions doesn't give away any proper question. Do you need to search tables?? If so, do you have right columns in place to use search?? – d_luffy_de Oct 12 '15 at 12:02
  • Go through the following link you may get some idea: http://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server – Naveen.sn Oct 12 '15 at 12:03
  • Sorry about that.So the system is Mysql.And there are two databases inside one of them is a wordpress database inside it there is a table called wp_postmeta so the columns there are meta_id, post_id, meta_key and meta_value.I want each meta_key with value "city" is meta_value with the value of the city is name to match values inside another database called GEO which has 4 columns : ID, city, lat and long.Hope this makes sense. – Hamza Abd Elwahab Oct 12 '15 at 12:04
  • @koushik Thanks, I believe I used the wrong term at first, it's a mySql not Sql database. – Hamza Abd Elwahab Oct 12 '15 at 12:25

1 Answers1

1

You can match your postmeta city values to your geo table by means of a JOIN operation. Try something like this

  SELECT pm.post_id, TRIM(pm.meta_value) city_name, 
         geo.lat, geo.lon
    FROM wp_postmeta pm
    LEFT JOIN geo ON TRIM(pm.meta_value) = geo.city_name
   WHERE pm.meta_key='city'

As long as the spelling of city names in your tables matches, this will display the latitude and longitude of the city with the name. Notice that the LEFT JOIN makes it display all the cities, including the ones without matching lat/lon values. You can change that to an ordinary INNER JOIN if you don't want the unmatched cities.

It sounds like you're hoping to change your postmeta table by inserting the lats and lons for the cities into it. This is a Bad Idea™. postmeta stores all its values as strings. Lat and lon are properly stored as FLOAT, or sometimes DOUBLE. Nothing good can come from changing coordinates to strings: you can't index them, you can't sort them, and you have to convert them back before you can compute using them. Instead, join your geo table when you need those values.

O. Jones
  • 103,626
  • 17
  • 118
  • 172