how to insert into table with incremental id

User, date Message
Written by gilgal
5 years ago
Category: General
32 posts since Wed, 12 Dec 07 		I want to enter username and password into the table with incrementing id:

$sql = "INSERT INTO user (username, pass) VALUES (" . $username . ", " . $password . ")";



Whether it is done in php or Heidisql I'm not sure how.
Written by ansgar
5 years ago
3977 posts since Fri, 07 Apr 06 		- "Add field": Create a new column for your table, let's name it "id", (Integer, unsigned, not null)
- "Manage indexes...": Create a primary key on "id"
- "Edit field properties": Define "id" as auto increment

However, if you already have some rows in that table you will encounter a problem here with HeidiSQL: It won't let you create a primary key + set it as auto increment at the same time. That bug will be addressed soon. But you can do it with SQL in the query editor:

ALTER TABLE `yourtable`
ADD PRIMARY KEY (`id`),
CHANGE `id` `id` INT UNSIGNED NOT NULL AUTO_INCREMENT
Written by gilgal
5 years ago
32 posts since Wed, 12 Dec 07 		It didn't work:


I wrote:

<?php
$con = mysql_connect("","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}


mysql_select_db("satb", $con);
$sql = "ALTER TABLE book
ADD PRIMARY KEY (`id`),
CHANGE `id` `id` INT UNSIGNED NOT NULL AUTO_INCREMENT";
mysql_close($con);
?>
Written by ansgar
5 years ago
3977 posts since Fri, 07 Apr 06 		Ok, so what was the errormessage you surely got when executing the ALTER statement?
Written by gilgal
5 years ago
32 posts since Wed, 12 Dec 07 		There was no error message.

I tried the following but for some reason it put all 6es:

require_once('mysql.php');
mysql_select_db("satb", $con);
$textid = $_POST['theid'];
if(count($textid) >= 1){
//$sql = "INSERT INTO book (page) VALUES ('" . $textpage . "')";
for($i = 1; $i < count($textid); $i++){
$sql = "UPDATE book SET id = ('" . $textid[$i] . "') WHERE id = 0";
//$sql = "INSERT INTO book (id) VALUES ('" . $textid[$i] . "')";
mysql_query($sql) or die('Error: ' . mysql_error());
}
}
Written by gilgal
5 years ago
32 posts since Wed, 12 Dec 07 		Actually I did that after I had wrapped with text boxes around ids also made with a for loop.
Written by ansgar
5 years ago
3977 posts since Fri, 07 Apr 06 		But the column is not yet set to auto_increment is it? So, you have to execute that ALTER TABLE statement, otherwise you will have to create the increasing counter each time you INSERT some record.

Btw, this is a forum for HeidiSQL, not for PHP ...
 

Please login to leave a reply, or register at first.