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;).
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)
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.
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.