Welcome Guest, Not a member yet? Register   Sign In
Basic Report output i can't get my head around
#1

[eluser]TornUp[/eluser]
Hi All!

Going to sound really n00b like and ask for some help in a query/output I'm trying to-do.

im currently producing an electronic phone list in CI, all users are stored in a tables with the following layout:

Users
Code:
id
Name
PhoneExt
DirectDial
Location (links to location->id)


Location:
Code:
id
Title


Now what I am trying to achieve is the following:


New York:
John Smith
Fred Jones
Katie bing

London:
Emma flop
Andy Flip

and so on, and so on....

can someone help me getting my head around the MySql query/passing it to the view via the controller.

I see it done all the time, but the concept confuses me! Sad

Many Thanks

Tom Kirby.
#2

[eluser]mddd[/eluser]
You need to JOIN the list of locations to the list of people.
Then you can loop through the results. Each result will contain the person and his/her location.
If the location changes, you know you've gone to the next list of people.
Like so:
Code:
// get the info
$this->db->select('location.title as locationtitle, users.*');
$this->db->from('location');
$this->db->join('users', 'users.location = location.id', 'left');
$list = $this->db->get()->result_array();

// show the info
$currentlocation = '';
foreach ($list as $item)
{
   // show location title if coming into a new location
   if ($item['locationtitle']!=$currentlocation)
   {
      echo '<p><b>'.$item['locationtitle'].'</b></p>';
      $currentlocation=$item['locationtitle'];
   }
   // show item
   echo '<p>'.$item['name'].'</p>';
}
For more info check out Mysql JOIN and after that see the CI manual Active Record page for how you write JOIN in CI.
#3

[eluser]TornUp[/eluser]
Hi Mddd,

Iv done MYSql Joins before, I was just not sure on how to loop through the items so they all sit under one title till there is nothing left for that title, then it jumps to the next title...

Kinda got my head around your code.. will give it a try..

Thanks! Smile
#4

[eluser]TornUp[/eluser]
Hi All,

So iv run into a slight problem...

the loop above is PERFECT! but.... i need to put somthing on the end, once the loop has finished... for example:


%insert @ Top%
<div class="title>LOCATION TITLE</div>
%loop start%
<div class="main numbers">
NAME - Number - Ext - Etc..
</div>
%loop end%
%insert @ Bottom%
<div class="closing"></div>


I just can't get my head around how i make it only output the closing div ONCE once the loop has finished! Sad

Help!?
#5

[eluser]TornUp[/eluser]
Sorry to bump this.... But I am really in need of finishing this project off today?

can anyone help?
#6

[eluser]mddd[/eluser]
I think it's not so hard. Just a bit of logical thinking.
If I get you right, you need to show the 'closing' div after each location? Well, how do you know it's the last one? You could look at the next one coming up. Like so:
Code:
// show the info
$currentlocation = '';
foreach ($list as $index=>$item)     // --- added $index to keep track of the keys AND values
{
   // show location title if coming into a new location
   if ($item['locationtitle']!=$currentlocation)
   {
      echo '<p><b>'.$item['locationtitle'].'</b></p>';
      $currentlocation=$item['locationtitle'];
   }
   // show item
   echo '<p>'.$item['name'].'</p>';

   // was this the last one?
   // that's the case if the locationtitle for the next item in line is different.
   // OR if there is no next item in line (so this is the very last one)
   if (!isset($list[($index+1)]) || $list[($index+1)]['locationtitle'] != $item['locationtitle'])
   {
      // show your ending div here
   }
}
See? Not too difficult if you take a logical approach.




Theme © iAndrew 2016 - Forum software by © MyBB