How to insert two records into different tables first one with insert and then with select from the insert php mysql

Question

My question is not easy to explain for myself, but I will train.

I can insert a record into a table´s column form a form with php to mysql without problems. This insert generates an auto_increment record in another column from the same table.

So I want to select this new auto_increment record and insert it in another table.

<?php
            // process form`enter code here`
            $db_host="myhost";
            $db_user="myuser";
            $db_password="mypassword";
            $db_name="mydbname";
            $db_table_name="table1";
            $db_table_name2="table2";

            $record1= utf8_decode($_POST['record1']);
            $record2= utf8_decode($_POST['redord2']);

            $db_connection = mysqli_connect($db_host,$db_user,$db_password,$db_name);

            if (!$db_connection) {
                die('Could not connect to the database');
            }
            //insert record1 into table1
            $insert_value = 'INSERT INTO `' . $db_name . '`.`'.$db_table_name.'` (`name`) VALUES ("' . $record1 . '")';

            $result1 = $db_connection->query($insert_value);

            //consult auto_increment column from table1
            $select_value = 'SELECT column FROM table1 WHERE name = "' . $record1 . '"';

           //insert record2 into table2 in a row where a column is like $record1 
            $insert_value2 = 'INSERT INTO `' . $db_name . '`.`'.$db_table_name2.'` (`column1, column2`) VALUES ("' . $record2. '","' . $select_value  . '") WHERE name = "'.$db_table_name2.'"';

            $result2 = $db_connection->query($select_value);

            mysqli_close($db_connection);

        ?>

are you referring to the last inserted id? check this link http://php.net/manual/en/mysqli.insert-id.php — roullie, Sep 14 '15 at 08:51

score 1 · Accepted Answer · edited May 23 '17 at 11:59

1

$query="Insert Statement";
$RunQuery = $db_connection->prepare($query);

If you're using PDO, it's PDO::lastInsertId(). So if your database handle is called $link:

$LastID = $db_connection->lastInsertId();

Or, If you're using MySQLi, it's mysqli::$insert_id or mysqli_insert_id():

$LastID = $db_connection->insert_id;

Now, Use this $LastID in next Query.

For more info, check mysql_insert_id, mysql_query in PDO

$insert_value2 = 'INSERT INTO `' . $db_name . '`.`'.$db_table_name2.'` (`column1, column2`) VALUES ("'.$record2.'","'.$LastID.'") WHERE name = "'.$db_table_name2.'"';

$result2 = $db_connection->query($insert_value2);

edited May 23 '17 at 11:59

Community

1
1

answered Sep 14 '15 at 09:44

Nana Partykar

10,556
10
48
77

Danish Enam, Thank you so much. With your answer I check mysql_:insert_id is working. But my main problem is when I want to use $LastID to insert int the table2. I can´t – Acicate Sep 14 '15 at 09:50
because, you are using $result2 = $db_connection->query($select_value); $select_value here. USE $insert_value2 inside like this query($insert_value2); @Acicate – Nana Partykar Sep 14 '15 at 09:53
Check My Edited Answer Mr @Acicate – Nana Partykar Sep 14 '15 at 10:01
I try to use this, but doesn´t work: $insert_value2 = 'INSERT INTO `' . $db_name . '`.`'.$db_table_name2.'` (`column1, column2`) VALUES ("' . $record2 . '","' . $LastID . '" ) WHERE name = "'.$db_table_name2.'"'; $db_connection->query($insert_value2); – Acicate Sep 14 '15 at 10:05
@Acicate Copy This exactly code which i've given now and replace your existing code $insert_value2 = "INSERT INTO '.$db_name.'.'.$db_table_name2.'(column1, column2) VALUES ('".$record2."','".$LastID."') WHERE name = '".$db_table_name2."'"; – Nana Partykar Sep 14 '15 at 10:09
Is not working :(. When I update the tables on Mysql I can see only the new record in the table1, but no in the table2 – Acicate Sep 14 '15 at 10:22
check here $record2= utf8_decode($_POST['redord2']); Check in your code, is redord2 or record2. Which is correct. $record2= utf8_decode($_POST['record2']); @Acicate $_POST['redord2'] or $_POST['record2' ?? – Nana Partykar Sep 14 '15 at 10:27
There is mistake in your code only. never write confusing codes/query @Acicate – Nana Partykar Sep 14 '15 at 10:29
You was right!! Thank you. But In the case I wanted to use my single query result like a $variable. How can I do that? – Acicate Sep 15 '15 at 07:01
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/89643/discussion-between-nana-partykar-and-acicate). – Nana Partykar Sep 15 '15 at 07:11

score 0 · Answer 2 · answered Sep 14 '15 at 09:04

0

The mysqli_insert_id() function returns the ID generated by a query on a table with a column having the AUTO_INCREMENT attribute.

If the last query wasn't an INSERT or UPDATE statement or if the modified table does not have a column with the AUTO_INCREMENT attribute, this function will return zero.

For example,

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
$mysqli->query($query);
echo $mysqli->insert_id;

answered Sep 14 '15 at 09:04

Insane Skull

9,220
9
44
63

Insane Skulll, Thank you so much. With your answer I check mysql_:insert_id is working. But my main problem is when I want to use $LastID to insert int the table2. I can´t – Acicate Sep 14 '15 at 09:51
this will give you last inserted id, and u can use it for second query. – Insane Skull Sep 14 '15 at 09:54

How to insert two records into different tables first one with insert and then with select from the insert php mysql

2 Answers2