Sami Sami - 2 months ago 9
MySQL Question

Select multiple columns from a table and insert data into another table in a different database in PHP-MySQL

I have to select data from a table in a database and insert them into another table in a different database. The below code returns the

$select_query
OK, but the
$insert_query
is not OK. Could you please correct the code and let me know your response?

$host1 = "localhost";
$user1 = "jackpot";
$pass1 = "jackpot";
$db1 = "pinkapple";
$host2 = "localhost";
$user2 = "jackpot";
$pass2 = "jackpot";
$db2 = "blueberry";

$mysql_connection1 = mysql_connect($host1, $user1, $pass1);
mysql_select_db($db1, $mysql_connection1) or die(mysql_error());
$select_query = mysql_query("SELECT field1, field2, field3 FROM tree WHERE date_entered > '2014-01-01 16:22:00'", $mysql_connection1);
$number = mysql_num_rows($select_query);

if ($select_query) {
echo "Select Query is OK <br>";
echo $number ."<br>";
} else {
echo "Select Query is not OK <br>";
}

$mysql_connection2 = mysql_connect($host2, $user2, $pass2, true);

mysql_select_db($db2, $mysql_connection2) or die(mysql_error());

while ($row = mysql_fetch_array($select_query)) {
$field1 = $row['field1'];
$field2 = $row['field2'];
$field3 = $row['field3'];
$insert_query = mysql_query("INSERT INTO jungle (desk1, chair1, table1) VALUES ('$field1', '$field2', '$field3')", $mysql_connection2);
if ($insert_query) {
echo "Insert Query is OK <br>";
} else {
echo "Insert Query is not OK <br>";
}
}
mysql_close($mysql_connection1);
mysql_close($mysql_connection2);


below you can see the schematic image of the tables.
enter image description here

Answer

You need to establish the values?

"INSERT INTO jungle (desk1, chair1, table1) VALUES (value1, value2, value3)"

You should collect the data from the first select and then you can set the pertinent values in your insert statement

You can do that using the mysql_fetch statement

$mysql_connection2 = mysql_connect($host2, $user2, $pass2, true);
mysql_select_db($db2, $mysql_connection2) or die(mysql_error());

while($row = mysql_fetch_array($select_query))
{
    $field1 = $row['field1'];
    $field2 = $row['field2'];
    $field3 = $row['field3'];
    $insert_query = mysql_query("INSERT INTO jungle (desk1, chair1, table1) VALUES ('$field1', '$field2', '$field3')",$mysql_connection2);

    if ($insert_query) {
      echo "Insert Query is OK <br>";
    } else {
      echo "Insert Query is not OK <br>";
    }

}

This statement loops through your select statement and inserts a row for every returned result using your INSERT statement

But really you should also look into mysqli because mysql is depreciated. This is however the basic logic behind what you are doing

Comments