How can I get two auto-increments in one database table?

[Joemeister]

I want the post_delete column to generate unique id's so that when I want to delete a comment for e.g. I can refer to the post_delete's value.

Here is my users TABLE

CREATE TABLE users (  
user_id     INT(8) NOT NULL AUTO_INCREMENT,  
user_name   VARCHAR(30) NOT NULL,  
user_pass   VARCHAR(255) NOT NULL,  
user_email  VARCHAR(255) NOT NULL,  
user_date   DATETIME NOT NULL,  
user_level  INT(8) NOT NULL,  
pic_location  VARCHAR(255) NOT NULL,
post_delete  INT(8) NOT NULL AUTO_INCREMENT, 
UNIQUE INDEX user_name_unique (user_name),  
PRIMARY KEY (user_id)  
);

and here is my posts TABLE

CREATE TABLE posts (  
post_id         INT(8) NOT NULL AUTO_INCREMENT,  
post_content        TEXT NOT NULL,  
post_date       DATETIME NOT NULL,  
post_topic      INT(8) NOT NULL,
post_by     INT(8) NOT NULL,  
PRIMARY KEY (post_id)  
);

and here is my delete query in php

$sql = "DELETE FROM posts WHERE post_by =" . $_SESSION['post_delete'];

It says when I include the table that there can only be one auto-increment. How can I change this so that the post_delete can generate unique id's?

Thanks!

[Database Shardi]

I want the post_delete column to generate unique id's so that when I want to delete a comment for e.g. I can refer to the post_delete's value.

Here is my users TABLE

CREATE TABLE users (  
user_id     INT(8) NOT NULL AUTO_INCREMENT,  
user_name   VARCHAR(30) NOT NULL,  
user_pass   VARCHAR(255) NOT NULL,  
user_email  VARCHAR(255) NOT NULL,  
user_date   DATETIME NOT NULL,  
user_level  INT(8) NOT NULL,  
pic_location  VARCHAR(255) NOT NULL,
post_delete  INT(8) NOT NULL AUTO_INCREMENT, 
UNIQUE INDEX user_name_unique (user_name),  
PRIMARY KEY (user_id)  
);

It says when I include the table that there can only be one auto-increment. How can I change this so that the post_delete can generate unique id's?

Thanks!

You can try the following syntax,

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

For more help check http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

[smantscheff]

This looks like nonsense.
When you want to delete a record from the post table you have to know it's post_id. You do not need an arbitrary generated value in the user table for that purpose.

[Joemeister]

Ok but how will that sql query then look like? Because this:

$sql = "DELETE FROM posts WHERE post_id =" . $_SESSION['user_id'];

deletes everything that the user commented/posted?

[smantscheff]

This is nonsense, too.
Your query

$sql = "DELETE FROM posts WHERE post_id =" . $_SESSION['user_id'];

deletes every post where the post_id equals the user_id. Since both are auto_increment values which do not have any logical relationship, your query will in effect delete arbitrary records with no relationship whatsoever.
You have to save the post_id of the post which you/the user wants to delete in a variable, e.g. a POST or session variable, and then delete exactly this post, like in

$sql = "DELETE FROM posts WHERE post_id =" . $_POST['post_id_to_delete'];

(With this construction, beware of mysql injection, though).

[carlhooper]

You can try the following syntax,

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

[Joemeister]

@smantscheff

Where do one retrieve or create the 'post_id_to_delete' ?

I don't fully understand that, because it gives me the error saying: Undefined index: post_id_to_delete

Thanks for your patience so far!

[smantscheff]

post_id_to_delete is the id of the post which is to be deleted. Wherefrom you get it depends on your application logic. So how does you application define which posts shall be deleteted?