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

Question

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

@ShankarDamodaran, I fixed the Values, still returning `Insert Query is not OK` — Sami, Mar 23 '14 at 17:14
It was because you weren't grabbing the original query values, using the keyword values doesn't change anything if you don't establish values — Chitowns24, Mar 23 '14 at 17:16
@wishchaser, I got the warning `Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, resource given in...` — Sami, Mar 23 '14 at 18:09
@Apiah So you have problem with your SELECT query in the first place. Please check table name, column names and the WHERE clause condition. To be sure, store the query in a variable, say `$query` and echo it. Paste the echoed query in your mysql administration UI, such as a PHPMyAdmin and see what is the actual mistake you are doing. — wishchaser, Mar 23 '14 at 18:16
@wishchaser, thank you for your time and help. I echoed the `$number = mysql_num_rows($select_query); ` and it returned `20`. would you also recommend something for insert query? — Sami, Mar 23 '14 at 18:20
@Apiah Yes, the same thing as said in my previous comment. See if you have problems in your INSERT statements by echoing the query. Not `$select_query`. The original query `echo "INSERT INTO jungle (desk1, chair1, table1) VALUES ('$field1', '$field2', '$field3')";` as in Chitowns24's answer. — wishchaser, Mar 23 '14 at 18:29
@wishchaser, thank you again for your comment. From `mysql_error($insert_query)`, I get this error message `Warning: mysql_error() expects parameter 1 to be resource, string given in...` — Sami, Mar 23 '14 at 19:22
@Apiah, I don't think it is a proper way to use `mysql_error()` function. In your case, it must be something like `if(!$insert_query) die(mysql_error());` — wishchaser, Mar 23 '14 at 19:53
And Please `echo "INSERT INTO jungle (desk1, chair1, table1) VALUES ('$field1', '$field2', '$field3')";` and check for errors in the output echoed. — wishchaser, Mar 23 '14 at 19:55
@Apiah Please update your question with the latest code you are using. It will help us see what you are doing. — wishchaser, Mar 23 '14 at 19:58
@wishchaser, could you please check this query? `$insert_query = mysql_query("INSERT INTO jungle (desk1, chair1, table1) VALUES ('hello world', 'hello world', 'hello world')", $mysql_connection2); `, I can not even insert the above into the database. I checked the database connection and it was also ok. — Sami, Mar 23 '14 at 20:00
@Apiah Yes it is correct if `$mysql_connection2 is valid`, `The connection has a database named blueberry`, `there is a table named jungle in the database blueberry` and `the table jungle contains the 3 columns desk1, chair1 and table1` — wishchaser, Mar 23 '14 at 20:07
@wishchaser, I updated the code. Also, this runs fine `if ($mysql_connection2) {echo "connection is OK";}` — Sami, Mar 23 '14 at 20:12
@Apiah, What about `if(mysql_select_db($db2, $mysql_connection2)){echo "Database Selected";} ` — wishchaser, Mar 23 '14 at 20:16
@Apiah, `if(mysql_query("SELECT * FROM jungle LIMIT 1",$mysql_connection2)){echo "Table exists";}` — wishchaser, Mar 23 '14 at 20:25
@Apiah, `if(mysql_query("SELECT desk1, chair1, table1 FROM jungle LIMIT 1",$mysql_connection2)){echo "All columns exist";}` — wishchaser, Mar 23 '14 at 20:31
@Apiah, `if(mysql_query("INSERT INTO jungle SET desk1='success', chair1='success', table1='success'", $mysql_connection2)){echo "Values inserted successfully"}` — wishchaser, Mar 23 '14 at 20:42
@Apiah, Is there any column in the table jungle that cannot be empty? Maybe a unique or primary key. Set a value to that column in insert statement. — wishchaser, Mar 23 '14 at 20:56
@wishchaser, yes I think so, please let me check my table structure, I think one of columns doesn't have the default value, — Sami, Mar 23 '14 at 20:57
@wishchaser, thanks a lot, yes you are right, one of the columns doesn't have the default value and I set a value to the column; problem is solved. I appreciate all your time and help, cheers — Sami, Mar 23 '14 at 21:02
@Apiah Glad you made it work finally. If you had executed `INSERT INTO jungle SET desk1='success', chair1='success', table1='success'` in your phpMyadmin or something similar, you would have found the issue long ago. Anyway Cheers:) — wishchaser, Mar 23 '14 at 21:05

Chitowns24 · Accepted Answer · 2014-03-23T17:13:20.827

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

thanks for your response. I changed it to "INSERT INTO jungle (desk1, chair1, table1) VALUES (field1, field2, field3)", but still no luck! Is there something else I'm missing? — Sami, Mar 23 '14 at 17:09
thanks again, your answer is fine, there was a problem in my table structure that one column did not have the default value, so I gave a value to the column in insert query. — Sami, Mar 23 '14 at 21:14

score 1 · Answer 2 · edited Jul 19 '22 at 20:53

I get the post is old, but I had to do the same thing and used mysqli as is suggested. Only thing I would caution is this example uses just a integer field and a short text field. If you have additional fields you should use prepared statements, however as I know the data here is fixed and contains no special characters so this will be fine for my needs.

<?php
include "database1.php"; // Using database connection file here conn variable is $connj
include "database2.php"; // Using database connection file here conn variable is $conn


// select data and put values in array from first database
$selectsql = mysqli_query($connj,"SELECT id,name FROM table1 ORDER BY id ASC");
$result = mysqli_query($connj, $selectsql);

while($row = mysqli_fetch_array($selectsql))
{
    $id = $row['id'];
    $name = $row['name'];

    //replace query into second table in second database    
    $replacesql = mysqli_query($conn, "REPLACE INTO table2 (name,id) VALUES ('$name','$id');");
    
    if($replacesql)
    {
        echo "Replace Successful<br>";
    }
    else
    {
        echo "replace error<br>";
    }
}
?>

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

2 Answers2

Linked