1524
asked on
php script to convert mysql table
Hi,
I need to take the data out of my table 'links' (structure below) and transfer it into my table 'nuke_links_links' (structure also below) which is contained in a different database, but on the same server. I figured that the best way to do this would be with a php script, but I don't know enough about php to write one... I actually tried to do this just using phpmyadmin but was pretty unsucessful...
any ideas anyone? I've put the max number of question points up for this...
# Table structure for table `links`
#
CREATE TABLE links (
ID int(11) NOT NULL auto_increment,
SiteName varchar(100) NOT NULL default '',
SiteURL varchar(100) NOT NULL default '',
LastUpdate timestamp(14) NOT NULL,
Added timestamp(14) NOT NULL,
Description blob NOT NULL,
Category smallint(6) unsigned NOT NULL default '0',
UserName varchar(16) NOT NULL default '',
Password varchar(16) NOT NULL default '',
Hint varchar(50) NOT NULL default '',
Email varchar(50) NOT NULL default '',
InIP varchar(15) NOT NULL default '',
OutIP varchar(15) NOT NULL default '',
HitsIn int(7) unsigned NOT NULL default '0',
HitsOut int(7) unsigned NOT NULL default '0',
PRIMARY KEY (ID),
UNIQUE KEY SiteURL (SiteURL),
KEY Category (Category),
KEY HitsIn (HitsIn),
KEY HitsOut (HitsOut)
) TYPE=MyISAM;
# -------------------------- ---------- ---------- ----------
importing into this:
# Table structure for table `nuke_links_links`
#
CREATE TABLE nuke_links_links (
pn_lid int(11) NOT NULL auto_increment,
pn_cat_id int(11) NOT NULL default '0',
pn_title varchar(100) NOT NULL default '',
pn_url varchar(100) NOT NULL default '',
pn_description text NOT NULL,
pn_date datetime default NULL,
pn_name varchar(100) NOT NULL default '',
pn_email varchar(100) NOT NULL default '',
pn_hits int(11) NOT NULL default '0',
pn_submitter varchar(60) NOT NULL default '',
pn_ratingsummary double(6,4) NOT NULL default '0.0000',
pn_totalvotes int(11) NOT NULL default '0',
pn_totalcomments int(11) NOT NULL default '0',
PRIMARY KEY (pn_lid)
) TYPE=MyISAM;
I need to take the data out of my table 'links' (structure below) and transfer it into my table 'nuke_links_links' (structure also below) which is contained in a different database, but on the same server. I figured that the best way to do this would be with a php script, but I don't know enough about php to write one... I actually tried to do this just using phpmyadmin but was pretty unsucessful...
any ideas anyone? I've put the max number of question points up for this...
# Table structure for table `links`
#
CREATE TABLE links (
ID int(11) NOT NULL auto_increment,
SiteName varchar(100) NOT NULL default '',
SiteURL varchar(100) NOT NULL default '',
LastUpdate timestamp(14) NOT NULL,
Added timestamp(14) NOT NULL,
Description blob NOT NULL,
Category smallint(6) unsigned NOT NULL default '0',
UserName varchar(16) NOT NULL default '',
Password varchar(16) NOT NULL default '',
Hint varchar(50) NOT NULL default '',
Email varchar(50) NOT NULL default '',
InIP varchar(15) NOT NULL default '',
OutIP varchar(15) NOT NULL default '',
HitsIn int(7) unsigned NOT NULL default '0',
HitsOut int(7) unsigned NOT NULL default '0',
PRIMARY KEY (ID),
UNIQUE KEY SiteURL (SiteURL),
KEY Category (Category),
KEY HitsIn (HitsIn),
KEY HitsOut (HitsOut)
) TYPE=MyISAM;
# --------------------------
importing into this:
# Table structure for table `nuke_links_links`
#
CREATE TABLE nuke_links_links (
pn_lid int(11) NOT NULL auto_increment,
pn_cat_id int(11) NOT NULL default '0',
pn_title varchar(100) NOT NULL default '',
pn_url varchar(100) NOT NULL default '',
pn_description text NOT NULL,
pn_date datetime default NULL,
pn_name varchar(100) NOT NULL default '',
pn_email varchar(100) NOT NULL default '',
pn_hits int(11) NOT NULL default '0',
pn_submitter varchar(60) NOT NULL default '',
pn_ratingsummary double(6,4) NOT NULL default '0.0000',
pn_totalvotes int(11) NOT NULL default '0',
pn_totalcomments int(11) NOT NULL default '0',
PRIMARY KEY (pn_lid)
) TYPE=MyISAM;
ASKER
Hi, Thanks for your help, I just tried using your example but I must still have something wrong... here's the error I got:
Warning: Wrong parameter count for mysql_db_query() in /home/sites/site61/web/con vert.php on line 4
Errror:
and here's the code I used:
<?php
$query1="select underground.links.links_co l_2,underg round.link s.links_co l_3 from underground.links";
$qid=mysql_db_query($query 1);
if($qid){
for($i=0;$i<mysql_numrows( $qid);$i++ ){
$row=db_fetch_array($qid);
add_data_in_nuke_links_lin ks($row[li nks_col_2] ,$row[link s_col_3]);
}
}
else{
echo'Errror:'.mysql_error( );
}
function add_data_in_nuke_links_lin ks($col1,$ col2){
$query2="insert into main.nuke_links_links(main .nuke_link s_links.nu ke_links_l inks_col_1 ,main.nuke _links_lin ks_col_2) values('$col1','$col2')";
$qid=mysql_db_query($query 2);
if($qid){
return 1;
}
else{
echo'Error:'.mysql_error() ;
}
}
?>
Thanks in advance!
--
Ben
Warning: Wrong parameter count for mysql_db_query() in /home/sites/site61/web/con
Errror:
and here's the code I used:
<?php
$query1="select underground.links.links_co
$qid=mysql_db_query($query
if($qid){
for($i=0;$i<mysql_numrows(
$row=db_fetch_array($qid);
add_data_in_nuke_links_lin
}
}
else{
echo'Errror:'.mysql_error(
}
function add_data_in_nuke_links_lin
$query2="insert into main.nuke_links_links(main
$qid=mysql_db_query($query
if($qid){
return 1;
}
else{
echo'Error:'.mysql_error()
}
}
?>
Thanks in advance!
--
Ben
ok do some changes first make a database connection
on top of script..
$conn=mysql_pconnect("host ","user"," password") ;
now replace all mysql_db_query with
mysql_db_query("database_n ame",$quer y) use query1 and query2 respectively...
and i used the col_1 and col_2 just and ex.
u have to relace those from ur tbale col names..
i m goin out this time..when i will return i will post the full code here...
Lk
on top of script..
$conn=mysql_pconnect("host
now replace all mysql_db_query with
mysql_db_query("database_n
and i used the col_1 and col_2 just and ex.
u have to relace those from ur tbale col names..
i m goin out this time..when i will return i will post the full code here...
Lk
cna u please post here whihc columns u want o grab and wannt to put in whihc column ?
Lk
Lk
I think by using the phpmyadmin is will be preety simple first generate a txt file and then use this text file to import the data into any table into any database but first of all check that structure of both the table is same
Harry
Harry
ASKER
Hi lokeshv,
here's how I would ideally like to transfer the data across:
thanx in advance! :)
--
Ben
ID
SiteName pn_title
SiteURL pn_url
LastUpdate
Added, pn_date
Description pn_description
Category pn_cat_id
UserName pn_submitter
Password
Hint
Email pn_email
InIP
OutIP
HitsIn
HitsOut pn_hits
here's how I would ideally like to transfer the data across:
thanx in advance! :)
--
Ben
ID
SiteName pn_title
SiteURL pn_url
LastUpdate
Added, pn_date
Description pn_description
Category pn_cat_id
UserName pn_submitter
Password
Hint
Email pn_email
InIP
OutIP
HitsIn
HitsOut pn_hits
have to try to output txt file from phpmyadmin I think it will be easier to you
Harry
Harry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!!!! youre a lifesaver! much appreciated :)
anytime :o)
LK
LK
modify that according to ur requirement
table1
table1_col_1
table1_col_2
table1_col_3
table2
table2_col_1
table2_col_2
suppose now u have to grab the data(col2 and col3) and dump into the table2
so
query1="select database1.table1.table1_co
$qid=mysql_db_query($query
if($qid){
for($i=0;$i<mysql_numrows(
$row=db_fetch_array($qid);
add_data_in_table2($row[ta
}
}
else{
echo'Errror:'.mysql_error(
}
function add_data_in_table2($col1,$
$query2="insert into database2.table2(database2
$qid=mysql_db_query($query
if($qid){
return 1;
}
else{
echo'Error:'.mysql_error()
}
}
Hope this will help...
Lk