Insert_id starting "1"

PHP
1

I have a table named “myTable”
It has two columns.
They are “ID” and “myNumeric”.
Both “ID” and “myNumeric” are int and ID is primary key.
It has no records at the moment in the table.

$myDBconnect=mysqli_connect('localhost', 'root', $dbPassword, $myDB);
mysqli_set_charset($myDBconnect,"utf8");

$myNumericData=1111;
$myInsert=("INSERT INTO myTable (myNumeric)  
VALUES ($myNumericData)");  
$myInsertEXE=mysqli_query($myDBconnect, $myInsert); 
echo $myDBconnect->insert_id;

When the code above is executed for the first time,
it produces “0” although I am expecting “1” because it is the first record.
the table becomes like the following.

I want my target result like the following.

(Q) How can I make it starts “1” instead of “0”?

2

Well it’s not really a fix but why not insert one row then delete it. Im pretty sure next row will AI at 1 since old IDs are not reused. But beware as rows are added and deleted you will not have a continuous 1-10,000 or whatever, you will have gaps.

3

by default, DB indexes start at 1

create table test(id int auto_increment, name int, primary key(id));
insert into test(name) values(2);
select * from test;

id	name
1	2

but the value of an ID should be irrelevant anyway, as long as its unique.

1 Like
4

Did you defined the column to AUTO_INCREMENT? Sounds like you didn’t.

1 Like
5

I think chorn and rpkamp are right, if col is AI should start as 1. Also in my opinion, using an AI id is really only good for that - an ID. If you want to sort or something like that you probably need to manage another col

6

You have something else going on here. mysql autoinc does start with 1. Not sure how it is even possible to get a zero in there.

Here is a quick test you can run from a mysql console window:

use play;

DROP TABLE IF EXISTS myTable;

CREATE TABLE myTable (
  id int NOT NULL AUTO_INCREMENT,
  myNumeric int,
  PRIMARY KEY (id)
);
INSERT INTO myTable (myNumeric) VALUES(1111);
SELECT * FROM myTable;
+----+-----------+
| id | myNumeric |
+----+-----------+
|  1 |      1111 |
+----+-----------+
1 row in set (0.00 sec)
7

OMG, I forgot it.
Thank you very much.

2 Likes
8

If you have used mysql database for storing and retriving data of your project. in your database you have one table which store the student_id and name of the student. id must be unique and also primary key.so it gives the auto_increment.So insert_id starting with “1”. if you delete table and re-insert data then insert_id start with this number when it is stopped.if You truncate the table and re-insert data than it starting with “1”.my project also work for that type of functionalities.

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.