update query

hi,

i have two location and marker with similar filelds.that is

1.lid
2.name
3.address
4.latitude
5.longitude.
any change made in the location table it also resemble in makers table.i know it can be easily achieve by using triggers.Instead of triggers i using cron which run every 1 minute.during this execution check location table if any updation is there.if it is no updation just leave otherwise update the corresponding in the markers table. how it can be achieve.any body help me do that.

thanks

 

A trigger would be a much easier and less problematic method of doing this. Are you asking how to setup a trigger, or how to program a cron and query to do it?

 

AFAIK trigger is available only for MySQL version 5.

+1

 

i need the sql query for this updation process.i plan to put the query as the php script .this script run every one minutes.it check location table for changes.if any changes happen it automatically update the marker table.regarding to this i need sql query.how i do this.if u know help me.......
thanks

 

This sounds like a classic case of a database that is not properly normalized. You have 2 tables that essentially share data, yet instead of storing that shared data in one table and linking to it from the second table, you are trying to create some sort of Frankenstein auto-update function.

The proper way to fix this is to not store that shared data in the second table, instead store it all in the first table (possibly a third table) and assign a unique ID field to every row of data. Then store the corresponding unique ID field from the first table in every row of the second table that shares that data. No need to update, simply create a query and JOIN those two tables whenever you need to pull that data in.

If you would like to post the structure of your tables, some sample data and the relationship the tables have we would be happy to tell you specifically how your table structure should be modified.

 

i have two tables namely

Location Table

Lid (auto_increment)
street
name
latitude
longitude


Markers Table
id (auto_increment)
name
address
lat
lon

whenever new update in the location it resemble in the markers also.how it is possible plz help me do that.

 

Actually its simpler than I first described. You don't need the Marker's Table at all. Every single piece of data (excluding the id) is already in the Location Table. Where ever you use the Marker's Table simply use the Location Table instead.

The Marker's Table is a copy of the Location Table just without the 'street' field. There's no need to copy its data to the Markers Table because the Location Table has everything you need already.

 

hi,

Actually i perform in the markers but i cant do that in the location table.i need to perform some distance calculations and display the location in map so that's why i need when ever the updations made in the location table.it also effect in the markers table.

thanks