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

Sami Published at Dev

Sami

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

Chitowns24

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-7

Comments

0 comments

From Dev

Related Related

Article

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

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

Mysql insert into with multiple id from another table

How to select data from a table and insert into another table?

How to insert multiple rows from a table to another table based on date condition (PHP-MySQL-Query)?

MySQL Insert INTO using SELECT from another table with additional data

php mysql select set of columns from table one based on values of colons in another table

Insert multiple rows into a MySQL database from a table

Insert multiple rows from select into another table

mysql: select rows from another table as columns

MySQL - Insert table data from another table

Multiple insert in MySQL table from PHP table

Select from table where columns names equal data in a different table

How to select data from a table and insert into another table?

MySQL/PHP check if data is in database table, if not insert it

looping mysql query to get multiple rows of data from a table and insert into another table

insert multiple rows mysql from another table

PHP: Show data from mysql database in table

How to insert data from 3 different input fields together in one column of table in mysql database in codeigniter

Select table from Sql server and insert data to Mysql table

How to insert and retrive data from one table to another table in mysql database

MySQL - Insert table data from another table

Multiple insert in MySQL table from PHP table

MySQL insert into column data from another table

Retrieve data from mysql database and insert it in a table using php

How to insert multiple columns with different select conditions into another table using SQL

Insert Data From One Table To Another - MySQL

mysql insert into table select from another database

Insert specific rows from one table in database into another with different columns

MySql: insert into table SELECT from another table where first_table ID =another_table.id

PHP MYSQL - insert a new record but requires column from select statement in another table - fails in php