I want to get last id in mysql and php

Question

hi i want to get last id from my table then i will put it in registration form with +1 mean's +1 id will be automatically generated when any form inserted i'm doing this because i have 4 user's i want that id no of registration forms starts from 1 for every user

SELECT * 
FROM registration 
WHERE id=(SELECT MAX(id) FROM registration);

i use this code but i'm unable to add AND condition i need something like

SELECT * 
FROM registration 
WHERE id=(SELECT MAX(id) FROM registration) 
AND project_name='test2';

this mean i want to add AND condition where it will check this project_name last id

Question is far from clear. Do you get an error message? If so please show us! — RiggsFolly, Feb 14 '23 at 15:27
Hey Muhammad! Welcome to StackOverflow - I'd like to know which database driver you're using? If you're using PDO - there is a built in function to get your LastInsertId - if the id on your table is auto-incrementing you should be able to leverage this instead of doing your own query. — Ryan, Feb 14 '23 at 15:29
Assuming there is a column called `project_name` in the `registration` table the second query shoudl be perfectly ok? You are going to have to explain more about the issue — RiggsFolly, Feb 14 '23 at 15:29
@Ryan I have a feeling the `last id` part of this question is more than a little misleading. As there is no INSERT anywhere to be seen I assume the OP means the Last `id` on the table rather than the last `id` created by the last INSERT — RiggsFolly, Feb 14 '23 at 15:31
___i want to get last id from my table then i will put it in registration form with +1___ No dont ever do this. The most obvious reason being that it 2 users are doing the same thing, at the same time, they will both get the SAME ID. And there you are in a Mother of All S.N.A.F.U's — RiggsFolly, Feb 14 '23 at 15:33

score 0 · Answer 1 · answered Feb 14 '23 at 15:56

Riggsfolly already warned you about generating your own IDs.

It is MUCH safer to let the database generate UNIQUE ID's.

(Almost) Every table I create gets a primary key that has some form of SERIAL in it.

For example: For Postgres I use SERIAL:

CREATE TABLE registration (
  registrationid SERIAL PRIMARY KEY,
  project_name VARCHAR(100),
  whatever VARCHAR(1000)
)

You seem to use MYSQL: Look up AUTO_INCREMENT.

Now, if you insert into that table, you do it without the registrationid:

INSERT INTO registration (project_name , whatever ) VALUES ('test2', 'hi there');

If this is the first insert in the table registrationid will automagically become 1. Next insert is will be 2. etc etc.

Now suppose you did this 1000 times, you next one will then be 1001.

When you delete a few, that doesn't matter, the next will be 1002.

Now: If you want the query for the highest registrationid AND some project_name, do it like you did, but make sure you get the MAX() of the ones that have your desired project_name:

SELECT * 
FROM registration 
WHERE registrationid= (SELECT MAX(registrationid) FROM registration WHERE (project_name='test2') ) ;

2 last observations:

Follow Riggsfolly's advice and do not generate your own uniqueness, but let the database do this using appropriate syntax when defining your table (SERIAL for Postgres, AUTO_INCREMENT for MySQL, etc).
In my example the inserts are naive. It is better to use PDO for databinding when working with strings to avoid SQL-injection.

I want to get last id in mysql and php

1 Answers1