A grouping is a bunch of numbers 1, 2, 3, ... that are created all together on interest. Groupings are oftentimes utilized in data sets in light of the fact that numerous applications require each column in a table to contain an interesting worth and successions give a simple method to produce them. This part portrays how to utilize arrangements in MySQLi.
Using AUTO_INCREMENT column
The easiest path in MySQLi to utilize Sequences is to characterize a segment as AUTO_INCREMENT and leave rest of the things to MySQLi to fare thee well.
Example
Evaluate the accompanying model. This will make table and after that it will embed not many lines in this table where it isn't needed to give record ID since it's auto increased by MySQLi.
mysql>CREATE TABLE tutorials_auto(
id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(30) NOT NULL,PRIMARY KEY(id));
Query OK, 0 rows affected (0.28 sec)
mysql>INSERT INTO tutorials_auto(id,name) VALUES(NULL,'sai'),(NULL,'ram');
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+------+
| id | name |
+----+------+
| 1 | sai |
| 2 | ram |
+----+------+
2 rows in set (0.05 sec)
Obtain AUTO_INCREMENT Values
LAST_INSERT_ID( ) is a SQL work, so you can utilize it from inside any customer that sees how to give SQL explanations. Something else, PERL and PHP contents give elite capacities to recover auto augmented estimation of last record.
PERL Example
Utilize the mysql_insertid quality to get the AUTO_INCREMENT esteem produced by an inquiry. This trait is gotten to through either an information base handle or an assertion handle, contingent upon how you issue the inquiry. The accompanying model references it through the information base handle:
$dbh->do ("INSERT INTO tutorials_auto (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysqli_insertid};
PHP Example
In the wake of giving a question that creates an AUTO_INCREMENT esteem, recover the incentive by calling mysql_insert_id( ) −
mysql_query ("INSERT INTO tutorials_auto (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysqli_insert_id ($conn_id);
Renumbering an Existing Sequence
There might be a situation when you have erased numerous records from a table and you need to resequence all the records. This should be possible by utilizing a basic stunt however you ought to be mindful so as to do as such if your table is having gets together with other table.
On the off chance that you discover that resequencing an AUTO_INCREMENT segment is unavoidable, the best approach to do it is to drop the section from the table, at that point add it once more. The accompanying model tells the best way to renumber the id esteems in the bug table utilizing this strategy −
mysql> ALTER TABLE tutorials_auto DROP id;
mysql> ALTER TABLE tutorials_auto
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
-> ADD PRIMARY KEY (id);
Starting a Sequence at a Particular Value
Naturally, MySQLi will begin arrangement from 1 however you can indicate some other number too at the hour of table creation. Following is the model where MySQLi will begin succession from 100.
mysql> CREATE TABLE tutorials_auto
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
-> PRIMARY KEY (id),
-> name VARCHAR(30) NOT NULL,
-> );
Then again, you can make the table and afterward set the underlying arrangement esteem with ALTER TABLE.
mysql> ALTER TABLE tutorials_auto AUTO_INCREMENT = 100;