MySQL Forums
Forum List  »  PHP

Re: html,mysql,php copy data from one db to another
Posted by: Barry Galbraith
Date: June 08, 2012 07:43PM

Brian, there a number of things you do to try to fix this problem.
See my comments / fixes in your code.

<html>
<body>

<?php

//Part 1: get data to copy 
$con = mysql_connect("IPADDRESS","USERNAME","PASSWORD");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("DATABASE", $con);

$MainResult = mysql_query("SELECT column1
,column2
,column3
,column4
,column5
,column6
,column7
,column8
,column9
,column10
,column11
,column12
,column13
,column14
,column15
,column16
,column17
  FROM TABLE") or die(mysql_error());  // check for errors

mysql_close($con);

// Done with part 1: get data to copy 

//Part 2: Insert and Update with new data
$con = mysql_connect("IPADDRESS","USERNAME","PASSWORD");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("DATABASE", $con);


while ($row = mysql_fetch_row($MainResult)) 
{
//  if the values you are inserting are strings they need to be quoted
// If the strings can contain ' or " or \ they need to be escaped
mysql_query("INSERT INTO TABLE VALUES ('".mysql_real_escape_string($row[0])."', 
'".mysql_real_escape_string($row[1])."', 
$row[2],  // ok if the value is an integer
$row[3],   // you'll need to fix the rest of these as required
$row[4], 
$row[5], 
$row[6], 
$row[7], 
$row[8], 
$row[9], 
$row[10], 
$row[11], 
$row[12], 
$row[13], 
$row[14], 
$row[15], 
$row[16], 
NULL)   // no quotes around NULL    
ON DUPLICATE KEY UPDATE TABLE SET COLUMN1=$row[0], // again, if it's a string it needs to be escaped and quoted
COLUMN2=$row[1], 
COLUMN3=$row[2], 
COLUMN4=$row[3], 
COLUMN5=$row[4], 
COLUMN6=$row[5], 
COLUMN7=$row[6], 
COLUMN8=$row[7], 
COLUMN9=$row[8], 
COLUMN10=$row[9], 
COLUMN11=$row[10], 
COLUMN12=$row[11], 
COLUMN13=$row[12], 
COLUMN14=$row[13], 
COLUMN15=$row[14], 
COLUMN16=$row[15], 
COLUMN17=$row[16]") or die(mysql_error()); // check for errors
}
//Done with part 2: Insert and Update with new data

//Part 3: Print Data from Updated DB
$MirrorResult = mysql_query("SELECT * FROM TABLE") or die(mysql_error());  // check for errors

$fields_num = mysql_num_fields($MirrorResult);
echo '<table border="1"><tr>';
$fields = mysql_query("DESCRIBE TABLE;");
while ($row = mysql_fetch_row($fields))
{
	 echo "<th>";
	 echo $row[0];
	 echo "</th>";
}
echo "</tr>";
while($row = mysql_fetch_row($MirrorResult))
{
    echo "<tr>";
    echo "<td>";
	echo $row[0];
	echo "</td>";
    echo "<td>";
    echo $row[1];
    echo "</td>";
    echo "<td>";
    echo $row[2];
    echo "</td>";
    echo "<td>";
    echo $row[3];
    echo "</td>";
    echo "<td>";
    echo $row[4];
    echo "</td>";
    echo "<td>";
    echo $row[5];
    echo "</td>";
    echo "<td>";
    echo $row[6];
    echo "</td>";
    echo "<td>";
    echo $row[7];
    echo "</td>";
    echo "<td>";
    echo $row[8];
    echo "</td>";
    echo "<td>";
    echo $row[9];
    echo "</td>";
    echo "<td>";
    echo $row[10];
    echo "</td>";
    echo "<td>";
    echo $row[11];
    echo "</td>";
    echo "<td>";
    echo $row[12];
    echo "</td>";
    echo "<td>";
    echo $row[13];
    echo "</td>";
    echo "<td>";
    echo $row[14];
    echo "</td>";
    echo "<td>";
    echo $row[15];
    echo "</td>";
    echo "<td>";
    echo $row[16];
    echo "</td>";
    echo "<td>";
    echo $row[17];
    echo "</td>";
    echo "</tr>\n";
}
echo "</table>";

mysql_free_result($MainResult);
mysql_free_result($MirrorResult);

mysql_close($con);
//Done with part 3: Print Data from Updated DB
?>

</body>
</html>

Good luck,
Barry.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.