getting variable from a query

[eluser]london77[/eluser]
Hi,

I have a table that shows my income and expenses. I wrote a query to get find my total income or expenses.

and i write it like this..

Code:

$query = $this->db->query("SELECT SUM(amount) AS mnt FROM my_account WHERE inex='Income' and type='Business' ");
        
        return $query->result();

but to get the total as a variable i have to use

foreach

I was wondering if there is a better way to get these query results as a variable so that i can make some calculations with them.


thanks in advance.

[eluser]TWP Marketing[/eluser]
From the USer Guide

file:///home/twp/Dev/CodeIgniter_2.1.2/user_guide/database/results.html

$Query->result() returns an object

$query->result_array() returns an array, which you might find easier to manipulate with foreach.
Read the User Guide page for examples.

[eluser]london77[/eluser]
Hi, thanks for the quick reply.

But i was trying to skip the use of foreach.

Because each time i have to use for each.

Imagine , for this database i need to run many queries and each queries has returning a single row( sums). i like to assign this single row result to a variable without using "foreach".

thanks

[eluser]TWP Marketing[/eluser]
See the User Guide page:

Code:

$query = $this->db->query("YOUR QUERY");

if ($query->num_rows() > 0)
{
   $row = $query->row();

   echo $row->title;
   echo $row->name;
   echo $row->body;
}

[eluser]london77[/eluser]
Thanks, i have seen this but code is still long.
I suppose no other way around.

[eluser]TWP Marketing[/eluser]
[quote author="TWP Marketing" date="1347574787"]See the User Guide page:

Code:

$query = $this->db->query("YOUR QUERY");

if ($query->num_rows() > 0)
{
   return( $query->row() );
}

[/quote]

How much smaller can you get and still check your results?

[eluser]london77[/eluser]
how can i get this return value as a variable?

Code:

return( $query->row()

[eluser]TWP Marketing[/eluser]
[quote author="london77" date="1347575602"]how can i get this return value as a variable?

Code:

return( $query->row()

[/quote]

In your controller:

$record = $this->Your_model->Your_method(); // call the db query method in your model

$record->somevar;
$record->someothervar;

What you do with the data is up to you.
Your question is too generic to give you an answer. If you are using the MVC prardyme, then you have a controller and a model with some view to display your data. READ the User Guide...

[eluser]CroNiX[/eluser]
If you know you are only going to get one result, use db::row();

Code:

$query = $this->db
  ->query("SELECT SUM(amount) AS mnt FROM my_account WHERE inex='Income' and type='Business' ")
  ->row();

//if we found the result, return the sum.
if ($query->num_rows() == 1)
{
   return( $query->mnt );
}
//if not, return 0 (or whatever appropriate);
return 0;

In your controller:

Code:

$total = $this->your_model->your_totaling_method();

[eluser]CroNiX[/eluser]
or using pure Active Record

Code:

$query = $this->db
  ->select_sum('amount');
  ->where('inex', 'Income')
  ->where('type', 'Business')
  ->get('my_account')
  ->row();

But then you'd

Code:

return $query->amount;