Automatically number a column upon INSERT

[strongpot]

I am completely new to Triggers but I hear they can help me with a specific problem.

I have a table:

picid        picture        recordListingID
1            danny.jpg      0
2            jane.jpg       0
3            ralph.jpg      0

In the recordListingID column (int) i would like to generate numbers sequentially upon an INSERT. Is it possible? Can it be done without a trigger? Thanks in advance.

[1stDAN]

if you create your table, you can specify column recordListingID like recordListingID int NOT NULL auto_increment. So every time you insert a new row recordListingID will be automatically incremented by 1 starting with 1.

You can also do this by a trigger: define a before-insert trigger. Within this trigger search for maximum recordListingID value of already stored rows. Insert new row with recordListingID value maximum+1 (within trigger: set new.recordListingID = maximum + 1;).

[Reverend Jim]

Or in the insert you can do

INSERT INTO mytable (picid,picture,recordListingID)
VALUES((SELECT MAX(picid)+1 FROM mytable),'george.jpg',0)

[IIM]

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

So create a table using query,

CREATE TABLE mytable (
     picid INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

and then while inserting just add values to other columns,this value will be automatically filled by mysql server,mysql service maintains a track of the last id used ,so it is much quicker that using subquery.

INSERT into mytable(name) values ('sad');

So in this case even if you delete a row from a table,the next value will be 1 greater than the deleted value while using max function and incrementing 1 will make sure that you will always get one greater than the max id found in table.

[strongpot]

Thanks, it seems like this is working except for Godaddy not allowing Super Priv on their shared hosting. I hate godaddy so much. My boss loves them because they do superbowl commercials. FML. Thanks though.