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(*) 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`)') Code: $row->columnname Code: $row->MONTHNAME(`time`) 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() But it's show error then i try put every database column individually Code: $this->db->select('MONTHNAME(time) as month_name'); All idea is your so it can't be possible to me without your help. So thanks again buddy
[eluser]osci[/eluser]
Quote:Thanks dude it’s working it’s really helpful to mebut then... Quote:But it’s show error Its working but it's showing error :S Quote: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.
|
Welcome Guest, Not a member yet? Register Sign In |