How to convert result value in column name using query?

Question

I have one view which contains following fields:

I got following result using following query:

select `key`,fieldvalue  FROM page_view WHERE language = 'EN' AND page_flag = 'imprint'

and got following result:

But i want above result in such a way so that i can use record as follows because i dont want to retrieve via loop:

title      link      dmeta    kmeta      content
imprint    imprint   --       --         --

So that i can access it via

$result['title'],$result['link']...

The result you are getting is in correct format. you can access it the way you require — undefined_variable, Dec 18 '15 at 13:23
no i cant use that way because it gives $result['key'] = title $result['fieldvalue'] = imprint in loop — Yadav Chetan, Dec 18 '15 at 13:26
you need to do pivot on table, example in mysql is hehe [link](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — xto, Dec 18 '15 at 13:27
Here is a link that you can use to do in a dynamic way: http://dba.stackexchange.com/questions/47902/how-to-transpose-convert-rows-as-columns-in-mysql — Jorge Campos, Dec 18 '15 at 13:33
see also [mysql dynamic crosstab query](http://stackoverflow.com/questions/34206766/mysql-dynamic-crosstab-query-selecting-child-records-as-additional-columns/34237336#34237336) — amdixon, Dec 20 '15 at 11:41

Yadav Chetan · Accepted Answer · 2015-12-21T09:57:22.620

I have done this with array to get this kind of functionality:

$q = $this->db->query('SELECT `key`,fieldvalue 
                                FROM page_view
                                WHERE page_flag = ? AND language = ?',array($page_flag,$language));
        if($q->num_rows() >0)
        {
            $result = array();
            $r = $q->result_array();
            foreach($r as $field)
            {
                $result[$field['key']] = $field['fieldvalue'];
            }
            echo '<pre>';
            print_r($result);

        }

Above code will give output as i want using query:

Array
(
    [title] => Imprint
    [link] => imprint
    [dmeta] => meta description
    [kmeta] => keywords
    [content] =>Impressum
)

How to convert result value in column name using query?

1 Answers1