• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Retrieving data from multiple tables

#1
[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";
  }

#2
[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?


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.