Help me to solve database problem in codeigniter

[eluser][email protected][/eluser]
Hi,

I am new in CodeIgniter trying to create a small article's website for practice. All articles are coming from database every thing is working fine but in One query i got confuse i don't know how to call it by active record and use it in my view are following:

Code:

SELECT MONTHNAME(`time`), YEAR(`time`), COUNT(*)
FROM `articles`
GROUP BY MONTHNAME(`time`)
ORDER BY `id` DESC
LIMIT 0, 6;

Here i want to group my articles by month and get count of article in a particular month. But i have no idea how to implement it in my module and get records in my view. I have main problem in sql functions ie. MONTHNAME(`time`)

[eluser]InsiteFX[/eluser]
You need to show your database table so that we know what typr of fields you our using.

InsiteFX

[eluser][email protected][/eluser]
here articles is my table, time for time save as timestamp. function MONTHNAME(`time`) to get month from time saved as timestamp in database, YEAR(`time`) to get year from time and count(*) is to get count ie. how many article in data base of january.

Query working fine i checked it. But the main problem is when i am using active records in codeIgniter like

Code:

$this->db->select('YEAR(`time`)')

then it's not woking i think because of '`' but when i get array from my data base by using query method then i don't know how to display it in view. like normaly we do it for example

Code:

$row->columnname

But here i am getting time by MONTHNAME(`time`) but i can't use

Code:

$row->MONTHNAME(`time`)

. that's the main broblem.

I know there must any good way to do it.

I hope it make sense

[eluser]osci[/eluser]
try this
[code]
$this->db()
->select('MONTHNAME(`time`) as Month_name, YEAR(`time`) as Year_no, COUNT(*)')
->from(articles)
->group_by('MONTHNAME(`time`)','DESC')
->order_by('id','DESC')
->limit(0,6)
->get();
{/code]

so you can call the fields with the alias (AS) name.

[eluser][email protected][/eluser]
Thanks dude it's working it's really helpful to me. Actually there is only one change ie. as you said to use code below:

Code:

$this->db()
  ->select(‘MONTHNAME(`time`) as Month_name, YEAR(`time`) as Year_no, COUNT(*)’)
  ->from(articles)
  ->group_by(‘MONTHNAME(`time`)’,‘DESC’)
  ->order_by(‘id’,‘DESC’)
  ->limit(0,6)
  ->get();
{

But it's show error then i try put every database column individually

Code:

$this->db->select('MONTHNAME(time) as month_name');
$this->db->select('YEAR(time) as year_no');
$this->db->select('COUNT(*) as number');
$this->db->group_by('Year(time)');
$this->db->order_by('id', 'DESC');
$query = $this->db->get('articles');

All idea is your so it can't be possible to me without your help. So thanks again buddy

[eluser]osci[/eluser]

Thanks dude it’s working it’s really helpful to me

but then...

But it’s show error

Its working but it's showing error :S

All idea is your so it can’t be possible to me without your help

Well that's not absolutely true....
It's not quite my idea, I just tried translating your sql to AR and solve you the problem of refencing the fields that are functions.
Also mysql manual and codeigniter userguide is your help too. So it's possible to do this without me

Anyway, I can't really help you because you don't really provide what errors you are getting. Give more feedback at least.

[eluser]Unknown[/eluser]
thx a lot.
finally I fixed my problem