Help, how to setup career postings

[eluser]xavier44[/eluser]
Hey, having some issues trying to wrap my head around this. I have a table called stores and a table called careers in the database. In stores I have fldStoreID and then in careers have fldStoreID so I can get the results from stores when it matches the careers.

Here is where I get all messed up. How can I get it so I list all stores and then under each store I list all the available career position. So it would look like this.

I can get the models and controllers to display each table separately, but would like to make it look like this.

Store 1
- position 1
- position 2
Store 2
Store 3
- position 3

I am new to codeigniter framework and want to learn the right way to do it instead of starting bad practices from the beginning. If need more information let me know, Ill try my best to provide whats needed.

Thanks for any help

[eluser]achilleusrage[/eluser]
I think you can get the results you need with a RIGHT OUTER JOIN, like this:

<b>In your model:</b>

Code:

$this->db->select("careers.fldPositionTitle,stores.fldStoreName");
$this->db->from("careers");
$this->db->join("stores","careers.fldStoreID = stores.fldStoreID","right outer");
$this->db->order_by("stores.fldStoreName","ASC");

$q = $this->get();

$STORE_POSITIONS = q->result_array();
return $STORE_POSTIONS;

Should yield:

Code:

fldStoreName | fldPositionTitle
-----------------------------------
store 1      | position 1
store 1      | position 2
store 2      | NULL
store 3      | position 1

[eluser]xavier44[/eluser]
Thanks for quick reply achilleusrage. That is pretty close to what I am looking for. The only thing I need to change is the way they are sorted. I need all positions of a store to show up under the related store but without repeating the store name. Like this.

Code:

fldStoreName | fldPositionTitle
-----------------------------------
store 1      | position 1
             | position 2
             | position 3

store 2      | NULL
store 3      | position 1

store 4      | position 1
             | position 2

Does this make sense? Is it doable? What comes to mind is a foreach statement. Is there way to do something like that with the model or controller?

[eluser]CroNiX[/eluser]
Something like this might work (untested)

Code:

$last_store = '';  // Keep track of the last store name used
foreach($stores as $store)
{
  // Only output the store name if it isn't the same as the previous
  if ($store['fldStoreName'] !== $last_store)
  {
    echo '<strong>' . $store['fldStoreName'] . '</strong><br />';
  }
  $last_store = $store['fldStoreName'];  //assign the store name to the last used
  echo $store['fldPositionTitle'] . '<br />';  //output the titles
}

[eluser]CroNiX[/eluser]
You might also sort by the position titles after the store names, so they show up in order too.

Code:

$this->db->order_by("stores.fldStoreName", "ASC");
$this->db->order_by("careers.fldPositionTitle", "ASC");

[eluser]xavier44[/eluser]
Thank you so much exactly what I was looking for. You both are brilliant and I appreciate you taking the time to teach/help me.