1

im using codeigniter and mysql as db. i know might can be solved with mysql query, but if anyone can solve with codeigniter query it will be awesome, otherwise normal PHP mysql query can work also.

I have Table with 3 columns, Well it have many feilds but i have shown here 4 enteries, i didnt wanted 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 me 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 this 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 cant understand his query and that query i am not sure if is any use of me.

Please can anyone help me build a query to return row values of SettingsKey as columns.

Community
  • 1
  • 1
Sizzling Code
  • 5,932
  • 18
  • 81
  • 138

2 Answers2

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.

Bluedayz
  • 599
  • 5
  • 17
  • 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 Aug 28 '14 at 12:25
  • 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. – Sizzling Code Aug 28 '14 at 12:47
  • 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 :) – Bluedayz Aug 28 '14 at 13:01
  • 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 Aug 28 '14 at 13:04
  • 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 Aug 28 '14 at 13:04
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 ?>
kay
  • 337
  • 3
  • 7