Jump to content
phppup

maybe I'm searching incorrectly?

Recommended Posts

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.

Share this post


Link to post
Share on other sites

Insert customer record

Get its ID using last_insert_id()

Insert contactinfo with that id as a foreign key.

Edited by Barand

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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".

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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);
        }
    }
}            

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.