UPDATE query returns true NOT results

[eluser]joda[/eluser]
Hello,

I have the following code:

$query = $this->db->query("UPDATE table1 SET enabled = 'true' WHERE id IN ( SELECT id FROM table1 WHERE enabled = 'false' ORDER BY RANDOM() LIMIT 1 ) RETURNING id,value1");
var_dump($query);

I need $query to be an array with id,value1 as values but instead I get:

bool(true)

The update query works but I don't get the results I need returned.

Can anyone explain how I can get codeigniter to return id,value1 instead of true/false?

Thanks.

[eluser]InsiteFX[/eluser]
When "write" type queries are run it simply returns TRUE or FALSE depending on success or failure.

Take a look at the Active Record.

InsiteFX

[eluser]joda[/eluser]
Thanks InsiteFX. I read the manual regarding Active Record but I'm still not clear how I'd accomplish this. Any ideas?

[eluser]InsiteFX[/eluser]
MySQL does not have the RETURNING! You would need to create a work around.

If you can explain what you need to be done, I might be able to write it
for you.

InsiteFX

[eluser]joda[/eluser]
I need to select a random value from a table where a column value is true and return the id and a value. I need to insert data into another table including the id and value returned in the update.

I'm using postgresql and that query does work with when run in pgsql.

Thanks

[eluser]Krzemo[/eluser]
I doubt you can do it this way.
Select all IDs into an array, get pick a key randomly (0-n) and get a value for that key which will be your table's ID.
If your table is big this will be much faster then ORDER BY RANDOM(). Besides AFAIA thats the only solution to update random row and know which one was actually updated.

Regs
K

[eluser]John_Betong[/eluser]
[quote author="joda" date="1296223076"]Thanks InsiteFX. I read the
manual regarding Active Record but I'm still not clear how I'd accomplish this. Any ideas?[/quote]
 
Try this:

Code:

fucntion return_numeric()
{

  $query = $this->db->query(“UPDATE table1 SET enabled = ‘true’ WHERE id IN ( SELECT id FROM table1   WHERE enabled = ‘false’ ORDER BY RANDOM() LIMIT 1 ) RETURNING id,value1”);
var_dump($query);

  // I need $query to be an array with id,value1 as values but instead I get: bool(true)
  $result = $query ? 1 : 0;

  return $result;
}