Neverside

SQL Rand

Ok for a while now I've been trying to come up with a way to output a random sql table but haven't had any luck is there like a function i can use or does anyone have any advice.

___________________

Signature Suspended as it is in violation with the signature rules

why would you want to select from a random table?

___________________

Jeremie - Used to be the Director of Community Development

Adverts, random quotes, random usernames, ect

___________________

Signature Suspended as it is in violation with the signature rules

what DBMS are you using? mySQL? if so, try this -

Code:

select * from table order by rand() limit 1

random, table is not random row

___________________

http://www.philbrodeur.com - Expert PHP Development and Tutorials

Oops I meant row not table, yes I have MySQL, and no that didn't work.

___________________

Signature Suspended as it is in violation with the signature rules

Here is a code i created for the random display of advertisments. This also works for flash advertisments and at the bottum it has a section to record the amount of times the ad has been displayed.

PHP:

<?

INCLUDE("admin/connect.php");

$query = mysql_query("SELECT * FROM `ads` WHERE `status` = '$status'");
$num_rows = mysql_num_rows($query);

if ($num_rows > 1){
$list = array();
$query = mysql_query("SELECT `id` FROM `ads` WHERE `status` = '$status'");
while($row = mysql_fetch_array($query)){
$addvar = $row["id"];
array_push ($list,$addvar);
}
$i = rand (0,sizeof($list)-1);
$query = mysql_query("SELECT * FROM `ads` WHERE `status` = '$status' and id='$list[$i]'");
}

while($row = mysql_fetch_array($query)){
$one = $row["id"];

$adtype = explode('.', $row["image"]);
if ($adtype[1] == "swf"){

?>
<center><embed src="admin/ads/<?=$row["image"];?>" width="<?=$adwidth;?>" height="<?=$adheight;?>"></center> 
<? } else { ?>

        <center><a href="admin/go.php?ad=<?=$row["address"];?>&id=<?=$row["id"];?>" target="_blank"><img src="admin/ads/<?=$row["image"];?>" border="0"></a></center>

<?
}

};
mysql_close($connect);
include("admin/connect.php");
$query = mysql_query("SELECT * FROM ads WHERE id = '$one'");
while($rst = mysql_fetch_array($query)){
$num = $rst["views"] + 1;
};
mysql_query("UPDATE ads SET views='$num' where id='$one'");

?>

___________________

LocalHoster.com
FreeHostHelp.com

Quote:

Originally posted by konigwolf
Oops I meant row not table, yes I have MySQL, and no that didn't work.

didn't work? what version of mySQL are you running, and on which platform?

that statement works on my win32 mySQL 3.23.58. I left a semicolon off that statement by accident, maybe that was the problem? you could also try this (very slightly different) -

Code:

select * from table order by rand() limit 0,1;