Copying data from one table to another in the same database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Copying data from one table to another in the same database

Post by robster »

Hi :)

Is there a function (either php or mysql) that will grab the ENTIRE contents of one table (TableA) and append it to the end of another table (TableB). Both tables are IDENTICAL in their structure.

I've really looked around and can't seem to find something that pre-exists. I'm wondering if I'm giong to have to write my own tedious copy paste loop...?


I really am thankful for the help in these forums, if you know the answer I'd REALLY appreciate it :)

Rob
ybi
Forum Newbie
Posts: 6
Joined: Thu Sep 11, 2003 9:28 am
Location: Germany

Post by ybi »

hi robster,

just do

Code: Select all

<?php
$query="insert into table1 select * from table2";
mysql_query($query);
?>
table1 is the table you want to insert the complete content of table2.

I hope that'll help you :D

many greetings
ybi
?>
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

Wow, so simple... Thank you so much! :)


It's actually the other way around... I want all the contents of table 1 put into table 2... but yes, I get your code... VERY simple and thank you SO much...


:)


Rob
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

I knew it wouldn't be that easy LOL :)

There is ONE more thing.


The table that is being copied into already has the ID's of the table being copied from. That means that ID#1 would exist in both the source and destination tables...

Is that going to be a problem? Is the source table ID going to play havoc with the destination one?

(ID is an autoincriment in my tables)



Also, when I DO try and get this going (testing to see if my above thoughts are correct it seems to do its thing but I get not result (no copying of data from one table to the next).

Here is my code:

Code: Select all

<?php
echo "<br>connecting to database...<br><br>";
$connection = mysql_connect($dbhost, $dbusername, $dbpassword) or die("oops!  I couldn't connect to MySql (the database).  That's a BAD thing "); 


echo "copying data from current to current_public...<br><br>";
$query ="insert into current_public select * from current";
mysql_query ($query );
?>
Well, I tried an incorrect password in my config.php file and yes, my error message worked, then I fixed my password, no more error message. So I am presuming i'm connecting...

The rest though, doesn't copy the data from one table to the next...

Any ideas anybody? i'm completely stumped..

:(
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post by Unipus »

For starters, put this in your code, it will help you immensely with debugging:

Code: Select all

mysql_query ($query ) or die(mysql_error());
Second, I find it's ALWAYS best to test your queries directly in MySQL, so you can be absolutely certain if the problem is on the query, or in PHP.

Third, the query you were given before would probably work with your current situation if you change it like so:

Code: Select all

insert into current_public select {name, address, email_address, whatever} from current
inside the curly braces I've put an example... what you would want to put in yours is a comma-delineated list of every column EXCEPT the id column. That way, your data should insert naturally and append itself to the end of the current_public table. Obviously, leave out the curly braces.
Cruzado_Mainfrm
Forum Contributor
Posts: 346
Joined: Sun Jun 15, 2003 11:22 pm
Location: Miami, FL

Post by Cruzado_Mainfrm »

i think your code is alright, but u haven't selected a database yet:
mysql_select_db('mydb');
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

Thank you both for your help.

I replaced my custom error message with yours but as that part of the code was working fine it didn't do anything (as yet! ;) ).

re: the chosing the database, I do that in my connection line.

I took this line of cod into phpmyadmin as suggested:

Code: Select all

insert into current_public select &#123;'','year','month','number','title','animator','hardware','software','creationtime','rendertime','viewrecommend','animdescription','descriptionofcreation','mpg','jpg','rating','ratingafter','winner' &#125; from current
and this is the result it gave me:

Code: Select all

Error

SQL-query :  

INSERT  INTO current_public
SELECT &#123; '',  'year',  'month',  'number',  'title',  'animator',  'hardware',  'software',  'creationtime',  'rendertime',  'viewrecommend',  'animdescription',  'descriptionofcreation',  'mpg',  'jpg',  'rating',  'ratingafter',  'winner'&#125;
FROM current 



MySQL said: 
 
#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ''',  'year',  'month',  'number',  'title',  'animator',  'hard
Any ideas? I can't quite figuire that one out :(
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

upadate:

This worked in mysql:

INSERT INTO `current_public`
SELECT * FROM `current`;



I'll go look into it more in my php code and try and figuire it out, I'll post the results here incase anyone has this trouble in the future..

Thanks again,

Rob
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

It WAS the database not being chosen!!!

Here it is working ..Thank you all SO much! :)

Code: Select all

echo "<br>connecting to database...<br><br>";
$connection = mysql_connect($dbhost, $dbusername, $dbpassword) or die( mysql_error ()); 


echo "copying data from current to current_public...<br><br>";
$content = mysql_db_query($dbname, "INSERT INTO current_public SELECT * FROM current");
$Xcontent = mysql_fetch_array($content);
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

Ok, there's always one more thing isn't there...

I can't use the code above as I don't want to copy the ID across... so again, i went back and tried this method but it didn't work...

Can anyone see something really obvious in there?
Sorry to keep posting like this. I'm just at a loss.

Ta,

Rob

Code: Select all

$content = mysql_db_query($dbname, "INSERT INTO current_public ('','year','month','number','title','animator','hardware','software','creationtime','rendertime','viewrecommend','animdescription','descriptionofcreation','mpg','jpg','rating','ratingafter','winner') FROM current"); 
$Xcontent = mysql_fetch_array($content);
User avatar
Leviathan
Forum Commoner
Posts: 36
Joined: Tue Sep 23, 2003 7:00 pm
Location: Waterloo, ON (Currently in Vancouver, BC)

Post by Leviathan »

Why are you trying to insert a blank field? That's your problem. Yes, I know you have an id that you don't want to copy over (and can't, since it's on autoincrement), so what you need to do is get rid of that. Try it and see what happens.
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post by Unipus »

That parenthetical is unecessary and may be breaking the query. Also you were missing the "SELECT"... the query you had would have been sending blank data anyway.

Code: Select all

INSERT INTO current_public SELECT year,month,number,title,animator,hardware,software,creationtime,rendertime,viewrecommend,animdescription,descriptionofcreation,mpg,jpg,rating,ratingafter,winner FROM current
Haven't tested it, but I believe it should work.
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

Thanks again guys.

Funny thing. I used the code just posted by unipus and it didn't work, but I added the '' for the Id tag and it did work... I don't get it, but it SEEMS to work at the moment...

I'll drop back later after further testing. thanks again, so much :)

Rob



works:

Code: Select all

$content = mysql_db_query($dbname, "INSERT INTO current_public SELECT '',year,month,number,title,animator,hardware,software,creationtime,rendertime,viewrecommend,animdescription,descriptionofcreation,mpg,jpg,rating,ratingafter,winner FROM current");
Post Reply