问题描述:

Im making a drag n drop sortable list. What I am trying to do is increment a column in MyQql based on its id value automatically when new records are entered. ie:

if i have a row with an id = 3, and it is the first record enetered for that id, then its recordid = 1.

if i have a row with an id = 14, and it is the first record enetered for that id, then its recordid = 1.

if i have a row with an id = 3, and it is the second record enetered for that id, then its recordid = 2.

So i want it to autoincrement recordid based on its id value. not the whole table value. Does that make sence? what code would i need in php to find the highest value recordid pertaining to the id and then increment it by 1 when a new record is entered? Thanks in advance.

网友答案:

Something like this?

INSERT INTO `table` (`id`, `recordid`) VALUES
(
    $id,
    (SELECT MAX(`recordid`) + 1 AS `rid` FROM `table` WHERE `id` = $id)
);

You could probably optimize it way further though.

相关阅读:
Top