Arkadiusz G. Arkadiusz G. - 5 months ago 8
MySQL Question

Mysql Two tables mixed data

I have two tables:

table1:

ID| Name
1 | firstname1
2 | firstname2
3 | firstname3


table2:

ID| Name
1 | lastname1
2 | lastname2
3 | lastname3


And i want insert combination table1 and table2 to table3 example:
table3:

ID| Fullname
1 | firstname1-lastname1
2 | firstname1-lastname2
3 | firstname1-lastname3
4 | firstname2-lastname1
5 | firstname2-lastname2
6 | firstname2-lastname3
7 | firstname3-lastname1
8 | firstname3-lastname2
9 | firstname3-lastname3


A try in php (pdo) but is very slow... i must create 10 000 000 records (after combination)

my php:

<?php
//get firstnames, get lastnames before and...
foreach ($firstnames as $firstname) {
foreach ($lastnames as $lastname) {
$this->pdo->prepare("INSERT INTO `table3` (`fullname`) VALUES(?)")->execute([$firstname['name'] . '-' . $lastname['name']]);
}
}


Soo, how i can create a query that mix data?

Thans

Answer

Use below SQL to insert data in table3

  INSERT INTO table3(fullname ) SELECT concat( table1.name,'-',table2.name ) FROM table1 CROSS JOIN table2;