maybe I'm searching incorrectly?

[phppup]

I don't mean to bother you with silly questions, but I must be using the wrong search terms to find any viable answers to this.

Hypothetical situation:  I have a table [Customers] with with an auto-increment id, customer_name and email generated from a form.

Now, I decide to expand my database to include customer-address and telephone number.

I decide to create a second table named Contactinfo for this purpose (assuming this is a better database practice).

As Customerinfo is generated, I want it to POST in association with the respective customer (assuming that 'id' is the best field for association).

I need some examples and best practices to accomplish this.

Examples, links, or preferred terminology would be great so I can attack this phase.

Thanks.

[Barand]

Insert customer record

Get its ID using last_insert_id()

Insert contactinfo with that id as a foreign key.

Edited February 8, 2019 by Barand

[phppup]

I'll research this.

Do the ID's then become 'attached'?

Am I better off creating a single form that requests ALL the info and delegating it to post accordingly?

[Barand]

21 minutes ago, phppup said:

Do the ID's then become 'attached'?

There won't be a piece of string stretched between them but as they will have the same value you can use them in a join.

If they aren't in the same form you don't need to use last_insert_id as the customer will be pre-existing when you add the contact info.

[ginerjm]

Seems like the correct solution would be to add these new items to the original table  - it already has their name and email (contact) info so you simply need to expand that and not create a new table for essentially "more contact info".

[Barand]

There is a need for a separate table if you need multiple contact info for a customer (EG different office locations or business/home contact details)

[phppup]

Fought with code until I eventually put this after my INSERT statement:

if (mysqli_query($conn, $sql)) {
    $last_id = mysqli_insert_id($conn);
    echo "New record is: " . $last_id;
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

I finally got the ID value, except all entries into the table are being duplicated from each single form submission.

It appears that this is because $sql is being called twice.  I even tried to put this into a function (didn't execute at all).

How do I get to a single table posting?

[phppup]

More info.....

Here is my original code that works well PRIOR to attempting to acquire the LAST INSERT ID:

$query  = "SELECT * FROM table WHERE email='$email' ";   //checks that email is unique
$result = mysqli_query($conn, $query);                   //RUNS the above query
if (mysqli_num_rows($result) == 0) {                     //if no duplicates exist, then INSERT

$sql="INSERT INTO $table..... etc etc

}

So the question remains, how do I retrieve the LAST INSERT ID without having duplicate entries into my table, and without restructuring my code too drastically (as to affect other portions that use the same CONN).

[kicken]

If you're data is getting duplicated then you're calling mysqli_query twice with your insert query.  mysqli_insert_id doesn't cause duplication, it just returns the id generated by the previous insert query.

Also, if you want to prevent duplicate email addresses you should have a UNIQUE constraint on that column, not query the table first.

[phppup]

You are correct. Located the problem and remedies it.

So, if I make the EMAIL column unique, how would I trigger an error message to the user when a duplicate email is entered, without querying the table?

[ginerjm]

Uh... without querying the table?? 

 

If you make the email column the primary key and add the "on duplicate key" clause to your query statement you can recognize the dupe when the query is attempted. Or you can do a test query to look for the email before adding the new record.  But - without doing either of these you'll never determine a dupe exists.

[Barand]

2 hours ago, phppup said:

So, if I make the EMAIL column unique, how would I trigger an error message to the user when a duplicate email is entered, without querying the table?

like this

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);    // TELL MYSQLI TO THROW EXCEPTION
$db = mysqli_connect(HOST,USERNAME,PASSWORD,'test');
//
// CREATE SOME TEST DATA OF EXISTING USERS
//
        $db->query("DROP TABLE IF EXISTS testuser");                                               
        $db->query("CREATE TABLE testuser (                                                        
                    id int not null auto_increment,
                    username varchar(20),
                    primary key (id),
                    unique (username)
                    )
                    ");
        $db->query("INSERT INTO testuser (username) VALUES ('curly'), ('larry'), ('mo')");

//
//  NOW WE ADD A NEW USER
//
if (isset($_POST['username'])) {
    $stmt = $db->prepare("INSERT INTO testuser (username) VALUES (?)") ;
    $stmt->bind_param('s', $_POST['username']);
    try {
        $stmt->execute();
    }
    catch (mysqli_sql_exception $e) {
        if ($e->getCode() == 1062)               // is it a duplicate key error?
            echo "That username already exists";
        else {
            throw ($e);
        }
    }
}