CodeIgniter Forums

Full Version: Populating table based on value in database
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I'm trying to use code igniter to make a function that displays data in a table based on a specific value in the table. for example one of my columns in my sql displays a 1 I want to load all the rows that have 1 in that column. Then so on and so forth with the 2's etc. I want them all tied to one template page then accessed with the URI helper. I'm not really sure how to get this started I looked around the internet but to no aval.
If you haven't done so yet, you probably need to expand your design a bit to determine the following:
- the URI(s) you want to use for the given page(s)
- the SQL required to get the data
- how the user will navigate between pages, if necessary

Although you can change it in the routing, the URI(s) usually determine the naming of your controller and the methods/functions in that controller.

If you're selecting all rows with a particular value in one column only, then it's likely you could just pass the value as the first parameter of your method (which would also make the value the third segment of your URI, not counting your base URL), so you would just check the value to make sure it's reasonably safe to pass to the model, which would use it in the SQL to retrieve your data (possibly after performing more specific validation to reduce the chance that it will cause an error). If you need to filter on multiple columns, you may need additional parameters, but the basic idea is the same.

Once the controller has retrieved the data from the model, you pass it to a view, which is, more or less, your template.

The tutorial and other docs should help you get through most of it, but I'm sure people here can help with more specific questions, as well.
Let me explain my project a little deeper. Its for a train simulator information system I'm trying to gather data from 3 tables. One holds the value for the yard id and the yard name. The  second holds track id, the yard id (from the first table) and the info about the tracks (track name and length). The third table holds the information for the trains and has columns for track id and yard id. What I'm trying to do is have the user click on a link which goes to a view eg [URL]/atis/yards/1 would go to the first yard and the data with a yard id of 1 would be populated in a table. I'm trying to make one view that can be used dynamically.

I tried writing a function for this but it threw an error Column 'yard_id' in where clause is ambiguous

Code:
function getyard($yard_id)
  {
          $q = $this->db->select('*')
                           ->from('trains_yard')
                           ->join('yards', 'yard.id=yard_id','left')
                           ->join('trackdatabase', 'track.id=track_id', 'right')
                           ->where ('yard_id', $yard_id)
                           ->limit(1)
                           ->get();

          if($q->num_rows() > 0) {
                  foreach ($q->result() as $row) {
                          //$data[] = $row;
                          $data = $row->yard_id;
                  }

                  return $data;
          }
  }
}
When writing join statements, it's good practice to mention the table name or an alias for each table, like so:
PHP Code:
$q $this->db->select('*')
   ->
from('trains_yard t1')
   ->
join('yards t2''t2.id=t1.yard_id','left')
   ->
join('trackdatabase t3''t3.id=t2.track_id''right')
   ->
where ('t1.yard_id'$yard_id)
   ->
limit(1)
   ->
get(); 
The sql handles t1 as an alias for the table "trains_yard" etc.