Page 1 of 1

Copying data from one table to another in the same database

Posted: Tue Sep 23, 2003 6:08 am
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

Posted: Tue Sep 23, 2003 6:21 am
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
?>

Posted: Tue Sep 23, 2003 6:32 am
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

Posted: Tue Sep 23, 2003 12:50 pm
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..

:(

Posted: Tue Sep 23, 2003 6:57 pm
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.

Posted: Tue Sep 23, 2003 7:01 pm
by Cruzado_Mainfrm
i think your code is alright, but u haven't selected a database yet:
mysql_select_db('mydb');

Posted: Thu Sep 25, 2003 2:13 pm
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 :(

Posted: Thu Sep 25, 2003 2:28 pm
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

Posted: Thu Sep 25, 2003 2:38 pm
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);

Posted: Thu Sep 25, 2003 3:35 pm
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);

Posted: Thu Sep 25, 2003 5:29 pm
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.

Posted: Thu Sep 25, 2003 5:39 pm
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.

Posted: Fri Sep 26, 2003 12:19 am
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");