Converting a Mysql Result Object to Associative Array (CodeIgniter)

Question

I'm trying to get a database query which is an object converted to an associative array, so that I can use it in the calendar class in codeigniter.

This is my model:

<?php

class Get_diary_model extends Model {
    
    function getAllDiaries($year,$month) {
        
        $data = $this->db->query("SELECT day AND entry FROM diary WHERE month=$month AND year=$year"); // the entries for the relevant month and year
        
        foreach($data->result_array() as $row) { // return result as assoc array to use in calendar
            echo $row['day'];
            echo $row['entry'];
        }

        return $data;
        }
    }

and this is the error I get:

Fatal error: Cannot use object of type CI_DB_mysql_result as array in C:\wamp\www\mm\system\libraries\Calendar.php on line 219

This is a typo question. Your query's SELECT is asking for day AND entry, but it should be day, entry. This should be return $this->select('day, entry')->get_where('diary', ['month' => $month, 'year' => $year])->result_array(); — mickmackusa
– mickmackusa ♦, Commented Nov 5, 2023 at 22:51
Given the question's title, it is unclear what your desired result is. — mickmackusa
– mickmackusa ♦, Commented Nov 5, 2023 at 23:43

Christophe · Accepted Answer · 2010-09-16 11:14:09Z

6

Check ou this video tutorial, it will help you -> http://net.tutsplus.com/tutorials/php/codeigniter-from-scratch-the-calendar-library/

Your model should look like this:

    function getAllDiaries($year,$month)
    {
        $q = $this->db->query("SELECT day AND entry FROM diary WHERE month=$month AND year=$year");

        if($q->num_rows() > 0):
            foreach($q->result() as $row):
                $data[] = $row;
            endforeach;
            return $data;
        else:
            return false;
        endif;
    }

and your controller:

    function index($year = null, $month = null)
    {
        $this->load->model('Get_diary_model');

        if (!$year) {
            $year = date('Y');
        }
        if (!$month) {
            $month = date('m');
        }

        $data['calendar'] = $this->Get_diary_model->getAllDiaries($year, $month);
}

answered Sep 16, 2010 at 11:14

Christophe

4,8285 gold badges46 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Robimp Over a year ago

Perfect! Thank you very much, my controller is similar to that, I just couldn't figure out the model part. Will check out the video as well...

Radek Simko Over a year ago

I'm giving downvote because of obvious SQL injection... It might solve the problem (stated in question), but the code doesn't care about any escaping at all, therefore it might bring more problems.

Christophe Over a year ago

You are of course correct about the sql injection but that was not initially the problem and therefore the downvote is a bit unfair. If I had not answered the question correctly I would understand.

Phil Sturgeon · Accepted Answer · 2010-09-17 08:31:40Z

3

The problem was not in your use of result_array(), more that you later return $data directly. $query = $this->db->query() then use $query->result_array() in the foreach. Then you can return $data after building it up in the foreach.

The other answer is a long-winded way of writing the following:

function getAllDiaries($year,$month)
{
    $sql = "SELECT day AND entry FROM diary WHERE month=$month AND year=$year";
    return $this->db->query($sql)->result();
}

But of course that will return an array of objects, not an multidimensional array.

edited Sep 17, 2010 at 8:31

answered Sep 16, 2010 at 13:01

Phil Sturgeon

30.8k12 gold badges82 silver badges118 bronze badges

2 Comments

Robimp Over a year ago

Hi Phil thanks for the reply, I gave that a try but it doesn't quite do what I want. I'm trying to get the 'day' field from the db to be the array key, and the 'entry' the value. So far, no luck.

Phil Sturgeon Over a year ago

var_dump() is the answer here. Whenever you get an unexpected result, var_dump(), see what is there and act accordingly. To get an assoc array you are using the correct syntax. It is something in your foreach or array manipulation later that is going wrong.

Kiran · Accepted Answer · 2015-04-10 13:41:25Z

1

Use below simple method,

$query = $this->db->get_where('table', array('table_id' => $id));

        $queryArr = $query->result();        

        foreach ($queryArr[0] as $key=>$val)
        {
            $row[$key]=$val;
        }

print_r($row); //this will give associative array

answered Apr 10, 2015 at 13:41

Kiran

1,2202 gold badges16 silver badges27 bronze badges

1 Comment

mickmackusa Over a year ago

It makes no sense to collect the result set data with result(), then use a foreach() to iterate the first row (which might not exist), then populate a new variable ($row) which is identical to $queryArr[0]. This is a not-well-thought-out answer. Just use row_array()

Matt Raines · Accepted Answer · 2016-08-21 22:14:00Z

0

Here is the solution for CodeIgniter-3

function getAllDiaries(){
    $query = $this->db->query("YOUR QUERY HERE");
    return $query->result('array');
}

OR

function getAllDiaries(){
    return $this->db->query("YOUR QUERY HERE")->result('array');
}

Note: result() function accept "array" or "object" as parameter. Default is "object"

edited Aug 21, 2016 at 22:14

Matt Raines

4,2478 gold badges34 silver badges38 bronze badges

answered Aug 21, 2016 at 22:02

Sumon Sarker

2,7971 gold badge27 silver badges37 bronze badges

Collectives™ on Stack Overflow

Converting a Mysql Result Object to Associative Array (CodeIgniter)

4 Answers 4

3 Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related