How to take the id of a table and copy it to another adding value to the other fields-CodePudding

I have 2 tables MySql: **table1** **table2** I have to take the value of the ID field from table1 and bring it into the user_id field of table2. But not only. In table2 the record must be composed as follows. user_id= value taken from table1 field2=fixed value field3=fixedvalue2 I tried to use INSERT INTO with SELECT for PHP script but couldn't find the solution.

CodePudding user response：

To take the ID value from table1 and insert it into the user_id field of table2, along with the fixed values for field2 and field3, you can use the following SQL query:

INSERT INTO table2 (user_id, field2, field3)
SELECT id, 'fixedvalue1', 'fixedvalue2'
FROM table1

Make sure to replace 'fixedvalue1' and 'fixedvalue2' with the actual values that you want to insert into field2 and field3.

Alternatively, you can use a PHP script to execute this query, like so:

<?php
// Connect to the database
$db = mysqli_connect('hostname', 'username', 'password', 'database_name');

// Define the query
$query = "INSERT INTO table2 (user_id, field2, field3)
          SELECT id, 'fixedvalue1', 'fixedvalue2'
          FROM table1";

// Execute the query
mysqli_query($db, $query);

// Close the connection
mysqli_close($db);

Make sure to replace the placeholder values in the code with the actual values for your database connection.