Mysql add primary key from a table to others two tables

Question

I have three tables (Table A, Table B, Table C).

I want to add the primary key from "table A" into columns of "tables B, C", with one submit buttons.

The tables B, C, have there owns primaries keys.

**|table A|**
|primary key|Column 1|Column2|
|    AI     |  test  | test  |
------------------------------

**|table B|**
|Primary key|           Column 1         |Column 2|
|    AI     |Add Primary key from table A|  test  |
----------------------------------------------------

**|table C|**
|Primary key|           Column 1         |Column 2|
|   AI      |Add Primary key from table A|  test  |

As you see i want in column 1, in tables B and C, add the primary key from table A. This is my php code that i used:

$sql = "INSERT INTO `table A` (Column 1, Column 2)VALUES(test, test')";
$sql_B = "INSERT INTO `table B` (Column 1, Column 2)VALUES(LAST_INSERT_ID(), test)";
$sql_C = "INSERT INTO `table C` (Column 1, Column 2)VALUES(LAST_INSERT_ID(), test)";

The problem is that, $sql works, $sql_B works (takes the LAST_INSERT_ID() from previous INSERT, in table A), but the third query take the LAST_INSERT_ID() from table B, which is normal, but its not that i want. Is there any way to assign LAST_INSERT_ID() in a variable and add this variable in sql query.

Any opinion will be fine.

you can check https://stackoverflow.com/questions/5157905/mysql-query-result-in-php-variable on how to save the result of a query into a variable — TheOni, Mar 08 '18 at 10:02

Harrison Ifeanyichukwu · Answer 1 · 2018-03-08T13:34:35.503

from the code you have, i will make changes. copy the code below to replace your final code

if(isset($_POST['create_record'])){
// αντιστοίχηση πεδίων με τις μεταβλητές για τον πίνακα 'truck_records'
$user_id = mysqli_real_escape_string($connection, $_POST['user_id']);
$user_ip = mysqli_real_escape_string($connection, $_POST['user_ip']);
$company_name = mysqli_real_escape_string($connection, $_POST['company_name']);
$nick_enable = mysqli_real_escape_string($connection, $_POST['nick_enable']);
$company_nick_name = mysqli_real_escape_string($connection, $_POST['company_nick_name']);
$driver_kind = mysqli_real_escape_string($connection, $_POST['driver_kind']);
$driver_name = mysqli_real_escape_string($connection, $_POST['driver_name']);
$invoice = mysqli_real_escape_string($connection, $_POST['invoice']);
$shipping_note = mysqli_real_escape_string($connection, $_POST['shipping_note']);
$invoice_number = mysqli_real_escape_string($connection, $_POST['invoice_number']);
$invoice_amount = mysqli_real_escape_string($connection, $_POST['invoice_amount']);
$comments = mysqli_real_escape_string($connection, $_POST['comments']);
// αντιστοίχηση πεδίων με τις μεταβλητές για τον πίνακα 'truck_weights'
$weight_tr_full = mysqli_real_escape_string($connection, $_POST['weight_tr_full']);
$weight_tr_empty = mysqli_real_escape_string($connection, $_POST['weight_tr_empty']);
$weight_product = mysqli_real_escape_string($connection, $_POST['weight_product']);
$weight_tr_full_date = mysqli_real_escape_string($connection, $_POST['weight_tr_full_date']);
$weight_tr_empty_date = mysqli_real_escape_string($connection, $_POST['weight_tr_empty_date']);
// αντιστοίχηση πεδίων με τις μεταβλητές για τον πίνακα 'material_weight'
$material_1 = mysqli_real_escape_string($connection, $_POST['material_1']);
$material_wei_1 = mysqli_real_escape_string($connection, $_POST['material_wei_1']);
$material_2 = mysqli_real_escape_string($connection, $_POST['material_2']);
$material_wei_2 = mysqli_real_escape_string($connection, $_POST['material_wei_2']);
$material_3 = mysqli_real_escape_string($connection, $_POST['material_3']);
$material_wei_3 = mysqli_real_escape_string($connection, $_POST['material_wei_3']);
$material_4 = mysqli_real_escape_string($connection, $_POST['material_4']);
$material_wei_4 = mysqli_real_escape_string($connection, $_POST['material_wei_4']);
$material_5 = mysqli_real_escape_string($connection, $_POST['material_5']);
$material_wei_5 = mysqli_real_escape_string($connection, $_POST['material_wei_5']);
$material_6 = mysqli_real_escape_string($connection, $_POST['material_6']);
$material_wei_6 = mysqli_real_escape_string($connection, $_POST['material_wei_6']);
$material_7 = mysqli_real_escape_string($connection, $_POST['material_7']);
$material_wei_7 = mysqli_real_escape_string($connection, $_POST['material_wei_7']);
$material_8 = mysqli_real_escape_string($connection, $_POST['material_8']);
$material_wei_8 = mysqli_real_escape_string($connection, $_POST['material_wei_8']);
$material_9 = mysqli_real_escape_string($connection, $_POST['material_9']);
$material_wei_9 = mysqli_real_escape_string($connection, $_POST['material_wei_9']);
$material_10 = mysqli_real_escape_string($connection, $_POST['material_10']);
$material_wei_10 = mysqli_real_escape_string($connection, $_POST['material_wei_10']);

$sql_record = "INSERT INTO `truck_records` (
user_id, user_ip, company_name, nick_enable, company_nick_name, driver_kind, driver_name, 
invoice, shipping_note, invoice_number, invoice_amount, comments
)VALUES(
'$user_id', '$user_ip', '$company_name', '$nick_enable', '$company_nick_name', '$driver_kind', '$driver_name', 
'$invoice', '$shipping_note', '$invoice_number', '$invoice_amount', '$comments')";

