How to get your data from a select statement in Kohana?

Question

If I have this code (which is from their docs)

$query = DB::query(Database::SELECT, 'SELECT * FROM users WHERE username = :user');
$query->param(':user', 'john');
$query->execute();

How would I access the actual data returned from this?

I am looking for an associate array containing $array['username'] and whatever other columns are on the users table.

I have tried:

#1
echo $query->execute();
#2
$row = $query->execute();
echo $row['username'];
#3
$query->fetch();

None of which work. What am I missing?

Answer 1

Having never worked in the Kohana database part and given the Documentation is lacking examples for that (From what I could find). I have found a Database_Query page. Which seems to be something like $query->as_assoc() here is how I would think it would be used, but not guaranteeing it will work.

$query = DB::query(Database::SELECT, 'SELECT * FROM users WHERE username = :user');
$query->param(':user', 'john');
$query->as_assoc();
$return = $query->execute();

Just from the limited information I found that will hopefully work.

Answer 2

It looks like it's giving back a Kohana_Database_Result object. Try:

$rows = $result->as_array(); 

on it.

http://kohanaframework.org/guide/api/Database_Result

http://kerkness.ca/wiki/doku.php?id=crud_with_the_query_builder#reading_database_records

Answer 3

Database_Result object is a wrapper for database row list. Usually it is used with foreach:

$rows = DB::query(Database::SELECT, 'SELECT * FROM users WHERE username = :user')
   ->param(':user', 'john')->execute();
foreach($rows as $row)
   echo $row['id'].': '.$row['username'];

If you are expecting only one row (its your question as I understand), use current():

$row = current($rows);
echo $row['username'];

Note that Database_Result can be used as a simple array, so current(), foreach(), count() and other array functions are available.

PS. Here is a link for kohana 2.3 docs, using DB Queries is the same.