Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

php script to convert mysql table

Posted on 2002-04-11
10
Medium Priority
?
270 Views
Last Modified: 2008-03-10
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;

0
Comment
Question by:1524
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 4

Expert Comment

by:lokeshv
ID: 6933558
just a small ex for u ...


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_col_2,database1.table1.table1_col_3 from database1.table1 ";

$qid=mysql_db_query($query1);

if($qid){
        for($i=0;$i<mysql_numrows($qid);$i++){
                $row=db_fetch_array($qid);
                 add_data_in_table2($row[table1_col_2],$row[table1_col_3]);
         }
}
else{
        echo'Errror:'.mysql_error();

}



function add_data_in_table2($col1,$col2){
$query2="insert into database2.table2(database2.table2.table2_col_1,database2.table2_col_2) values('$col1','$col2')";

$qid=mysql_db_query($query2);

if($qid){
     return 1;
}
else{
        echo'Error:'.mysql_error();
}
}



Hope this will help...

Lk
0
 

Author Comment

by:1524
ID: 6933594
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/convert.php on line 4
Errror:

and here's the code I used:

<?php
$query1="select underground.links.links_col_2,underground.links.links_col_3 from underground.links";

$qid=mysql_db_query($query1);

if($qid){
       for($i=0;$i<mysql_numrows($qid);$i++){
               $row=db_fetch_array($qid);
                add_data_in_nuke_links_links($row[links_col_2],$row[links_col_3]);
        }
}
else{
       echo'Errror:'.mysql_error();

}



function add_data_in_nuke_links_links($col1,$col2){
$query2="insert into main.nuke_links_links(main.nuke_links_links.nuke_links_links_col_1,main.nuke_links_links_col_2) values('$col1','$col2')";

$qid=mysql_db_query($query2);

if($qid){
    return 1;
}
else{
       echo'Error:'.mysql_error();
}
}
?>

Thanks in advance!

--

Ben
0
 
LVL 4

Expert Comment

by:lokeshv
ID: 6933621
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_name",$query) 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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:lokeshv
ID: 6933726
cna u please post here whihc columns u want o grab and wannt to put in whihc column ?


Lk
0
 
LVL 5

Expert Comment

by:harwantgrewal
ID: 6933743
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
0
 

Author Comment

by:1524
ID: 6933867
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
0
 
LVL 5

Expert Comment

by:harwantgrewal
ID: 6933991
have to try to output txt file from phpmyadmin I think it will be easier to you

Harry
0
 
LVL 4

Accepted Solution

by:
lokeshv earned 1200 total points
ID: 6934010
here it is ..

just add ur database server setting and the database_name1
and database_name2...


<?
$conn=mysql_pconnect("database_server","user","password");

if(!$conn){
        echo'couldnt connect';
        die;
}



$query="insert into database_name2.nuke_links_links(pn_title,pn_url,pn_date,pn_description,pn_cat_id,pn_submitt
er,pn_email,pn_hits) select SiteName,SiteURL,Added,Description,Category,UserName,Email,HitsOut from database_na
me1.links";

$qid=mysql_query($query);
if($qid){
        echo'table updated';
}
else{
        echo'error:'.mysql_error();
}
?>



Hope this will solve problem...

Lk:o)
0
 

Author Comment

by:1524
ID: 6934063
Thanks!!!! youre a lifesaver! much appreciated :)
0
 
LVL 4

Expert Comment

by:lokeshv
ID: 6934188
anytime :o)


LK
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question