CodeIgniter Forums
Retrieving data from multiple tables - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Retrieving data from multiple tables (/thread-27574.html)



Retrieving data from multiple tables - El Forum - 02-15-2010

[eluser]sico87[/eluser]
Hello there I have a database that has 2 tables in it, one is the categoryTable and the is the userMenuTable, the categoryTable currently has two columns in it, categoryId and categoryTitle it currently holds 2 rows of data, the categoryId's = 1 and 2 and the categoryTitles = News and Blog, in the the userMenuTable I keep a record of what categories that user has selected, the table has 3 columns, menuEntryId, categoryId and cookieId, this table keeps a record of which cookie has which category selected, the ID is to then run these queries,

The first query, gets the users selected categories
Code:
function getMenu($cookieId) {
  $this->db->select('*');
  $this->db->from('categoryTable');
  $this->db->join('userMenuTable', 'categoryTable.categoryId = userMenuTable.categoryId', 'left');
  $this->db->where('userMenuTable.cookieId', $cookieId);

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

}

The next query gets all the categories that have no cookieId assigned to them,

Code:
function getAllMenus($cookieId) {
$sql ="SELECT categoryTable.categoryTitle, categoryTable.categoryId, userMenuTable.cookieId, userMenuTable.menuEntryId, categoryTable.categoryOnline,
categoryTable.categoryIsSpecial, categoryTable.categoryDateCreated, categoryTable.categorySlug, categoryTable.dashboardUserId, categoryTable.categoryAbstract
FROM categoryTable LEFT JOIN userMenuTable
   ON categoryTable.categoryId = userMenuTable.categoryId
UNION ALL
SELECT categoryTable.categoryTitle, categoryTable.categoryId, userMenuTable.cookieId, userMenuTable.menuEntryId, categoryTable.categoryOnline,
categoryTable.categoryIsSpecial, categoryTable.categoryDateCreated, categoryTable.categorySlug, categoryTable.dashboardUserId, categoryTable.categoryAbstract FROM categoryTable RIGHT JOIN userMenuTable
   ON categoryTable.categoryId = userMenuTable.categoryId
WHERE userMenuTable.cookieId = NULL";

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

however this returns an array that looks like this,
Code:
[0] => Array
    (
        [categoryId] => 1
        [categoryTitle] => blog
        [categoryAbstract] => <p>asdsdsadasdsadfdsgdgdsgdsgssssssssssss</p>
        [categorySlug] => blog
        [categoryIsSpecial] => 0
        [categoryOnline] => 1
        [categoryDateCreated] => 1265123745
        [dashboardUserId] => 0
        [menuEntryId] => 5
        [cookieId] => bang4b696152b4869
    )

[1] => Array
    (
        [categoryId] => 8
        [categoryTitle] => News
        [categoryAbstract] => <p>The world at Bang Marketing moves fast, keep up to date w
        [categorySlug] => news
        [categoryIsSpecial] => 0
        [categoryOnline] => 1
        [categoryDateCreated] => 1265283717
        [dashboardUserId] => 0
        [menuEntryId] => 6
        [cookieId] => bang4b696152b4869
    )

[2] => Array
    (
        [categoryTitle] => blog
        [categoryId] => 1
        [cookieId] => bang4b696152b4869
        [menuEntryId] => 5
        [categoryOnline] => 1
        [categoryIsSpecial] => 0
        [categoryDateCreated] => 1265123745
        [categorySlug] => blog
        [dashboardUserId] => 0
        [categoryAbstract] => <p>asdsdsadasdsadfdsgdgdsgdsgssssssssssss</p>
    )

[3] => Array
    (
        [categoryTitle] => News
        [categoryId] => 8
        [cookieId] => bang4b696152b4869
        [menuEntryId] => 6
        [categoryOnline] => 1
        [categoryIsSpecial] => 0
        [categoryDateCreated] => 1265283717
        [categorySlug] => news
        [dashboardUserId] => 0
        [categoryAbstract] => <p>The world at Bang Marketing moves fast, keep up to date w
    )
)

Somehow I need to build a query that returns all the categories that are in the categoryTable and checks that against there it's Id matches one that is in the userMenuTable where the cookieId matches that of the users, and then return an array so I can loop through it like this,
Code:
if(isset($mainMenu)) {
   //die(print_r($mainMenu));
   foreach ($mainMenu as $k => $v) {
    if($v['menuEntryId'] == '') {
     echo "<li class='menuItem'>
     <a href='".base_url()."welcome/getContent/$v[categoryId]' class='navLink' id='$v[categoryTitle]'>".$v['categoryTitle']."</a>
     </li>";
    } else {
     echo "<li class='menuItem'>
     <a href='".base_url()."welcome/getContent/$v[categoryId]' class='saved navLink' id='$v[categoryTitle]'>".$v['categoryTitle']."</a>
     </li>";
    }
   }
  } else {
   // do something else
   //echo "here";
  }



Retrieving data from multiple tables - El Forum - 02-15-2010

[eluser]danmontgomery[/eluser]
I think you're making this more complicated than it needs to be... You only need one query:

Code:
$sql = "SELECT categoryTable.categoryTitle, categoryTable.categoryId, userMenuTable.cookieId, userMenuTable.menuEntryId, categoryTable.categoryOnline,
categoryTable.categoryIsSpecial, categoryTable.categoryDateCreated, categoryTable.categorySlug, categoryTable.dashboardUserId, categoryTable.categoryAbstract
FROM categoryTable
JOIN userMenuTable ON categoryTable.categoryId = userMenuTable.categoryId
WHERE userMenuTable.cookieId = 'some_string'";

Code:
if(isset($mainMenu)) {
  foreach ($mainMenu as $k => $v) {
    echo "<li class='menuItem'>
      <a href='".base_url()."welcome/getContent/$v[categoryId]' class='" . ( strlen($v["menuEntryId"]) ? "saved " : "" ) . "navLink' id='$v[categoryTitle]'>".$v['categoryTitle']."</a>
      </li>";
  }
} else {
  ...
}

You're not very clear about what you're trying to accomplish (or maybe I'm just confused by the db structure)... If that's not what you're looking for, can you be more specific about the result you're trying to get?