Jump to content

maybe I'm searching incorrectly?


phppup

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.

Link to comment
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.

Link to comment
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".

Link to comment
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?

Link to comment
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).

Link to comment
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.

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
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);
        }
    }
}            

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

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.