Select a Random Database Row WHERE ID=a random value created by PHP function

Question

If I have a PHP function that generates a random number, is it possible to pass that variable into the sql statement in the WHERE clause? I'm using CodeIgniter, so this is my code using its syntax.

$random = rand(1, 572);
$result = $this->db->query( ' SELECT part1, part2, _id FROM `questions` WHERE `_id` >= '$random' LIMIT 0,1 ');

Is this even possible to do?

EDIT: The reason I want the php to execute the random number is because I need to call it multiple times throughout my pages, and it needs to do another call to another database using a sql query

Don't let people persuade you to use a different solution, you're headed in the right direction anyway. jan.kneschke.de/projects/mysql/order-by-rand ...otherwise, you just need to learn to concatenate strings (in PHP, use the period) — TehShrike
– TehShrike, Commented Sep 13, 2011 at 3:27
Because I know I have 572 rows right now. In the future that will probably grow, so what would be the best way to pass into that rand() function the max amount of rows in my table? — Bert B.
– Bert B., Commented Sep 13, 2011 at 3:36
@TehShrike: If the solution is better than my current, then I personally will let others persuade me into using that :) Of course they'd have to let me understand first why their solution is better — Rolando Cruz
– Rolando Cruz, Commented Sep 13, 2011 at 3:46
@Rolando: your solution is correct, though your suggestion (ORDER BY RAND() LIMIT 1) is misinformed - you should check out that last post I linked! It's quite good. — TehShrike
– TehShrike, Commented Sep 13, 2011 at 6:57

Rolando Cruz · Accepted Answer · 2011-09-13 03:48:51Z

1

Yes it is possible if you concatenate the variable with the string:

$query = "SELECT 
            part1, 
            part2, 
            _id 
          FROM
            questions
          WHERE _id >= " . $random . " LIMIT 0,1";
$result = $this->db->query($query);

But if what you want is to select a random row, then you might want this query

 SELECT part1, part2, _id FROM questions ORDER BY RAND() LIMIT 1

EDIT

I understand that _id will be random, but you are specifying the min and max for rand(), right? So you'd have to change it whenever you insert a new row, or you'd have to use two queries if you want to make sure rand() does not return a value too high. By using ORDER BY RAND() you are free from both problems. You simply have to get the value of _id that was returned from the query.

edited Sep 13, 2011 at 3:48

answered Sep 13, 2011 at 3:21

Rolando Cruz

2,7841 gold badge17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Bert B. Over a year ago

Sorry, read my edit. I need to use that same number for a different call as well to a different database

user557846 Over a year ago

@Bert why not use the id selected in the query for that?

Bert B. Over a year ago

This is exactly it. I forgot to concatenate it. I will accept your answer as best ASAP, stackoverflow is giving me a waiting period before I can select it as best.

Rolando Cruz Over a year ago

But you'd have to harcode the maximum value of rand() every time you insert something to the database. If you simply want to store the ID, then get it from the result. You are returning _id from the query anyways.

Rolando Cruz Over a year ago

Yes, but you execute the rand() before you execute the query. So if you want to use COUNT() you'd have to use 2 queries now. I understand that _id will be random but you are specifying the min and max for rand(), right? So you'd have to change it whenever you insert a new row, or you'd have to use two queries if you want to make sure rand() does not return a value too high. By using ORDER BY RAND() you are free from both problems. You simply have to get the value of _id that was returned from the query.

|

Kenneth Spencer · Accepted Answer · 2011-09-13 03:26:54Z

0

This might just be a mater of using double quotes instead of single quotes on the outside of your string.

$result = $this->db->query("SELECT part1, part2, _id FROM `questions` WHERE `_id` >= '$random' LIMIT 0,1 ");

answered Sep 13, 2011 at 3:26

Kenneth Spencer

1,59214 silver badges15 bronze badges

Comments

karse23 · Accepted Answer · 2011-09-13 06:43:56Z

0

Try this:

$result = $this->db->query("SELECT part1, part2, _id FROM questions WHERE _id >= '".$random."' LIMIT 0,1 ");

answered Sep 13, 2011 at 6:43

karse23

4,1456 gold badges32 silver badges33 bronze badges

Collectives™ on Stack Overflow

Select a Random Database Row WHERE ID=a random value created by PHP function

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related