Jump to content


Photo

help please...


  • Please log in to reply
2 replies to this topic

#1 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 12 April 2006 - 01:02 PM

Hi, can someone please help...

right what i have is one table

table 1 and the field in this is number

then i have 32 records 1,2,3,4, etc... 30,31,32

now i have

table2 and a field in this called randomnumber...

and what i want to do is update 32 records in table2 with the records from table1 but randomly and not in the order of 1,2,3, etc...

#2 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 12 April 2006 - 02:33 PM

Not that I do not want to help you out but why would you want to do this?? You could use php to randomize the output of the data rather than have another table.

Ray

#3 wisewood

wisewood
  • Members
  • PipPipPip
  • Advanced Member
  • 226 posts
  • LocationRotherham, England

Posted 12 April 2006 - 03:15 PM

I dont know the name of the field you have your numbers 1-32 in, so i've called it ID.
I have assumed there is an auto-increment field on table2 called 'id'.
<?php // SELECT 32 ENTRIES FROM table1 in random order
$strSQL = "SELECT * FROM table1 ORDER BY rand() LIMIT 32";
$result = mysql_query($strSQL) or die ("Invalid query");
$num = mysql_num_rows($result);

$count = 1;
// FOR EACH RESULT, INSERT THE NUMBER INTO THE randomnumber FIELD OF table2
for($i=0;$i<$num;$i++)
{
$number= mysql_result($result,$i,"number");
$insert_qry = "UPDATE table2 SET randomnumber=$number WHERE id=$count";
$insert_rslt = mysql_query($insert_qry);
$count++;
}
?>

It might not do exactly what you want, and might not be 100% coded right as i've not tested it, but it should point you along the right path.

The important thing is to SELECT FROM table1 ORDER BY rand() and then to update one entry in table2 for each of the 32 results.

If you wanted to update 32 random results in table2, and table2 contains a lot more than 32 entries...

<?php // SELECT 32 ENTRIES FROM table2 in random order
$strSQL = "SELECT * FROM table2 ORDER BY rand() LIMIT 32";
$result = mysql_query($strSQL) or die ("Invalid query");
$num = mysql_num_rows($result);
$count = 1;

// FOR EACH RANDOM RESULT, INSERT $count NUMBER INTO THE randomnumber FIELD OF table2
for($i=0;$i<$num;$i++)
{
$id= mysql_result($result,$i,"id");
$insert_qry = "UPDATE table2 SET randomnumber=$count WHERE id=$id";
$insert_rslt = mysql_query($insert_qry);
$count++;
}
?>

Hope this is of some use, or i've wasted my time lol.
wisewood: proven fact, I am both wise, and wooden.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users