Hi,
Need in brief:
Have 3 tables. I need to extract data from two tables and fill the third table.
Table structure:
COM PROFILER:
CREATE TABLE comprofiler (
id int(11) NOT NULL default '0',
user_id int(11) NOT NULL default '0',
firstname varchar(100) collate latin1_general_ci default NULL,
middlename varchar(100) collate latin1_general_ci default NULL,
lastname varchar(100) collate latin1_general_ci default NULL,
cb_appelation varchar(40) collate latin1_general_ci default NULL,
hits int(11) NOT NULL default '0',
avatar varchar(255) collate latin1_general_ci default NULL,
avatarapproved tinyint(4) default '1',
approved tinyint(4) NOT NULL default '1',
confirmed tinyint(4) NOT NULL default '1',
lastupdatedate datetime NOT NULL default '0000-00-00 00:00:00',
banned tinyint(4) NOT NULL default '0',
banneddate datetime default NULL,
bannedby int(11) default NULL,
bannedreason mediumtext collate latin1_general_ci,
acceptedterms tinyint(1) NOT NULL default '0',
cb_wineryphone varchar(255) collate latin1_general_ci default NULL,
cb_compaddr varchar(255) collate latin1_general_ci default NULL,
cb_wineaddr varchar(255) collate latin1_general_ci default NULL,
cb_caddress varchar(255) collate latin1_general_ci default NULL,
cb_waddress varchar(255) collate latin1_general_ci default NULL,
cb_ccity varchar(255) collate latin1_general_ci default NULL,
cb_wcity varchar(255) collate latin1_general_ci default NULL,
cb_cstate varchar(255) collate latin1_general_ci default NULL,
cb_wstate varchar(255) collate latin1_general_ci default NULL,
cb_czip varchar(255) collate latin1_general_ci default NULL,
cb_wzip varchar(255) collate latin1_general_ci default NULL,
cb_varitals varchar(255) collate latin1_general_ci default NULL,
cb_varietal mediumtext collate latin1_general_ci,
cb_amenities varchar(255) collate latin1_general_ci default NULL,
cb_amenitiebox mediumtext collate latin1_general_ci,
cb_operhours varchar(255) collate latin1_general_ci default NULL,
cb_opdelimit varchar(255) collate latin1_general_ci default NULL,
cb_ophr varchar(255) collate latin1_general_ci default NULL,
cb_hrsofop varchar(255) collate latin1_general_ci default NULL,
cb_closebox varchar(255) collate latin1_general_ci default NULL,
cb_mon varchar(255) collate latin1_general_ci default NULL,
cb_frm varchar(255) collate latin1_general_ci default NULL,
cb_to varchar(255) collate latin1_general_ci default NULL,
cb_chkmon tinyint(4) default NULL,
cb_tue varchar(255) collate latin1_general_ci default NULL,
cb_frmtue varchar(255) collate latin1_general_ci default NULL,
cb_frmtueto varchar(255) collate latin1_general_ci default NULL,
cb_chktue tinyint(4) default NULL,
cb_wed varchar(255) collate latin1_general_ci default NULL,
cb_frmwed varchar(255) collate latin1_general_ci default NULL,
cb_towed varchar(255) collate latin1_general_ci default NULL,
cb_cbwed tinyint(4) default NULL,
cb_thr varchar(255) collate latin1_general_ci default NULL,
cb_frmthr varchar(255) collate latin1_general_ci default NULL,
cb_tothru varchar(255) collate latin1_general_ci default NULL,
cb_chkthr tinyint(4) default NULL,
cb_fri varchar(255) collate latin1_general_ci default NULL,
cb_frmfri varchar(255) collate latin1_general_ci default NULL,
cb_tofri varchar(255) collate latin1_general_ci default NULL,
cb_chkfri tinyint(4) default NULL,
cb_sat varchar(255) collate latin1_general_ci default NULL,
cb_frmsat varchar(255) collate latin1_general_ci default NULL,
cb_tosat varchar(255) collate latin1_general_ci default NULL,
cb_chksat tinyint(4) default NULL,
cb_endform varchar(255) collate latin1_general_ci default NULL,
cb_bridge varchar(255) collate latin1_general_ci default NULL,
cb_descript mediumtext collate latin1_general_ci,
cb_phone varchar(255) collate latin1_general_ci default NULL,
cb_phonede varchar(255) collate latin1_general_ci default NULL,
cb_winephone varchar(255) collate latin1_general_ci default NULL,
cb_url varchar(255) collate latin1_general_ci default NULL,
cb_stadistr varchar(100) collate latin1_general_ci default 'california',
cb_prod varchar(12) collate latin1_general_ci NOT NULL default '0',
cb_sunday varchar(255) collate latin1_general_ci default NULL,
cb_fromsun varchar(255) collate latin1_general_ci default NULL,
cb_tosun varchar(255) collate latin1_general_ci default NULL,
cb_sunclos tinyint(4) default NULL,
cb_appelationloc varchar(255) collate latin1_general_ci default NULL,
cb_block varchar(255) collate latin1_general_ci default NULL,
cb_otherplz varchar(255) collate latin1_general_ci default NULL,
cb_otheramplz varchar(255) collate latin1_general_ci default NULL,
cb_varothrlabl varchar(255) collate latin1_general_ci default NULL,
cb_apploc varchar(255) collate latin1_general_ci default NULL,
cb_space varchar(255) character set latin7 NOT NULL default '',
cb_rowspaceone varchar(255) character set latin7 NOT NULL default '',
cb_rowspacetwo varchar(255) character set latin7 NOT NULL default '',
cb_rowspacethree varchar(255) character set latin7 NOT NULL default '',
cb_rowspacefour varchar(255) character set latin7 NOT NULL default '',
cb_rowspacefive varchar(255) character set latin7 NOT NULL default '',
cb_amenothrs varchar(255) character set latin7 NOT NULL default '',
cb_amenstrspace varchar(255) character set latin7 NOT NULL default '',
cb_rowspacesix varchar(255) character set latin7 NOT NULL default '',
cb_abvoerhour varchar(255) character set latin7 NOT NULL default '',
cb_abvophr varchar(255) character set latin7 NOT NULL default '',
cb_drop varchar(255) character set latin7 NOT NULL default '',
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
USER
CREATE TABLE users (
id int(11) NOT NULL auto_increment,
name varchar(50) collate latin1_general_ci NOT NULL default '',
username varchar(25) collate latin1_general_ci NOT NULL default '',
email varchar(100) collate latin1_general_ci NOT NULL default '',
`password` varchar(100) collate latin1_general_ci NOT NULL default '',
pwd2 varchar(45) collate latin1_general_ci NOT NULL default '',
usertype varchar(25) collate latin1_general_ci NOT NULL default '',
block tinyint(4) NOT NULL default '0',
sendEmail tinyint(4) default '0',
gid tinyint(3) unsigned NOT NULL default '1',
registerDate datetime NOT NULL default '0000-00-00 00:00:00',
lastvisitDate datetime NOT NULL default '0000-00-00 00:00:00',
activation varchar(100) collate latin1_general_ci NOT NULL default '',
params text collate latin1_general_ci NOT NULL,
PRIMARY KEY (id),
KEY usertype (usertype),
KEY idx_name (name)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
PLACE :
CREATE TABLE mos_scvmaps (
id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL default '',
address text,
suburb varchar(50) NOT NULL default '',
state varchar(20) NOT NULL default '',
country varchar(50) NOT NULL default '',
postcode varchar(10) NOT NULL default '',
misc text,
lat varchar(10) default NULL,
lng varchar(10) default NULL,
params text,
published tinyint(1) NOT NULL default '0',
checked_out int(11) NOT NULL default '0',
checked_out_time int(11) NOT NULL default '0',
ordering int(11) NOT NULL default '0',
catid int(11) NOT NULL default '0',
user_id int(11) NOT NULL default '0',
access tinyint(3) NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
PLACE TABLE DATA == USER.name + PROFILER.id,user_id, and profiler.address columns.
Please note that i want to do this in steps.
1. In the profiler table i have cb_apploc which holds the place in strings.
2. I want to retrieve the data rows matching one place string and then replace this with catid for maps table.
3. Likewise i could do for the other 7 places , I believe.
Please let me know how i could do this data insertion from one table to another with a column data string replaced to a integer value before inserting the other.
With regards
Harish
