Sync Fields

It is a basic principle of database design that you don’t duplicate data that is identical. Difference is information, no difference means no information. And there are always problems when trying to maintain the duplicates.
If the location table always matches the main table, then the info can be fetched using a JOIN.

The main table has more fields than the locations table. They are part of two separate systems whose location data is/are the same. The issue is exactly how to get the location data to be the same as the location data of the main table.
Please be specific. Exactly how can a JOIN be used? Please give example(s). When location information is changed in the main table, that change is also needed in the locations table.

A “JOIN” is a variation to the SQL “SELECT” statement, it lets you construct a synthetic database row that combines fields from multiple tables that match on specified criteria.
More details available here:
https://dev.mysql.com/doc/refman/5.7/en/select.html
https://dev.mysql.com/doc/refman/5.7/en/join.html

If you could list the fields from the main and locations tables that pertain to your question, and also provide say 3 rows of data covering say 2 different locations, and also the SELECT statement you use on the main table, then I will be able to show you how the join works.

Thank you.
There is a main table that is a list of businesses with the business address and lots of other information. It looks something like:
ID Name Address City State Zip URL Other Other Other
There is a locations database that uses a locations table that looks like this:
location_id location_label location_address location_city location_state location_postcode location_url
These tables are parts of two separate systems that share the same location data and work in concert with each other. Right now I have to edit both tables as well as the corresponding WordPress page. Needed is to be able to input into the main table and have the changes “automatically” reflected in the locations database.

Presumably it is business names which are being looked up in the main table to get among other fields the business address. Please describe what is being looked up in the locations table ?

See description above.

You described the fields of the locations table, not how it is looked up. My question was reasonable. You clearly don’t need my assistance.

Bye.

You are correct.
Thank you.

Looking for plugin that will sync any selected fields in any selected table with any selected fields in any other selected table, in the same database, in either direction, independent of how any other system works with them. The tables that need syncing are part of two separate systems, one written almost totally in java and the other totally in php. The locations table (java) needs to be identical with the locations only, in the main database table. Every time there is a change or addition to the main database, certain location data (fields) for that table needs to be synchronized with the locations table. The locations data is used by a calendar whereas the main data is used as an information database that uses the calendar events but not its locations. This is why the sync is one direction only (main to locations), and only for selected fields used by the calendar locations database.

The ID, Name, Address, City, State, and Zip, information, are the same for these two tables. The corresponding fields in the Locations table needs to always be the same as the main data table, for each record, whether or not the field names are the same.

The address data in the locations database needs to always be the same as in the main database. When the address data changes in the main database, this needs to be automatically changed in the locations database.

In addition, when a new record is created or deleted in the main database table, the same needs to happen in the locations table. In creating any new location in the locations table, the name has to be appended with the city and state. This happens within the same database, not between two separate databases. There are several fields in several tables that need to always be the same as certain fields in the main table.

This is all done manually now.

Looking for plugin or code that does this automatically.

There are multiple plugins that use some of the information in the main table such that when the data in the main table changes, certain fields need to be updated in the data of each plugin.

Here is what’s happening:

There are several interacting plugins and scripts.

First the main calendar. It uses a locations table of its own. This table contains names, addresses, business URLs, and other data used by the calendar only. This locations table shares ID number, address, and facility URL, with the main facility table, and needs to be automatically updated when the main facility table is changed. These are the only fields in this table shared with the main facilities table. The other fields are used by the calendar only.

There is a Facility Locator. This is a system that holds facility specific data like ID number, Name, Address, Business URL, and other data used, specific to that business, such as hours and location, not used by the calendar. It gets its data from the main facilities table. Its task is to take the user to the selected facility page, where one can get data for that specific facility, including its event schedule, among other features.

The Event Display system lists events, dates, event URLs, for each facility. It gets its data from the calendar and main facility tables by ID numbers.

The Event Printing system gets its data from the main main calendar locations table and main facility table, and enables a user to print the event data in several formats.

These both tables in ONE database where certain fields in the calendar locations table need to be kept updated with similar fields (named differently) in the calendar locations table, AND when a record is added or deleted in the main facilities table, the same happens in the calendar locations table. The main facilities table is the master. This will do away with making future manual changes to the locations table as well as help minimize human errors.

This can be either a plugin or code.

Not trying to make a third table. Need to sync between certain fields in main table and certain fields in locations table. Their names are not the same. Main table is never changed by Locations table. Fields and records in Locations table are always changed, added, and/or deleted, by Main table.