EDIT* Sorry I didn't mean to add that error message in the topic title.

Hey guys, I'm new to PHP and MYSQL and I have been searching for hours for a way to copy the data from a row in one table (all columns) and insert it into another table (same column names). I've also browsed around Daniweb and haven't found a direct solution for my code. I'm sorry if this has already been posted here.

Here is some of my code
I connect to the first database, get the row and break down the columns into variables. I then close that connection and open another connection on another server and attempt to add those variables to the second database

$con1 = /*connect to server 1*/
$con2 = /*connect to server 2*/

mysql_select_db("database1", $con1) or die(mysql_error());

$result = mysql_query("SELECT * FROM table1 ORDER BY id DESC", $con1);
$row1 = mysql_fetch_array($result);
$row2 = mysql_fetch_array($result);
$row3 = mysql_fetch_array($result);
/*etc...*/

/*after finding the row, break it up*/
$one =  $row3['col1'];
$two =  $row3['col2'];
$three =  $row3['col3'];
/*etc...*/

mysql_close($con1);
mysql_select_db("databse2", $con2) or die(mysql_error());

mysql_query
	("
		INSERT INTO table2 (col1, col2, col3, col4, col5)
		VALUES('$one','$two','$three','$four','$five')
	", $con2)
	or die(mysql_error());

I have an echo setup after the first connection, the second connection, and at the very end that echos all of the variables. They all work and I get no errors, but when I check the second database there is no data. I have tried countless ways of doing this, however, I always run into a problem or error. This is the closest I have gotten to completing the task at hand.

If anyone has some tips or advise, I would greatly appreciate the help. I've read hundreds of forums posts and gone through several options in the manual. maybe I missed something?

write first two lines shown below in code part, in the begining of your code and then try to run your page.

Also echo your query before inserting, copy that from browser, run that prepared query in your second database directly using phpmyadmin (or any other tool). Then check whether you are getting any error there or not.

<?php

error_reporting(E_ALL); 
ini_set("display_errors", 1);
        
$con1 = /*connect to server 1*/
$con2 = /*connect to server 2*/
.
.
.
.
$query="INSERT INTO table2 (col1, col2, col3, col4, col5)
		VALUES('$one','$two','$three','$four','$five')";
echo $query;

mysql_query($query, $con2)
	or die(mysql_error());

.
.
.

?>

Aside from utridevis proposal to get at the error itself, your code is pathetically awkward. How can you know how many rows and how many variables you will have? Construct a loop instead which reads from server1 and imports into server2 one row at a time.
Also, if I had to tackle this problem, I would not use PHP but rather the mysql command line interface. Just dump the table from server1, replace the table name (if necessary) and feed them into server2. You can do it all on one line:

mysqldump -h server1 db1 table1 | sed "s/table1/table_new/g" | mysql -h server2 db2

(You have to add your credentials with -u -and -p, of course).

Thanks for the quick response guys, I'm sorry I should have clarified what I was trying to do beforehand. Here is the setup... I have an admin panel that adds content to the first database, I check the number of rows in the database and the first 12 rows I display on the main page, if its greater than 12 I want to copy that 13th row and add it to the second database to be displayed on a different page while simultaneously deleting it from the first database.

There were no reported errors btw.

The reason my coding is so clunky is because I'm trying to visualize every step that happens before I make it more concise with loops and whatnot. In this case I want to copy the 13th row with 5 columns... so I have no question of the number of elements I'm working with.

I have found a similarly clunky way to fix the problem after thinking about urtrivedi's idea. I simply echo'ed all of the elements into several hidden form textareas and then added text that says "Database Shift is needed." and a button that says "continue?" to then add those element into the newtable the same way I did the first table.

Thanks alot for the help guys! Now it's time to simplify and make it more efficient... I'll definitely look into the mysql command line interface methods, but as I said. I just started looking into PHP/MySQL syntax yesterday.

If you know of a more efficient way of doing this, please enlighten me.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.