php - 我们可以在MySQL数据库的PHP的同一列上添加和更新吗？

mysql_query("insert into support values('null','$status','$sbj','$message','$userid','$user','','','$date','')", $dbc) or die('Could not connect: ' . mysql_error());

$index = mysql_insert_id();
$inci = '1' . $index;

echo "$inci";

mysql_query("UPDATE support SET incidentno='$inci' WHERE index='$index'",$dbc) or die('Could not connect: ' . mysql_error());

printf("<h2>your incident id is: %d\n</h2>", $inci) ;

echo $inci;

最佳答案

在表上添加触发器（使用MySQL控制台或类似工具），这是一个完整的示例，其测试表名称为support

CREATE TABLE `support` (
    `index` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(100) NOT NULL,
    `incidentno` varchar(100) NOT NULL,
    PRIMARY KEY (`index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `login` (
    `username` varchar(100) NOT NULL,
    `section` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DELIMITER $$
CREATE TRIGGER `generate_incidentno` BEFORE INSERT ON `support`
FOR EACH ROW
BEGIN
    SET NEW.incidentno = CONCAT(CASE (SELECT section FROM login WHERE username=NEW.username)
        WHEN 'dehli' THEN '1'
        ELSE '2'
    END
    , (
        SELECT AUTO_INCREMENT FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'support'
    ));
END$$
DELIMITER ;


INSERT INTO login VALUES ('user1','dehli'),('user2','dehli'),('user3','other');
INSERT INTO support VALUES (NULL,'user1','');
INSERT INTO support VALUES (NULL,'user1','');
INSERT INTO support VALUES (NULL,'user2','');
INSERT INTO support VALUES (NULL,'user3','');
INSERT INTO support VALUES (NULL,'user3','');
SELECT * FROM support;


id  username   incidentno
1   user1      11
2   user1      12
3   user2      13
4   user3      24
5   user3      25

如您所见，一切都很好