Random MySQL Entry

I am tring to figure this out and I can't. When you set a primary key. Say something_id and it is defined as a int (12) auto_inc. I go and fill it up with 10 entries, meaning it is now set to be something_id entry 10, but say I go and delete entry 5. Why doesn't it take that 5 for the next number, but instead it does a 11 and leaves 5 blank.

How might I get it to where it fill's up the 5 instead of going to the next number or is that just one of the bad thing's of having a sql entry auto_inc ?

Also, the random part. The reason I ask this is I take the total mysql entries

num_of_mysql($result) and then randomize it and pull a random number from that. Now, take the above and I do that it pull's a 5 out which blank and it doesn't see entry number 11, because there is really only a total of 10 table entries.

So, another question is. Would it be better just to read the something_id 3's into an array then pull a random something_id # out of the array? I am thinking about doing this, but don't want to because I am thinking it will be to slow for me. I guess maybe?

Sorry if you can't understand what I am tring to say. Maybe someone will understand and can help me out with what I am tring to figure out.

 

 

 

Comment/Reply (w/o sign-up)

Supposing you have two tables - one containing usernames and some other details and another table containing the user's address, telephone number etc.

In table A, your primary key is the auto incrementing field - incresing by 1 for every new user added to the database. This field is also the foreign key to the table B - which stores the addresses. thus using this autoinc field you can map an username to it's corresponding postal address in table B.

Supposing you deleted record 5 and the index now starts from 6. This affects only table A - what happens to all those records after 5 in table B ?? They get linked to some new username which appears at location 6 - totally corrupting the data. Table B has no way of knowing that a record has been removed from A and that it should adjust it's own index values accordingly. This is why once the count reaches a certain number it stays there and doesn't go back to the last deleted index - no matter how many records you delete from in between.

This gives rise to another problem though.. All of a sudden you have a whole bunch of orphaned records in table B - addresses for which the original username have been removed from table A.. what do you do with these ?? Under these circumstances - you either delete the records with same index from both tables - or fill up the records with blanks in both. Either approach works.

As for your case - you can still pick a random value from num_of_mysql($result) - except that once you read that record - check whether the first field is blank or not. If blank, generate another random value and repeat the above step, till you reach a record that's not blank. This is a better approach than reading the whole thing into an array and running the random routine on it.

Regards,
m^e

 

 

 

Comment/Reply (w/o sign-up)