Insert last added entry from a table into another table

Question

I have 3 tables: A, with positions; B, with references; and C where I do the relations between positions and references. Each position can have more than one reference, but the opposite isn't allowed. Okay. My routine: When the page that saves the relations between positions and references is loaded by submit buttom from the form, I do this: 1. See if this position exists If yes, go to 2. If no, display an error. 2. See if this reference exists. If yes, add the relation between this reference and this position. If no, add this reference and after, add the relation between this reference and this position.

This is my basic routine. My problem? When the reference doesn't exists, the script add a new reference and after, the relation between the new reference and the position... But it's doesn't working well. At first time, the script only adds the reference, but don't add the relation... I need back in the page and add again to work.

My code:

<?php
#verify if this position exists at db
if(!empty($_POST['position'])) {
    $query_position = "SELECT `PositionsId`, `PositionsIdentifier` from `positions` WHERE `PositionsId` = '".$_POST['position']."'";
    $select_position = mysqli_query($connect, $query_ref);
    #if yes, proceed to insert the new reference into this position...
    if (mysqli_num_rows($select_position)) {
        #verify if the reference exists at DB
        if(!empty($_POST['ref'])) {
            $query_ref = "SELECT `ReferencesId`, `ReferencesIdentifier` from `references` WHERE `ReferencesIdentifier` = '".$_POST['ref']."'";
            $select_ref = mysqli_query($connect, $query_ref);
            if (mysqli_num_rows($select_ref)) {
                $list_ref = mysqli_fetch_array($select_ref, MYSQLI_ASSOC);
                #verify if this reference exists to this position
                $query_reference = "SELECT `PositionRefId`, `PositionsRef_PosId`, `PositionRef_RefId` FROM `positions_refs` WHERE `PositionsRef_PosId` = '".$id."' AND `PositionsRef_RefId` = '".$list_ref['ReferencesId']."'";
                $select_reference = mysqli_query($connect, $query_reference);
                if (mysqli_num_rows($select_reference)) {
                    echo "This reference exists to this position...";
                }
                else {
                    #insert this reference to this position
                    $query_insert_reference = "INSERT INTO `positions_refs` (`PositionsRef_PosId`, `PositionRef_RefId`) VALUES ('".$id."','".$list_ref['ReferencesId']."')";
                    if (mysqli_query($connect, $query_insert_reference)) {
                        echo "OK.";
                    }
                    else {
                        echo "Error.";
                        echo "<br /><br />";
                        echo mysqli_error($connect);
                    }
                }
            }
            else {
                #if don't exists at db, insert the new reference
                if(!empty($_POST['ref'])) {
                    $query_insert_ref = "INSERT INTO `references`(`ReferencesIdentifier`) VALUES ('".$_POST['ref']."')";
                    if (mysqli_query($connect, $query_insert_ref)) {
                        #select the new reference ID
                        $query_new_ref = "SELECT `ReferencesId`, `ReferencesIdentifier` from `references` WHERE `ReferencesIdentifier` = '".$_POST['ref']." LIMIT 1'";
                        $select_new_ref = mysqli_query($connect, $query_new_ref);
                        if (mysqli_num_rows($select_new_ref)) {
                            $list_new_ref = mysqli_fetch_assoc($select_new_ref, MYSQLI_ASSOC);
                            #insert the new reference to this position
                            $query_insert_reference = "INSERT INTO `positions_ref`(`PositionsRef_PosId`, `PositionRef_RefId`) VALUES ('".$id."','".$list_new_ref['ReferencesId']."')";
                            if (mysqli_query($connect, $query_insert_reference)) {
                                echo "OK.";
                            }
                            else {
                                echo "Error.";
                            }
                        }
                        else {

                        }
                    }
                    else {
                        echo "Error.";
                    }
                }
                else {
                    echo "Error";
                }

            }
        }
        else {
            echo "Error";
        }
    }
}
else {
    echo "Error.";
} 
?>

I don't know another way to do this that works correctly... Have any way to do this: add the new reference into DB and after, without reload page, etc, add this new reference into the position?

Thanks,

Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use prepared statements with bound parameters, via either the [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) drivers. [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. — Alex Howansky, Oct 30 '17 at 22:31
I will be honest, your explanation of references and positions confuses me. But looking at your code, I noticed that your two insert statements do not insert in the same table...? INSERT INTO `positions_refs` .... The other INSERT INTO `variantsref`... is that normal? Final comment, you should look up transactions. If one of the 3 queries fails, you could break referential integrity (or is that taken care of at the db level?). And prepared statements! — Nic3500, Oct 30 '17 at 22:38
Hi, I fixed this, but it's the same table. I'll see the queries and displays errors if exists, but running this script, I see that if the reference doesn't exists, it's inserted but don't insert this new reference into the desired position... but when I back and try add again, the script insert this... I think that have a problem to get the id from last reference added to add the relation between this reference and the position, but I don't know if it's the problem. — Rafael, Oct 30 '17 at 23:42
About SQL Injection, I'm not worried because this script isn't public, I'm running to me and a colleague at localhost only. — Rafael, Oct 30 '17 at 23:48
Have you enabled warnings in your PHP installation? Because the code you posted have several errors that should throw at least a warning, for example you use `$list_doi['ReferencesId']` instead of `$list_ref['ReferencesId']` and in the other branch you use `$select_new_ref['ReferencesId']` instead of `$list_new_ref['ReferencesId']` — Alberto Martinez, Oct 30 '17 at 23:50
Fixed this issues now. I posted a little old version of my code because have some things that doesn't matter for this post and situation in new version... and on this version haven't. The original problem in both versions are the same and persists... — Rafael, Oct 31 '17 at 00:43
Have you tried the [mysqli_insert_id](http://php.net/manual/en/mysqli.insert-id.php) function to see if that might help to get the IDs that you need after the `INSERT`s? — Paul T., Oct 31 '17 at 02:43

score 0 · Accepted Answer · answered Oct 31 '17 at 14:13

0

If you want the last inserted id then you can use mysqli_inserted_id

mysqli_insert_id ($link)

Link : http://php.net/manual/en/mysqli.insert-id.php

answered Oct 31 '17 at 14:13

Channaveer Hakari

2,338
1
26
34

Insert last added entry from a table into another table

1 Answers1