How to insert the next highest number into the dat

问题:

I have a piece of mysqli code which I written where it will insert values into the database:

$insertsql = "
INSERT INTO Teacher
    (TeacherId, TeacherForename, TeacherSurname, TeacherEmail, TeacherAlias, TeacherUsername, TeacherPassword, Active, Code)
  VALUES
    (?, ?, ?, ?, ?, ?, ?, ?, ?)
";
if (!$insert = $mysqli->prepare($insertsql)) {
  // Handle errors with prepare operation here
}

$teacherid = ;

$insert->bind_param("sssssssss", $teacherid, $getfirstname, $getsurname,
             $getemail, $getteachid, $getuser,
             $password, $active, $code);

$insert->execute();

if ($insert->errno) {
  // Handle query error here
}

$insert->close();

But I have a little problem. At the moment I have left the $teacherid variable blank, but what I want this variable to do it to find the last "TeacherId" from the database and insert a new one by inserting the next "TeacherId".

FOR EXAMPLE:

If the "Teacher" Table looks like this for TeacherId:

TeacherId

T1
T2
T3
T4

Then when I Insert a new TeacherId value, it should insert T5, this is because T4 is the current highest T number so the next number should be T5.

Does anyone know how this can be achieved. Any help would be much appreciated :)

Thanks

回答1:

Change TeacherId from a varchar to an int. The create table will look a bit like this:

CREATE TABLE Teacher (
   TeacherId int unsigned not null auto_increment primary key

The auto_increment will start from 1 and automatically increase by 1 on each insert.

If you must prepend the T, you can always do it with CONCAT or in the php code.

回答2:

You could do something like this. It is an auto incrementing primary key column with a formatted column. I have not had a chance to test this.

CREATE TABLE Teacher (
  TeacherId mediumint UNSIGNED AUTO_INCREMENT PRIMARY_KEY,
  TeacherIdF varchar(6) AS 'T_' + CONVERT(varchar, TeacherId),
  ...
  CONSTRAINT Teacher_UKey01 UNIQUE (TeacherIdF)
)

If the constraint does not work, you can create a trigger that will update the column after a row is inserted.

CREATE TABLE Teacher (
  TeacherId mediumint UNSIGNED AUTO_INCREMENT PRIMARY_KEY,
  TeacherIdF varchar(6) NULL
  ...
)

CREATE TRIGGER FormattedTeacherId
AFTER insert ON Teacher
BEGIN
UPDATE Teacher SET TeacherIdF = CONCAT('T', TeacherId)
END

I have not tested this code.

回答3:

Well you can try this:

First get the total number of rows in the table and increment it by 1 and put that into your new id For example:

$selectUsers = mysql_query ("SELECT * FROM `Teacher`") or die(mysql_error());
$num_rows = mysql_num_rows($selectUsers);

$next_id = $num_rows+1;

$new_id = 'T'.$next_id ;

and after insert it into the table:

...