How would I randomly reorder an SQL table?

[diablo3]

I have retrieved 5 values from a database table...

The table is such as->

tmeta_idteam_id

11

22

33

44

55

Array ( 
[0] => 1 
[1] => 2 
[2] => 3
[3] => 4 
[4] => 5 ) 

.. and this array I have named $nonrandom_teamno. using shuffle() I have rearranged the array, the new array being $random_teamno, which is such as the following->

Array ( 
[0] => 3 
[1] => 4 
[2] => 5
[3] => 2 
[4] => 1 )

I am having real problems reinserting this into the SQL tables...

I have been trying the foreach loop below...

for($i = 1; $i < (count($nonrandom_teamno)+1); $i++)
    {
        $db->query("UPDATE ancl_teammeta SET team_id ='$random_teamno[$i]' WHERE tmeta_id='$nonrandom_teamno[$i]'");       
    }

It puts numbers back in the database, but not random ones. Please help me with the correct query I need!! Thank you..

[btherl]

Can you do this please and post the output:

 

for($i = 1; $i < (count($nonrandom_teamno)+1); $i++)
    {
        $query = "UPDATE ancl_teammeta SET team_id ='$random_teamno[$i]' WHERE tmeta_id='$nonrandom_teamno[$i]'";
        print "Going to do: $query<br>";
        $db->query($query);  
    }

[diablo3]

the output for values 1-5 in the team_id colomn..  with the random numbers: 2, 3, 1, 5, 4

team_id:

3

2

3

4

5

 

[Pandemikk]

That's a for loop. Not a foreach loop. Personally, I'd recommend a foreach loop. And btw, don't use count() inside a for loop. Assign count to a variable and use that instead.

 

The problem with your code is you're shuffling the array but updating each team_id with the current array value where its team_id is the current array value. Basically, all shuffle has done is determine what order you're updating when you want it to be randomly updated.

 

What you need is to update team_id with the the old team_id's value.

 

<?php
$teams = array(
0	=> 1,
1	=> 2,
2	=> 3,
3	=> 4
);

// preserve team_ids
$teamids = array_values($teams);

// randomize
shuffle($teams);

// old team_ids are the keys and randomized team_ids are the values
$teams = array_combine($teamids, $teams);

foreach ($teams AS $key => $val)
{
$db->query("UPDATE ancl_teammeta SET team_id = $val WHERE tmeta_id = $key");
}
?>

 

There is one problem here. It is possible for the key and value to be the same thus not updating that specific team_id at all. I'm not sure if this will be a problem or not. Btw, shuffle assigns new keys to the elements. It's a bit of a pain.

[btherl]

diablo3, I just noticed your "for" loop starts at 1 - your arrays are indexed from 0 so it should start at 0, like this:

 

for($i = 0; $i < count($nonrandom_teamno); $i++)

 

Pandemikk's advice is good too - it's more elegant to use a foreach loop here.  And his solution leaves less room for logic errors.

[diablo3]

It's really frustrating, but I still can't get it to work!!!

 

I tried Pandemikk's code, that failed too...

 

It should be very simple... I want to update a table, with these random numbers:

Array ( [0] => 4 [1] => 2 [2] => 5 [3] =>1 [4] => 3 )

 

I'm pretty confused!!! lol

 

[btherl]

Did you add the 5th team to Pandemikk's code?  He included only 4, not 5.  Also I would add these debugging statements at each step to check that the values are correct:

 

<?php
$teams = array(
0	=> 1,
1	=> 2,
2	=> 3,
3	=> 4
);

// preserve team_ids
$teamids = array_values($teams);

print "<pre>";var_dump($teamids);print "</pre>";

// randomize
shuffle($teams);

print "<pre>";var_dump($teams);print "</pre>";
// old team_ids are the keys and randomized team_ids are the values
$teams = array_combine($teamids, $teams);

print "<pre>";var_dump($teams);print "</pre>";

foreach ($teams AS $key => $val)
{
$db->query("UPDATE ancl_teammeta SET team_id = $val WHERE tmeta_id = $key");
}
?>

[Pandemikk]

Well if you wanted a specific number for them that'd be much easier. What your original post asked for was to put random team_ids for existing team_ids into the database, which is what my code was for. If you want to assign specific keys specific values to be inserted all you need to do is this:

 

<?php
$teams[0] = 4;
$teams[1] = 2;
$teams[2] = 5;
// etc
?>

 

Or if you did indeed want to make them random you must not have added the 5th array as btherl stated.