Get row values as column names using CodeIgniter and MySQL

Question

I'm using CodeIgniter and MySQL as db. I know it can be solved with MySQL query, but I wonder if it can be solved with a CodeIgniter query, otherwise normal PHP MySQL query can work also.

I have Table with 3 columns, it has many fields but I have shown here 4 entries, I didn't want to make a table with many columns but only 1 row of data. so instead I went for this style of table as someone suggested on this website.

Problem is I never worked with this kind of table.

       SettingsID           SettingsKey        SettingsValue
----------------------------------------------------------------------
          1            |     facebookLink     | facebook.com
          2            |     twitterLink      | twitter.com
          3            |     youtubeLink      | youtube.com
          4            |     googlePlusLink   | googleplus.com

Now I want to run a query that should return me rows in to columns. I searched over net and found some solutions but I am not good with these new queries.

I suppose this guy had same kind of problem like I have but in his case he has same value repeated in column, where my SettingsKey has all the values unique. Here is the link to similar kind of question :

mysql select dynamic row values as column names, another column as value

I can't understand his query and that query I am not sure if is any use of me.

How can I build a query to return row values of SettingsKey as columns?

So do you want to get the SettingsValue of each SettingsKey? — Bluedayz
– Bluedayz, Commented Aug 28, 2014 at 12:17

Bluedayz · Accepted Answer · 2014-08-28 12:36:13Z

1

This should work:

<?php
$db = new PDO('mysql:host=localhost;dbname=DBNAME;charset=utf8', USERNAME, PASSWORD);
$stmt = $db->query("SELECT SettingsKey, SettingsValue FROM Settings");
$links = array();
while($row = $stmt->fetch()) {
    $links[$row['SettingsKey']] = $row['SettingsValue'];
}
$db = null;

This code queries this table and for each row the SettingsKey and the SettingsValue will be shown.

Now you can get the facebookLink like this:

echo $links['facebookLink'];

Hope this helps.

edited Aug 28, 2014 at 12:36

answered Aug 28, 2014 at 12:20

Bluedayz

5996 silver badges17 bronze badges

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

5 Comments

Sizzling Code Over a year ago

yes. i could use that query easily in codeigniter, but i dont know how to explain this. What if i want the value of facebookLink in SettingsKey and echo it in form. like echo $facebookLink; how can i achieve that in your query?

Sizzling Code Over a year ago

Yes. i just finished with setting it up in my project, worked like a charm. Many Thanks... However i am not using PDO, so did some changes.

Bluedayz Over a year ago

So do you use mysqli? That's your choice of course. I used mysqli for a long time, but I fell in love with PDO :)

Sizzling Code Over a year ago

No i am using CodeIgniter with Smarty Templates, Codeigniter is a PHP framework. All i needed was help in database, i am not good in databases. {{foreach $data as $key =>$row}} {{$sKey[$row->settingsKey] = $row->settingsValue}} {{/foreach}}

Sizzling Code Over a year ago

No i am using CodeIgniter with Smarty Templates, Codeigniter is a PHP framework. All i needed was help in database, i am not good in databases. {{foreach $data as $key =>$row}} {{$sKey[$row->settingsKey] = $row->settingsValue}} {{/foreach}}

kay · Accepted Answer · 2014-08-28 21:09:46Z

0

$sql = "
        SELECT SettingsKey, SettingsValue
        FROM Settings
       ";

$q = $this->db->query($sql);
return $q->result_array();

Go to your controller

$data['settings'] = $this->model_selection->your_function();
$this->load->view('example', $data);

And lasty on your view

<?php if(!empty($settings)): ?>

       <?php foreach($settings as $k => $v): ?>

        //print your staff in here mix with html and go crazy
       <?php endforeach; ?>

<?php endif ?>

edited Aug 28, 2014 at 21:09

answered Aug 28, 2014 at 21:01

kay

3373 silver badges7 bronze badges

Collectives™ on Stack Overflow

Get row values as column names using CodeIgniter and MySQL

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related