How to retrieve newly stored data in database table within the script of inserting it?

Question

I'd like to ask. I have two tables in a database. Here's the case, I have a form to get all data needed to be stored. Some of these data should have to be stored in one table, and some to the other. But, the ID of the data in one table should also be stored in another table for retrieving purposes. For example, in table 1 I have ID, this ID at table one should also be stored at the same time in table 2 as T2_ID, the moment the user pressed the Submit button, the data should automatically be stored evenly according to its purpose. However, in my case, only those data for table 1 are stored because I couldn't retrieve the ID from table 1 after it has been saved.

Here's my code:

if (mysqli_query($connect, $sql)) {
    $getinfo = "SELECT id FROM names WHERE firstname='$firstname' AND lastname='$lastname'";
    $query = mysql_query($getinfo, $connect);
    $row = mysql_fetch_array($query);
    $forid = $row['id'];
    $sql2 = "INSERT INTO phonenumbers (name_id, type, number) 
            VALUES ('$forid', '$contacttype', '$newnum')";

    if (mysqli_query($connect, $sql2)) {
        echo "<script>alert('New record added successfully!');</script>";
        header('Location: index.php');exit();
    }
    else {
        echo "<script>alert('Error: " . $sql2 . "<br>" . mysqli_error($connect) . "');</script>";
        header('Location: index.php');exit();
    }

}
else {
    echo "<script>alert('Error: " . $sql . "<br>" . mysqli_error($connect) . "');</script>";
    header('Location: index.php');exit();
}

The mysql_* functions are deprecated in PHP 5 and COMPLETELY REMOVED in PHP 7. You should switch to a properly maintained SQL interface layer such as mysqli or PDO, even if you don't intend to move to PHP 7 soon the mysql_* functions are not maintained and are an unacceptable security risk. They can also lead to data corruption. Also, you're mixing mysql_* and mysqli_* which can never work. — GordonM, May 05 '17 at 08:28
Following @GordonM comments - please direct yourself [here to read about PDO](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496#60496) — Tom, May 05 '17 at 08:29

score 0 · Answer 1 · answered May 05 '17 at 08:32

0

you can get last inserted id of your query

$forid = mysqli_insert_id($connect);
$sql2 = "INSERT INTO phonenumbers (name_id, type, number) 
        VALUES ('$forid', '$contacttype', '$newnum')";

answered May 05 '17 at 08:32

Chinito

1,085
1
9
11

Thanks a lot! I really got it! – JHON CARL IGNORO May 05 '17 at 08:41

score 0 · Answer 2 · answered May 05 '17 at 08:33

you need to use insert select ..

after your query

 INSERT INTO tbl1 (column1, column2) 
 VALUES ('$val1', '$val2');

you should query another like

 INSERT INTO tbl2 (id, tbl2column, tbl2anothercolumn)
 SELECT id, $anotherval as 'whatever' , $anotherval2 as 'anything' FROM tbl1 WHERE column1 = '$val1' and column2 = '$val2'

How to retrieve newly stored data in database table within the script of inserting it?

2 Answers2