Random Database Pull - Printable Version

Random Database Pull - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Random Database Pull (/showthread.php?tid=6770)

Pages: 1 2

Random Database Pull - El Forum - 03-11-2008

[eluser]codelearn[/eluser]
Hey guys,

I'm having some trouble retrieving random entries from a table. I have a certain amount of multiple choice questions in a table and I need to retrieve a random amount of questions from a random set of questions.

The structure looks like this:

Code:
id (int)

question (text)

varchar (int)

set (int)

What would be the active record code to pull lets say 3 random questions from a particular set?

Thanks so much for the help!

Random Database Pull - El Forum - 03-11-2008

[eluser]xwero[/eluser]

Code:
$this->db->where('set',1)->order_by('id','random')->get();

Random Database Pull - El Forum - 03-11-2008

[eluser]codelearn[/eluser]
xwero,

Thanks for the reply. Would there be a way to randomize the set we are pulling as well?

Random Database Pull - El Forum - 03-11-2008

[eluser]xwero[/eluser]
I forgot the limit in the previous snippet

Code:
$this->db->where('set',1)->order_by('id','random')->limit(3)->get();

I don't think you can do it using one query because you only want one set and three questions.

Random Database Pull - El Forum - 03-11-2008

[eluser]Pygon[/eluser]
[quote author="xwero" date="1205263700"]I forgot the limit in the previous snippet

Code:
$this->db->where('set',1)->order_by('id','random')->limit(3)->get();

I don't think you can do it using one query because you only want one set and three questions.[/quote]

Would this not work for some reason?

Code:
$this->db->where('set',1)->order_by('set','random')->order_by('id','random')->limit(3)->get();

Honestly, I think you'd be better off doing something where the set = random then order_by random id for performance.

Random Database Pull - El Forum - 03-11-2008

[eluser]codelearn[/eluser]
xwero,

Was that in regards to also having a random set?

Pygon,

It would not work because your still asking for set #1.

Random Database Pull - El Forum - 03-11-2008

[eluser]Pygon[/eluser]
[quote author="codelearn" date="1205265056"]xwero,

Was that in regards to also having a random set?

Pygon,

It would not work because your still asking for set #1.[/quote]

I don't think so. You're asking for a random set orders, then random question orders in the sets with my query.

EDIT:

ahh sorry -- didn't notice he had where set=1

Code:
$this->db->order_by('set','random')->order_by('id','random')->limit(3)->get();

Random Database Pull - El Forum - 03-11-2008

[eluser]codelearn[/eluser]
Pygon,

But aren't you still asking for the set = 1? Maybe I'm just confused.

Thanks!

RESPONSE TO EDIT: Would that pull random questions from a specific set? For instance if Set 1 had only 2 questions this would return a third question from a dif. set.. correct?

Random Database Pull - El Forum - 03-11-2008

[eluser]Pygon[/eluser]
Yes -- posted the edit -- I didn't notice the where(set,1) in xwero's query.

The performance on my query, while random, is incredibly bad.

I would suggest implementing something like this: http://jan.kneschke.de/projects/mysql/order-by-rand/

If you're worried about sub-query support, select_max and do your randomizing/ceil in php, then do a second select with your where and order_by. Really the holes is only a concern if you are worried about even random distribution across sets with gaps in the number (1,2,5,12)

Random Database Pull - El Forum - 03-11-2008

[eluser]Pygon[/eluser]

Quote:RESPONSE TO EDIT: Would that pull random questions from a specific set? For instance if Set 1 had only 2 questions this would return a third question from a dif. set.. correct?

Yes, this would, but as above, it's incredibly bad performance if you have a large number of rows.