MySQL: how do I reset auto_increment count fields?

[michaelsanford]

Situation: I have a table with passages of text, which are randomly picked using the following PHP code (the table is also described for clarity):

mysql> desc lyrics;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| lyric_id | smallint(5) unsigned |      | PRI | NULL    | auto_increment |
| title    | varchar(40)          |      |     |         |                |
| artist   | varchar(40)          |      | MUL |         |                |
| album    | varchar(40)          | YES  |     | NULL    |                |
| added_by | varchar(20)          | YES  |     | NULL    |                |
| lyrics   | blob                 |      |     |         |                |
+----------+----------------------+------+-----+---------+----------------+

$result_count = mysql_query("SELECT COUNT(lyric_id) FROM lyrics", $link);
$resultRow = mysql_fetch_array($result_count);
$rand_lyric_id = rand(1, $resultRow['0']);

As you can see, this code requires there to be an unbroken set from 1 to $resultRow['0'] (the row count).

But if you delete a row, the lyric_id which is an auto_increment, will not replace the missing value, and if that row is called randomly, it will generate an error.

Is there a way that I can reset auto_increment counts, other than manually filling in a record with the missing number?

And hey, if you have any other brilliant suggestions, I'm open

 

[Gnomo]

Okay,

This method uses SQL to handel the random and will sort the values in a random order and then just return the first random lyric_id.

If you use the random features of php, you will need to know the smallest number and the largest number in lyric_id (which could be gotten by using MAX and MIN in sql queries). However, this would cause problems is your list had missing values in the middle.

<?PHP

$result = mysql_query("SELECT `lyric_id` FROM `lyrics` ORDER BY RAND()" LIMIT 0,1", $link);

$rand_lyric_id = mysql_result( $result, 0);
?>

Hope that helps.

 

[michaelsanford]

That works, thanks!