Print a random row from mysql table

[slj90]

Hello,

 

I am trying to display a random row from an sql table. The fields I want to display are... 'Username' and 'XP'

 

I know the code to print data knowing the ID is..

 

<?php

include ("connectionusers.php");
   
$ID = $_SESSION["authenticatedUser"] ;
   
   
$ID = $_GET['ID'] ;
    
$query = "SELECT * FROM users WHERE ID = $ID";

$result = mysql_query ($query);

if ($row = mysql_fetch_array($result)) 
{ 
print "<b>ID: </b>" . $row["ID"] . "<br>" ;
print "<br><b>Username: </b>" . $row["Username"] . "<br>"; 

 

Could I use a code to generate a random number between 1 and 100, then place that number into 'ID' in the code above?

 

Sorry if I haven't explained this very well and I appreciate any help.

 

Thanks

 

[calmchess]

I'd use array_push to push all my rows into an array and then use $ran=rand (0,100) to generate a random number then say $target = arr0[$ran];

[mapleleaf]

$id = rand(0,100);

[tail]

mysql_query("SELECT `username`,`xp` FROM `users` WHERE `id`='".$id."' ORDER BY RAND() LIMIT 1");

 

I'm fairly certain that would be the fastest and most efficient way.

[calmchess]

oh yeah i forgot that there is a random *sql command......use that !

[slj90]

So it would be

 

mysql_query("SELECT 'Username','XP' FROM 'users' WHERE 'ID'='".$id."' ORDER BY RAND() LIMIT 1")

 

What do i need to put after to display the results?

 

Thanks for your help!

[tail]

$r=mysql_fetch_array(mysql_query("SELECT 'Username','XP' FROM 'users' WHERE 'ID'='".$id."' ORDER BY RAND() LIMIT 1"));
echo 'Username: '.$$r['username'].' XP: '.$$r['xp'];

[Buddski]

I am presuming that ID is unique to all users in the users table therefor if you are specifying the ID the RAND() will have no effect because it will be getting all the users with ID = $ID (which im guessing is 1) and then getting a random one from that set which is the only one there..

 

There are 2 approaches (assuming that I am right);

$sql_query = mysql_query("SELECT `Username`,`XP` FROM `users` ORDER BY RAND() LIMIT 1");

or

$sql_query = mysql_query("SELECT `Username`,`XP` FROM `users` WHERE `ID`='".rand(1,100)."'");

 

and FYI:

echo 'Username: '.$$r['username'].' XP: '.$$r['xp'];

will more than likely throw an undefined error as using that method will be looking for variables called, for example, $Someusername and $somexpthingo...

[tail]

and FYI:

echo 'Username: '.$$r['username'].' XP: '.$$r['xp'];

will more than likely throw an undefined error as using that method will be looking for variables called, for example, $Someusername and $somexpthingo...

 

If you didn't notice,  I used the mysql_fetch_array() function, which puts the results into variable $r, as seen in my code. The only reason my code wouldn't work is because I put two $ by accident.

[Buddski]

Im not saying that your code was entirely wrong but the double $ was a valid point, accident or not, not to mention the query itself may be wrong..

 

I was giving my 2 cents and pointing out some issues that may have been encountered.. I wasnt 'trying' to offend

[tail]

Haha wow, the query is wrong. I'm never going to try coding anything at 7:00am again!