[smithygotlost]

Heya guys

ok i have a table in my db called room under the table i have roomid and z the roomid can be anything from 1 - 1000 but the z will be the same if its the same area. now what i wanna do is select from roomid where id=300 that will then give the z from that table in this case 3

so right now i have z=3 ,what i now wanna do is get all the roomid's where z=3 and choose a random one, and display it as a variable

How can i do that ?

Cheers
Mike

[wirehopper]

PHP Code:

mysql_query('SELECT COUNT(*) AS \'count\' FROM `room` WHERE `z`=3');

$rResult=mysql_fetch_assoc();

$iNumRooms=$rResult['count'];

$iRandom=rand(1,$iNumRooms);

mysql_query('SELECT * FROM `room` WHERE `z`=3 LIMIT '.$iRandom.',1');

$rResult=mysql_fetch_assoc(); 

Not tested.

Basic idea - count the number of z=3s, generate a random number within that range, use that number as an offset to select the data.

[job0107]

This should work too:

PHP Code:

$id=300;
$sql = mysql_query("SELECT z FROM room WHERE roomid='$id'");
$r = mysql_fetch_row($sql);

$sql = mysql_query("SELECT * FROM room WHERE z='$r[0]' ORDER BY RAND()");
while($row = mysql_fetch_assoc($sql))
{
 if($row["roomid"] != $id)
 {
  $roomid = $row["roomid"];
  $z = $row["z"];
  break;
  }
 }
echo $roomid."<br />".$z."<br />"; 

[smithygotlost]

Very Nice Guys

ok ive moded Jerry's into this

PHP Code:

//Move Mobs
$mob = mysql_query("Select * from `roommobs` where `move`='1'")or die(mysql_error());
$mobid = mysql_result($mob,0, "mobid");
$id = mysql_result($mob,0, "roomid");
$sql = mysql_query("SELECT z FROM room WHERE roomid='$id'");
$r = mysql_fetch_row($sql);

$sql = mysql_query("SELECT * FROM room WHERE z='$r[0]' ORDER BY RAND()");
while($row = mysql_fetch_assoc($sql))
{
 if($row["roomid"] != $id)
 {
  $roomid = $row["roomid"];
  $z = $row["z"];
  break;
  }
 }
mysql_query("update `roommobs` set `roomid`='$roomid' where `mobid`='$mobid' and `move`='1'"); 

Nice bit of code btw erm next quest if i have more than 1 mob how do i make it loop till its done them all ? would it be something like a FOR ?

Cheers
Mike

[job0107]

Maybe like this:

PHP Code:

$mob = mysql_query("Select * from `roommobs` where `move`='1'")or die(mysql_error());
while($rows = mysql_fetch_assoc($mob))
{
 $mobid = $rows["mobid"];
 $id = $rows["roomid"];
 $sql = mysql_query("SELECT z FROM room WHERE roomid='$id'");
 $r = mysql_fetch_row($sql);

 $sql = mysql_query("SELECT * FROM room WHERE z='$r[0]' ORDER BY RAND()");
 while($row = mysql_fetch_assoc($sql))
 {
  if($row["roomid"] != $id)
  {
   $roomid = $row["roomid"];
   $z = $row["z"];
   break;
   }
  }
 mysql_query("update `roommobs` set `roomid`='$roomid' where `mobid`='$mobid' and `move`='1'");
 } 

[smithygotlost]

Never dissapoint do you jerry

Cheers
Fella

[job0107]

Your welcome.