1

SITUATION: I have 3 tables: people (peopleID, firstName, lastName), phoneNumbers (phoneID, countryCode, areaCode, phoneNumber, extension) and peoplePhoneNumbers (peopleID, phoneID) (which is a junction table with foreign keys for manyTOmany relation).

Everything is updated using one form.

GOAL: Let's say I have: John Smith 201 1111111 23, if I enter: Paul Lennon 201 1111111 23 (same phone number) the system should: 1) create a new row in table people (with new ID) 2) DON'T create a new row in table phone and return the ID already used for that same phone number. (2 phone number to be the same they should have same area code, same number and same extension).

This is the code I have so far:

    mysqli_query($link, "SET AUTOCOMMIT=0") or die(mysqli_error($link));
    mysqli_query($link,"START TRANSACTION") or die(mysqli_error($link));
    $sql1 = "INSERT INTO tbl_people (firstname, lastname)
        VALUES ('$firstname', '$lastname');";

    $sql2 ="INSERT INTO tbl_PhoneNumbers (AreaCode, PhoneNumber)
        VALUES ('$areamobile', '$mobile');"; 

    mysqli_query($link, $sql1) or die('Error: ' . mysqli_error($link));
    $last_people_id = mysqli_insert_id($link);

    mysqli_query($link, $sql2) or die('Error: ' . mysqli_error($link));
    $last_phone_id = mysqli_insert_id($link);       

    $sql3 ="INSERT INTO tbl_peoplephonenumbers (peopleID, phoneID)
        VALUES ('$last_people_id', '$last_phone_id');"; 
    mysqli_query($link, $sql3) or die(mysqli_error($link));

    header("Location: index.php?act=edit&people_id=$last_people_id"); /*after adding new person goes to edit page*/
    mysqli_query($link, "COMMIT")  or die(mysqli_error($link));
    mysqli_query($link, "SET AUTOCOMMIT=1") or die(mysqli_error($link));
Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
codeispoetry
  • 373
  • 2
  • 13
  • 3
    A bit of a side comment... You probably *don't* want to do this. A phone number is a value, not an entity. What if two people share a phone number, but then at a later time one of them changes it for only himself? In this design, he would be editing someone else's phone number as well. Does the system have some way of knowing when to edit the record vs. when to create a new one? The user really shouldn't have to specify that. Duplicating a phone number (a simple value) is a whole lot easier/cleaner than trying to clean up over-normalized data. – David Jan 08 '16 at 17:29
  • 1
    [Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Jan 08 '16 at 17:31
  • Thanks David! You suggest to just duplicate phone numbers, so Smith and Lennon will have have same phone numbers, but different phoneID. – codeispoetry Jan 08 '16 at 17:36
  • Why don't you just query the database first to check if a record exists which matches your criteria - if it doesn't then create a new one. – MistaJase Jan 08 '16 at 17:36
  • Thanks Jay, you are right, but I'm the only one using it and it will stay on a localhost. But I will for sure read your link for future reference! Thanks again! – codeispoetry Jan 08 '16 at 17:38
  • Thanks Jason, can you give me a direction of how to do that? – codeispoetry Jan 08 '16 at 17:40
  • @codeispoetry: Correct, duplicating data is ok in this case. Because a phone number isn't *itself* a unique entity. It's simple a value, an attribute which describes an entity (in this case a "person"). People can have duplicate values for their attributes. (For example, if two people have brown hair, you wouldn't create a single "Brown" record and link those two people to it. You'd just record that value for both people.) – David Jan 08 '16 at 17:44
  • @David Thanks! Than there is no need for a junction table. I can just use a foreign key from table phoneNumbers in table people... – codeispoetry Jan 08 '16 at 17:49
  • @David Hi David, I've asked a question about database structure, but I didn't get any answer. If you have two minutes, could you please get a look at it ad give me your input? Thanks for your help! [http://stackoverflow.com/questions/34981660/database-design-class-level-topics-marks] – codeispoetry Jan 25 '16 at 03:59

0 Answers0