After insert how do I select the same rec. without knowing any rec. info

[robin01]

Hello,

I’m working on a project at work something similar to call tracking system.

Question: how do I retrieve the last record “I created” not the last record in the database more specifically the record index as I need to display the index (aka= also call tracking number) on the screen for the client. Keep in mind that multiple people use this application and they could also be inserting records to the database.

I’m able to insert a record but how do I quickly select the same record I created back as I wouldn’t know the record number(index) to search for?

using PHP and mysql.

Thank for the assistance
r

[SamA74]

@robin01

using PHP and mysql.

mysql isn’t in PHP anymore, perhaps you meant mysqli.
There is mysqli_insert_id
Or in PDO lastInsertId

[robin01]

Hi SamA74,

I have no way to test this but will this function still pull the last record “I inserted”?

scenario: 5 users using the same application to add records to the db and I’m one of the 5 users. I insert a record and milliseconds later user2 inserts a record and user3 and on and on. will this function still select the record I inserted or will it select the last record regardless to who inserted the record?

I need to select the record I added and not the last record.
according to php site “PDO::lastInsertId — Returns the ID of the last inserted row or sequence value”

or I’m I not understanding this?

[mabismad]

The value you get is per-connection/database-session. It is concurrent ‘safe’ and returns the last insert id for the insert/update query you last executed on the current connection.

[r937]

@robin01

I need to select the record I added

you can always query it back using the values submitted for certain selected columns

every table which uses an auto_increment column as the primary key will also have one or more columns that make up what’s known as a “candidate key” which could hvae been used as the primary key except the auto_increment was used instead

so let’s say a table looks like this –

CREATE 
 TABLE mytable
     ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
     , foo VARCHAR(9) NOT NULL
     , bar VARCHAR(37) NOT NULL
     , date_added DATE NOT NULL
     , added_by_user INTEGER NOT NULL REFERENCES users(id)
     , date_updated DATE NULL
     , updated_by_user INTEGER NULL REFERENCES users(id)
     )

a typical insert would look like this –

INSERT
  INTO mytable
     ( foo
     , bar
     , date_added
     , added_by_user )
VALUES
     ( 'happy'
     , 'as a pig in shit'
     , CURRENT_DATE
     , 937 )

once this row has been inserted, it gets a new auto_increment value for the id column, right?

well, you can retrieve that id value like this –

SELECT id
  FROM mytable
 WHERE foo = 'happy'
   AND bar = 'as a pig in shit'
   AND added_by_user = 937

the reason you can do this is because presumably the combination of foo, bar, and added_by_user are unique

note: candidate keys should always be declared as such with a UNIQUE constraint

ALTER
 TABLE mytable
ADD UNIQUE ( foo, bar, added_by_user )