$sql = new mysqli($host, $username, $password, $database_name); //use this connection here. asign your host, username, password, database_name to the the variables.

$result_record = $connection->query($sql_record);
$last_insert_id = $connection->insert_id;

$sql_weight = "INSERT INTO `truck_weights` (
truck_weight_record_id, 
user_id,
weight_tr_full,
weight_tr_empty,
weight_product,
weight_tr_full_date,
weight_tr_empty_date
)VALUES(
'$last_insert_id',
'$user_id', 
'$weight_tr_full', 
'$weight_tr_empty', 
'$weight_product', 
'$weight_tr_full_date', 
'$weight_tr_empty_date')"; 

$connection->query($sql_weight);

$sql_material = "INSERT INTO `material_weight` (
mat_record_id,  
material_weight,
material_name
)VALUES(
'$last_insert_id', 
'$material_wei_1',
'$material_1')";

$connection->query($sql_material);

if($result_record){
    $smsg = 'WORK. ';
}else{
    $fmsg = 'NOT WORK. ';
}
echo $fmsg . "<br>";
echo $sql_record . "<br>";
echo $sql_weight . "<br>";
echo $sql_material . "<br>";

}

its return this error:Fatal error: Call to undefined method mysqli::execute() — alexikakos, Mar 08 '18 at 10:44
yes i replace $conn with $connection (my $conn), i dont used $conn = new mysqli("host", "username", "password", "database_name"); — alexikakos, Mar 08 '18 at 11:26
and $last_insert_id work the only problem is that i have double insert in table_A — alexikakos, Mar 08 '18 at 11:27
clear the whole tables, probably recreate the tables, and test it. and let me know if the insert is doubled. — Harrison Ifeanyichukwu, Mar 08 '18 at 11:32
check well to see if you are executing the $sql statement twice. it should be executed once along with the $sql_B and $sql_C — Harrison Ifeanyichukwu, Mar 08 '18 at 11:33
i checked, when i remove the firts three lines ($stmt = $conn->prepare($sql); $stmt->execute(); $last_insert_id = $conn->insert_id;) i have no double insert, but of course not working $last_insert_id... — alexikakos, Mar 08 '18 at 11:59
thnx for help me but, with the new code i have double insert and in tabbles B and C — alexikakos, Mar 08 '18 at 12:35
i dont use all your code, i dont use ($conn = new mysqli("host", "username", "password", "database_name");) is tha right? — alexikakos, Mar 08 '18 at 12:37
can you past the your code as comment here. dont show your connection password here while pasting please. — Harrison Ifeanyichukwu, Mar 08 '18 at 12:47
i have updated it. so copy and test. use the connection i wrote there.just assign your host to the $host variable, and likewise for others. — Harrison Ifeanyichukwu, Mar 08 '18 at 13:36

score 0 · Answer 2 · answered Mar 08 '18 at 10:25

0

You could try something like this for last table:

$sql_C = "INSERT INTO `table C` (Column 1, Column 2)VALUES((SELECT Primary key FROM `table A`), test)";

answered Mar 08 '18 at 10:25

Zivan Radosavljevic

13
4

Sorry but not insering nothing in table_B, table_C. – alexikakos Mar 08 '18 at 10:48

score 0 · Answer 3 · answered Mar 08 '18 at 10:55

0

In php there is a function for inserting last id, you can use this

$conn is your connection variable

$last_id = mysqli_insert_id($conn);

answered Mar 08 '18 at 10:55

lopamudra

49
9

this return "0" in columns_1. – alexikakos Mar 08 '18 at 11:12
when i echo $last_id; its "0" to – alexikakos Mar 08 '18 at 11:13
Yes after first query (for table_A) and before $sql_B, $sql_C. correct? – alexikakos Mar 08 '18 at 11:29
and i replaced $conn with $connection (my conect object) – alexikakos Mar 08 '18 at 11:29
Is your first table's column ID set with auto increment ? – lopamudra Mar 08 '18 at 11:47

score 0 · Answer 4 · answered Mar 09 '18 at 07:19

That s the solution that i used:

$sql = "INSERT INTO `table A` (Column 1, Column 2)VALUES(test, test')";
$sql_B = "INSERT INTO `table B` (Column 1, Column 2)VALUES((select MAX(id) FROM table A), test)";
$sql_C = "INSERT INTO `table C` (Column 1, Column 2)VALUES((select MAX(id) FROM table A), test)";

With this way i have the last id from specific table A, (max id) is alway the last id of table A

Mysql add primary key from a table to others two tables

4 Answers4