0

I´m facing quite interesting problem IMO. I´m working with MySQL & PHP and I have created two tables - 1. containing ID | Firstname | Lastname ; 2. containing ID | Phone Number. What I want to do is following: User can create a new entry in both tables. I want to achieve the state where in the second table (containing ID and Phone Number) there will be phone number added by the user and the ID of this phone number will match the ID of the person in the first table who the number belongs.

Let me give You an example:

#1 Table:

ID    FirstName    LastName
1      John         Cena
2      Tom          Jerry

#2 Table:

ID    PhoneNumber
1      123456
1      654321

=> John Cena with the ID of "1" will have in the second table two numbers.

What I´m struggling to do is the PHP function which will return the ID from the first table (named "People") based on firstname and lastname and then insert this value into the second table (named "PhoneNums").

At the moment I have this:

function GetUserID($firstname, $lastname) {
        $conn = OpenCon();
        $stmt = $conn->prepare("SELECT userid FROM People WHERE firstname='$firstname' AND lastname='$lastname'");
        $stmt->execute();
    }


function AddNum($phonenum, $firstname, $lastname) {
        try {
          $conn = OpenCon();
          $user_id = GetUserID($firstname, $lastname);
          $sql = "INSERT INTO PhoneNums (userid, number)
          VALUES ('$user_id', '$phonenum')";

          $conn->exec($sql);
          echo "New record added successfully";
        } catch(PDOException $e) {
          echo "<br>" . $e->getMessage();
        }
    }

Do You have any idea how to do it or how to correct my solution? :D

Dandys
  • 47
  • 1
  • 4
  • Does this answer your question? [PDO get the last ID inserted](https://stackoverflow.com/questions/10680943/pdo-get-the-last-id-inserted) – ADyson Mar 17 '22 at 20:13
  • 1
    Ps. There's no point using prepared statements unless you also use parameters with them. Your code is still vulnerable to sql injection attacks. Look over some examples to see the use of parameters – ADyson Mar 17 '22 at 20:14
  • @ADyson You are making the assumption that column `id` is an auto_increment column. While it very well may be, it might be too great an assumption to issue a "close as duplicate" vote. **The real problem, however, is**, that even if it is an auto_increment column, you have to issue the call to LAST_INSERT_ID following an insertion/update into the PEOPLE table to get the last id inserted into that table. But here we are talking about getting the id given a FirstName and LastName (which should combine to form a unique key). That seems to render LAST_INSERT_ID irrelevant in any case. – Booboo Mar 17 '22 at 20:46
  • 1
    You should only be trying to retrieve the `id` based on a first and last name if you had a guarantee that the combination of these two columns was unique in the `People` table. This could be ensured by adding a unique key constraint on the two combined columns `FirstName` and `LastName`. But in real life organizations can have multiple people with the same name and you would *not* want to do that. – Booboo Mar 17 '22 at 20:57

0 Answers0