July 12th, 2000, 01:37 PM
I have this table with 2 columns, id and content. Id is an auto-incrementing column. My php script produces a random number from 1 to number of rows in the table, and then displays the content for that row matching the random number to id. The problem is that if i delete a row, and the random number matches the id of the row deleted, that id will no longer exist. I could write some code to check for the id's existance, but is there another way to reset all of the id's in the id column?
Thanks in advance for any help!
July 12th, 2000, 04:26 PM
There is another way and you could even do it from an sql batch file, but would you want to?
The 5 easy pieces...
> alter table table_name modify id int not null;
> alter table table_name drop primary key;
> alter table table_name modify id int;
> update table_name set id=NULL;
> alter table table_name modify id int not null auto_increment primary key;
I believe that should do it, but my suggestion would be to read all the ids from the table into an array and then randomly choose one from that array, so you know it exists.
Better yet, try
> select id, content, content*0+RAND() as rand_col from table_name order by rand_col limit 1;
[This message has been edited by Kyuzo (edited July 12, 2000).]
July 13th, 2000, 01:10 PM
Thanks Kyuzo, that worked fine.