Problems with function and MySQL

[Niixie]

Hello PHPFreaks.

 

I have one problem, that I've tried to fix for a day or two now.

I need a function, that picks out a random row in a table, and returns one thing.

 

I tried with, getting the max rows in the table, make a random number in the interval 0 to max rows, then select the random number, but it failed.

 

I hope someone can help me out with this one?

 

-Niixie

 

This is my code, and I know it's pretty messy, because i fooled around with it for a day or two, so it's hard to find head or tail in it.

$query0 = sprintf("SELECT * FROM registertest");
        $result = mysql_query($query0);
        
        if (!$result) {
            $message  = 'Invalid query: ' . mysql_error() . "\n";
            $message .= 'Whole query: ' . $query;
            die($message);
        }
        
        $nums = mysql_num_rows($result);
        $rand_nums = rand(0,$nums);
        
        $query1 = sprintf("SELECT question FROM registertest WHERE id='%i'",
                $rand_nums);
        $result1 = mysql_query($query1) or die(mysql_error());
        
        if (!$result1) {
            $message  = 'Invalid query: ' . mysql_error() . "\n";
            $message .= 'Whole query: ' . $query;
            die($message);
        }
        
        $row = mysql_fetch_row($result1);
        mysql_free_result($result);
        mysql_free_result($result1);
        $_SESSION['registerquestion'] = $row;
        if(strlen($_SESSION['registerquestion'])!=0){return 1;}else{return 0;}

[jotorres1]

Are you sure id will always contain all numbers from 0 to max without skipping any numbers?  What will happen if you delete a row, and insert another?

 

You could consider this:

 

<?php 
$query = "SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1";
?>

[Niixie]

It doesn't have to be anything near my way to do it, i don't know all the fancy ways to do it, i only know the easy logic ones. But, I'm in for suggestions?

And yes, I can see that there would be a problem if a row got deleted..

Thanks for your reply.

 

[jotorres1]

Try using the query I gave you, and use the result.  That should help you with what you need, without having to do all that 'get random' number stuff.

 

<?php 
$query = "SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
?>

 

Hope that helps.

[Niixie]

Thank you Jotorres, but it doesn't return anything?

 

Heres my code.

$query = "SELECT * FROM `registertest` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `registertest` ) ORDER BY id LIMIT 1";
        $result = mysql_query($query) or die(mysql_error());
        
        if (!$result) {
            $message  = 'Invalid query: ' . mysql_error() . "\n";
            $message .= 'Whole query: ' . $query;
            die($message);
        }
        
        $row = mysql_fetch_row($result);
        $_SESSION['registertest'] = $row;
        return $row;

[Niixie]

Alright, i found a way to make it return the result. But it doesn't take a random number, it takes the first in the database?

[jotorres1]

Try changing this:

 

        $row = mysql_fetch_row($result);
        $_SESSION['registertest'] = $row;

 

to this:

 

        $row = mysql_fetch_array($result);
        $_SESSION['registertest'] = $row[0];