generating random 50 rowsets from table

[eluser]Prasad.P[/eluser]
Hi everybody,

I wanted to generate 50 row sets in random from a huge table. i tried using this following code;

Code:

$this->db->select('*');
$this->db->from( $this->session->userdata('subject') );
$this->db->order_by('sl_no','random');
$this->db->limit(50);
$resultID = $this->db->get();

it is returning only 1 row instead of 50, can anybody tell me where i went wrong.

In my older program without CI the following query string works fine in generating 50 random row sets;

Code:

SELECT * FROM ".$this->session->userdata('subject')." ORDER BY rand() LIMIT 50";

Thanks in advance.

[eluser]xwero[/eluser]
I've noticed that bug too in CI 1.6.2. The statement building method adds a backticked empty sting in front of the RAND function. As a temporary measure you could use

Code:

$this->db->order_by('RAND()');

[eluser]Prasad.P[/eluser]
Thankz xwero for your fast reply

but your statement is not working;

$this->db->order_by('RAND()');

its still displaying 1 row set only.

[eluser]Pascal Kriete[/eluser]
How are you displaying your data? The query looks right.

[eluser]Michael Wales[/eluser]
What database are you using? random ordering is not supported by all databases.

[eluser]xwero[/eluser]
Are you sure there is more than one row in the database table you are using? You can check it using

Code:

echo $resultID->num_rows();

The way to get the 50 rows would be

Code:

return $resultID->result(); // or ->result_array() if you want an array