To produce your desired results, you can use the INSERT ... SELECT syntax with a COUNT subquery on the result table, in order to retrieve your row count or 0, along with a CONCAT() of the mid to produce the desired rsid value.
To help prevent SQL injection attacks, it is strongly encouraged to use prepared statements,
Assuming rsid is your primary key you can use the following.
//enable mysqli exception handling
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$c = mysqli_connect($host, $user, $pass, $dbname);
$query = <<<'EOL'
INSERT INTO `result`
SELECT
CONCAT(?, COALESCE((SELECT COUNT(r.rsid)
FROM `result` AS r
WHERE r.mid = ?
GROUP BY r.mid), 0)),
?, ?, ?, ?
EOL;
try {
/* example data
* $m = 0;
* $u = 1;
* $w = 1;
* $k = 1;
*/
$stmt = mysqli_prepare($c, $query)
mysqli_stmt_bind_param($stmt, 'ssssss', $m, $m, $m, $u, $k, $w);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
/*
* retrieve the theoretical last record inserted
*/
$stmt = mysqli_prepare($c, 'SELECT MAX(rsid) FROM result WHERE mid = ?');
mysqli_stmt_bind_param($stmt, 's', $m);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $rsid);
if (mysqli_stmt_fetch($stmt)) {
echo $rsid;
}
mysqli_stmt_close($stmt);
} catch(mysqli_sql_exception $e) {
//handle the exception
//echo $e->getMessage();
}
Results per execution
| execution | $rsid |
| 1 | 00 |
| 2 | 01 |
| 3 | 02 |
...
Disclaimer, your current approach of using the total number of rows to generate the rsid is subject to serious data integrity complications. When a
record is deleted from the result table, the retrieved COUNT or mysqli_num_rows will result in a conflicting rsid. Additionally if the mid value is ever changed (a typo is corrected), the rsid value will become invalid.
There are also additional complications to consider with race conditions/hazards.
See sql - Do database transactions prevent race conditions for details on preventative measures
For example, if you have a record of 00 in the database already, SELECT CONCAT(mid, COUNT(rsid)) WHERE mid = 0; will return 01. After inserting 01 and then removing 00, your next insert will be a duplicate rsid of 01;
INSERT INTO results(rsid)
VALUES(01);
DELETE FROM results WHERE rsid = '00';
INSERT INTO results(rsid)
VALUES(01); /* error duplicate key */
I recommend using SELECT MAX(rsid) + 1 instead of COUNT(). This will ensure your rsid is not repeated after deletion, but does not resolve the UPDATE mid issue. However you will need to have a minimum mid of 1 for MAX() + 1 to work.
INSERT INTO `result`
SELECT
COALESCE((SELECT MAX(r.rsid) + 1
FROM `result` AS r
WHERE r.mid = ?
GROUP BY r.mid), CONCAT(?, 0)),
?, ?, ?, ?
If you absolutely need to use row count, to avoid the complications, you would need to ensure to only DELETE the highest rsid record within the mid grouping, and never UPDATE the mid column values. Otherwise you will need to rebuild all rsid values, on any change to the table. If you decide to rebuild the rsid values, I suggest using before update and after delete triggers to handle both of these instances and use a DATETIME NULL DEFAULT CURRENT_TIMESTAMP column to determine the ordering of the records.
Alternatively you could generate an rsid only when viewed, by using an AUTO_INCREMENT primary key and an incremental user variable. Then you no longer need to worry about the application controlled rsid, and only be required to insert the mid.
SET @rsid: = NULL;
SET @mid = NULL;
SELECT
result.*,
CONCAT(mid, CASE WHEN @mid != result.mid OR @rsid IS NULL THEN @rsid := 0 ELSE @rsid := @rsid + 1 END) AS rsid,
@mid:=result.mid
FROM result
ORDER BY mid ASC, id ASC;
Result
| id | mid | ... | rsid |
| 1 | 0 | ... | 00 |
| 29 | 0 | ... | 01 |
| 311 | 0 | ... | 02 |
| 20 | 1 | ... | 10 |
| 40 | 1 | ... | 11 |
...
As a last alternative, you can use a segmented AUTO_INCREMENT by specifying a composite primary key of mid, id with the MyISAM engine, which is not capable of transactions or foreign key references. However, there are additional complications with this approach. [sic]
AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group.
CREATE TABLE `result` (
`mid` INT(11) UNSIGNED NOT NULL,
`id` INT(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`mid`, `id`)
)
ENGINE=MyISAM
;
INSERT INTO result(mid)
VALUES(0),(1),(0),(2),(0),(1);
SELECT
result.*,
CONCAT(mid, id) AS rsid
FROM result;
Results:
| mid | id | rsid |
| --- | --- | ---- |
| 0 | 1 | 01 |
| 0 | 2 | 02 |
| 0 | 3 | 03 |
| 1 | 1 | 11 |
| 1 | 2 | 12 |
| 2 | 1 | 21 |
To update your current result table you can use
/* remove default value */
ALTER TABLE `result` ALTER `mid` DROP DEFAULT;
/* change engine, add id column, add composite primary key */
ALTER TABLE `result`
ENGINE=MyISAM,
CHANGE COLUMN `mid` `mid` INT(11) NOT NULL FIRST,
ADD COLUMN `id` INT(11) NOT NULL AUTO_INCREMENT AFTER `mid`,
ADD PRIMARY KEY (`mid`, `id`);
