Return random row, insert it into another database.

[eluser]JoeLongstreet[/eluser]
I'm trying to return a single random row from a table and insert the value of one of the columns into a different table. Works ok, but seems like there should be less code to do this? Am I wrong?

Code:

$random_num = strval(rand(0,183));
                $q = $this->db->get_where('table', array('id' => $random_num));
                foreach($q->result_array() as $row) {
                    $array[] = $row;
                }
                $number = $array[0]['columnName'];
                $this->db->insert('anotherTable', array('columnName' => $number));

Thanks for any help you can provide,

Joe

[eluser]mvdg27[/eluser]
I think your way of selecting a random row is not really efficient. You should limit your query to select 1 single row and order randomly by id:

Code:

$this->db->limit(1);
$this->db->order_by('id', 'random');
$query = $this->db->get('table');
$random_row = $query->first_row();

(didn't test the code, but this is the right approach. For details take a look at the user guide).

Hope that helps

[eluser]jedd[/eluser]
Your subject and your message conflict - do you mean another database, or another table?

For the code - DB random is apparently expensive, and so depending on how often you do this (it seems a silly thing to do, but, meh!) you may be better off doing a count(id), then using PHP's random function to identify an explicit row to get, and then get that row. Do some research / time tests.

Why are you using result_array() if you just want one row? Are you philosophically opposed to row_array() ?

[eluser]JoeLongstreet[/eluser]
Thank you, this seems like a more efficient way of doing things.

I was using result_array because I don't really know what I was doing. I am not opposed to row_array.

Joe