Randomise IDs in Mysql

Chronicle · 30 Dec 2006 at 14:47

Hello,
I have a database, but i want to reorder it so they're all random.
Is there an easy to way to do this?
I can't just set it so that the id becomes a new id (as there will be duplications).

lookitsjonno · 30 Dec 2006 at 15:15

hmm... i dont get why anyone would want to do that?

just do the randomisation in your SQL select query?

Chronicle · 30 Dec 2006 at 16:47

I display them like as news posts.
A lot of the "posts" next to each other (ie, 55,56,57,58) are all very similar. So it want to jumble them up.

toString · 30 Dec 2006 at 17:42

Code:

select * from table order by rand() limit 3

Will get 3 random rows.
Just like jonno said.

Chronicle · 30 Dec 2006 at 17:44

I don't want just a random one (or three).
I need to reset the ID's to something else, easily.

It is a complicated (ish) script, that is basically like a news script. It has multiple pages (as it splits pages up into groups of 10 posts).

Using that method won't work I dont think

yhack · 30 Dec 2006 at 20:06

Chronicle said:
I don't want just a random one (or three).
I need to reset the ID's to something else, easily.

It is a complicated (ish) script, that is basically like a news script. It has multiple pages (as it splits pages up into groups of 10 posts).

Using that method won't work I dont think

Could go something like:

//random +/-
$1 = rand(0,1);
if($plus >= 0.5){
$2 = "+";
}else{
$2 = "-";
}
//add/subtract a random number to id
$rand = rand(0,50);
if($2 == "+"){
$id2 = $row['id'] + $rand;
}elseif($2 == "-"){
$id2 = $row['id'] - $rand;
}

It won't work but you can change it.
Probably doesn't help at all

Chronicle · 30 Dec 2006 at 20:25

Thanks. But nah, didn't really help. This isn't so much a PHP, more a mysql question. I just need to reorder the ID's in a table, randomly (or at least... so it is different to what it is now).

yhack · 30 Dec 2006 at 21:08

Random each time you select them?

Chronicle · 30 Dec 2006 at 21:12

No. That is the whole point. I just need to set the IDs ONCE to something new (And random).

yhack · 30 Dec 2006 at 21:30

ok make an admin page
make a button to Random_id();
make a new field called new_id in the mysql table

function Random_id(){
//connect to database
//select new_id
$max_id = //get maximum id some how
while($i, $i<$max_id, i++){
//update each new_id with a random number
}
on the news page order as new_id.
or just run the function once...

Probably only makes sense to me and will never work...

Something like this?

growse · 30 Dec 2006 at 23:07

I just did exactly this - however, I converted my id from an integer to a 32-byte UUID string. I created a new table, with the altered structure, copied the data across, dropped the old table, and then ran a script that basically did:

Code:

Select id from tbl;
$uuid = new UUID();
update tbl set id=$uuid where id=$oldid;