0

Maybe not the best topic, but I am trying to make a simple example out of this in order to explain it more clearly. My tables in the example are:

CREATE TABLE users_logindata (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(250) UNIQUE DEFAULT NULL,
  reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE user_info(
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_user INT,
    adress VARCHAR(255),
     name VARCHAR(255),
    country VARCHAR(255),
    city VARCHAR(255),
    postal_place VARCHAR(255),
    zip_code VARCHAR(255)
);

I have one table that contain the login data of the user, and another table that hold the "extra" data the user might fill in if he wants to. But all can be filled in using one signup-form!

The script first create the user by adding the given data (required) to a database. In order to get the id that references to the user_info table (that store the extra data )i need to get the id from the 'users_logindata'. And use the id as reference inside the 'user_info' table.

The code to get the last id:

 /* get last entry in db*/
    public function getLastEntry($tablename){

         if ($result = $this->connect()->db_connection->query("SELECT id FROM $tablename ORDER BY id DESC LIMIT 1" )) {

            while ($o = $result->fetch_object()) {
                return $o->id;
            }
        }
    }

Steps would be something like:

1) add the user to 'users_logindata'

2) get the id by selecting the last id from 'users_logindata'

3) Use the value from step 2 and add it into the 'user_info => id_user' table.

Then my question is: if i have alot of users signing up at the same time, is it then possible that the id might be mixed up? since one user can reg between the adduser query (step1) and the fetching of the id (step2). Since im getting the last ID, that actually might be another id since there are more signing up at once?, or does PHP handle one .php file at a time?

sdfgg45
  • 1,232
  • 4
  • 22
  • 42
  • Yes that is entirely possible. Never rely on the `last_id` functions. It's not `PHP` here that's being handled one file at a time that is the issue, it's MySQL which would be unreliable in its results in this case. – Ohgodwhy Apr 09 '16 at 19:43
  • What would be an alternative to this? since the sql tables tend to get rather big and messy by putting in all the data in one table. – sdfgg45 Apr 09 '16 at 19:45

2 Answers2

1

Your question is kind of a mixup of a lot of things. Your web server config decides how many requests etc are handled.

If you're using MySQL/MariaDB then the "last insert id" function should be for the current connection only. Since you create a new connection on each request this should be perfectly fine to do.

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis.

http://dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html

JimL
  • 2,501
  • 1
  • 19
  • 19
  • I added my code that fetch the id in step 2. (custom function to get the last id in table) all runs in same .php file as stated abaue. not sure if this changes the awnser. – sdfgg45 Apr 09 '16 at 19:52
  • As long as you insert / check the last insert id on the same connection (same request) you should be fine. Doesn't matter which file it's in. – JimL Apr 09 '16 at 19:53
  • I supose you are right, was my thought upon this as well, but wanted to check with others before proceed. – sdfgg45 Apr 09 '16 at 19:57
  • http://stackoverflow.com/questions/1685860/how-do-i-get-the-last-inserted-id-of-a-mysql-table-in-php#comment17201885_1685867 – JimL Apr 09 '16 at 19:59
  • Thanks, ill check into that! – sdfgg45 Apr 09 '16 at 20:09
0

I recommend storing that data in a single table as it doesn't seem that much information to split up into multiple tables.

  1. Check if user with that email already exists
  2. If it doesn't then register the user
  3. If you decide to use two tables then use email address to retrieve the ID for that user from the first table
dchayka
  • 1,291
  • 12
  • 20