如何在数据库中插入下一个最高数字[英] How to insert the next highest number into the database

本文是小编为大家收集整理的关于如何在数据库中插入下一个最高数字的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有一个我编写的MySQLI代码,它将其将值插入数据库:

$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();

但是我有点问题.目前,我已经离开了$teacherid变量空白,但是我希望这个变量能够从数据库中找到最后一个"老师",并通过插入下一个"老师"来插入一个新的"老师".

例如:

如果"老师"桌子看起来像是这样的老师:

TeacherId

T1
T2
T3
T4

然后,当我插入一个新的教师值时,它应该插入T5,这是因为T4是当前的最高t号,因此下一个数字应为T5.

有人知道如何实现这一目标.任何帮助将不胜感激:)

谢谢

推荐答案

更改TeacherId从varchar到int.创建表看起来有点像这样:

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

auto_increment将从1开始,并在每个插入物上自动增加1个.

如果您必须预先预留T,则可以随时使用CONCAT或在PHP代码中进行.

其他推荐答案

您可以做这样的事情.它是带有格式列的自动增量主键列.我没有机会对此进行测试.

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

如果约束不起作用,则可以创建一个触发器,该触发器将在插入行后更新列.

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

我尚未测试此代码.

其他推荐答案

您可以尝试以下操作:

首先获取表中的行总数,然后将其递增1,并将其放入您的新ID中:

$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 ;

然后将其插入表格:

...

本文地址:https://www.itbaoku.cn/post/597314.html

问题描述

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

推荐答案

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.

其他推荐答案

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.

其他推荐答案

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:

...