Mysql/PHP Merge two tables

Question

How can I merge two tables into one table ?

For example, let say I have table named person with first_name and last_name,gender then I have another table called person_d and this has first_name, last_name, telephone and email.

The new table should contain first_name, last_name, telephone and email,gender

but i want to do that in an automatic way without even know what the fields name are just by using a conditions

Is that possible with sql or i have to do it using php? I thought that I can create php script and get fields name into two arrays then compare for doubles and add them to new array and delete them after that we will have arrays with no doubles this is my code

    <?php


$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "mydb";



//mysql and db connection

$db = new mysqli($servername, $username, $password, $dbname);

if ($db->connect_error) {  //error check
    die("Connection failed: " . $con->connect_error);
} else {

}


$query1 = $db->query("SELECT * FROM     tablename1");
$query2 = $db->query("SELECT * FROM     tablename2");
$table1 = array();
$table2 = array();
$newtable = array();

while ($property = mysqli_fetch_field($query)) { //fetch table field name

    array_push($table1,$property->name);

}

while ($property = mysqli_fetch_field($query)) { //fetch table field name

    array_push($table2,$property->name);

}


foreach($table1 as $field1) { 
    foreach($table2 as $field2) { 
        if ($field1==$field2)
        {
            array_push($newtable,$field1);
            unset($table1[$field1]);
            unset($table1[$field2]);

        }

    }

}

and after that i create new table with fields from arrays side by side

Is this the best way or there is another way to do that ?

Please don't just ask us to solve the problem for you. Show us how you tried to solve the problem yourself, then show us exactly what the result was, and tell us why you feel it didn't work. Give us a clear explanation of what isn't working and provide a Minimal, Complete, and Verifiable example. Read How to Ask a good question. Be sure to take the tour and read this. — Jay Blanchard
Why would you want to - the second table seems to contain everything in the first table? — P.Salmon
i tried before to write some code i will edit my question and show u what i've done — Abdessamad Ben
@AbdessamadBen- Please clear one thing, whether you need last_name two times in new table and first_name only once? — Ishpreet

Ivo P Ivo P · Accepted Answer · 2020-06-15T12:08:38

no need for PHP in here. This can be done in SQL only.

INSERT INTO new_table (first_name, last_name, telephone, email)
SELECT first_name, lastname, telephone, email
FROM person_d;

And afterwards

INSERT INTO new_table (first_name, last_name)
SELECT first_name, lastname
FROM person
ON DUPLICATE KEY IGNORE;

This requires you make the combination of first and last name UNIQUE

You could of course change the names of the columns. And I suppose this is an exercise to do only once

Mysql/PHP Merge two tables

1 Answers