I have 3 mysql tables:
CREATE TABLE IF NOT EXISTS `forum_profile_fields_data` (
`user_id` mediumint(8) unsigned NOT NULL default ‘0’,
`ruler_name` varchar(20) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE IF NOT EXISTS `forum_users` (
`user_id` mediumint(8) unsigned NOT NULL auto_increment,
`ruler_name` varchar(20) NOT NULL,
`planet_name` varchar(20) NOT NULL,
`x` tinyint(3) unsigned NOT NULL,
`y` tinyint(3) unsigned NOT NULL,
`z` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=53 ;
CREATE TABLE IF NOT EXISTS `dump_planets` (
`row_id` int(10) unsigned NOT NULL auto_increment,
`planet_id` smallint(5) unsigned NOT NULL,
`tick` smallint(5) unsigned NOT NULL,
`x` tinyint(3) unsigned NOT NULL,
`y` tinyint(3) unsigned NOT NULL,
`z` tinyint(3) unsigned NOT NULL,
`ruler_name` varchar(20) NOT NULL,
`planet_name` varchar(20) NOT NULL,
PRIMARY KEY (`row_id`),
KEY `idx_planet_id` (`planet_id`),
KEY `idx_tick` (`tick`),
KEY `idx_x` (`x`),
KEY `idx_y` (`y`),
KEY `idx_z` (`z`),
KEY `idx_ruler_name` (`ruler_name`),
KEY `idx_planet_name` (`planet_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=99301 ;
Every hour i want a php script to look at all 3 tables and do the following:
get dump_planets.x, dump_planets.y, dump_planets.z, dump_planets.planet_name from dump_planets where dump_planets.ruler_name = forum_profile_fields_data.ruler_name
the data should be taken from the (MAX)dump_planets.tick value
the data will then update into forum_users WHERE forum_users.user_id = forum_profile_fields_data.user_id
