[eluser]Amalgame[/eluser]
Hi all,
I'm trying to develop a software with CodeIgniter to classify my IP networks.
I just have a little problem with my entries to link categories ...
I copy my SQL tables and code to display this!
SQL tables:
Code: mysql> select * from bc_ids;
+------+------+------+------+
| i_id | t_id | c_id | h_id |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 2 |
| 3 | 1 | 2 | 3 |
| 4 | 1 | 3 | 4 |
| 5 | 2 | 1 | 5 |
| 6 | 2 | 2 | 6 |
| 7 | 2 | 2 | 7 |
| 8 | 2 | 3 | 8 |
+------+------+------+------+
mysql> select * from bc_tabs;
+------+------------+--------+
| t_id | t_name | t_desc |
+------+------------+--------+
| 1 | Totore | |
| 2 | Lion Heart | |
+------+------------+--------+
2 rows in set (0.00 sec)
mysql> select * from bc_categories;
+------+----------+--------+
| c_id | c_name | c_desc |
+------+----------+--------+
| 1 | Deadline | |
| 2 | Frontend | |
| 3 | Backend | |
+------+----------+--------+
3 rows in set (0.00 sec)
mysql> select * from bc_hosts;
+------+---------------+--------+---------------------+---------+------------+--------+-------------+-------------+--------------+----------+---------------------+
| h_id | h_ip | h_cidr | h_hostname | h_login | h_password | h_type | h_services | h_os | h_os_version | h_status | h_desc |
+------+---------------+--------+---------------------+---------+------------+--------+-------------+-------------+--------------+----------+---------------------+
| 1 | 192.168.1.1 | 24 | router.totore.local | admin | admin | vm | gw, ns, vpn | freebsd | 9.0 | on | freebsd router |
| 2 | 192.168.1.10 | 24 | www.totore.local | admin | admin | vm | http | freebsd | 9.0 | on | freebsd web server |
| 3 | 192.168.1.20 | 24 | mail.totore.local | admin | admin | vm | imap, smtp | freebsd | 9.0 | on | freebsd mail server |
| 4 | 192.168.1.200 | 24 | esxi.totore.local | admin | admin | server | vm | vmware esxi | 5.0.0 | on | vmware vsphere |
| 5 | 172.16.0.1 | 16 | router.lh.local | admin | admin | vm | gw, ns, vpn | freebsd | 9.0 | on | freebsd router |
| 6 | 172.16.0.10 | 16 | www.lh.local | admin | admin | vm | http | freebsd | 9.0 | on | freebsd web server |
| 7 | 172.16.0.20 | 16 | mail.lh.local | admin | admin | vm | imap, smtp | freebsd | 9.0 | on | freebsd mail server |
| 8 | 172.16.0.200 | 16 | esxi.lh.local | admin | admin | server | vm | vmware esxi | 5.0.0 | on | vmware vsphere |
+------+---------------+--------+---------------------+---------+------------+--------+-------------+-------------+--------------+----------+---------------------+
8 rows in set (0.00 sec)
In controller:
Code: public function view()
{
$this->db->where('t_id', $this->uri->segment(3));
$query = $this->db->get('ids');
if ( $query->num_rows() >= 1 )
{
$this->db->from('categories c');
$this->db->join('ids i', 'c.c_id = i.c_id', 'inner');
$this->db->where('i.t_id', $this->uri->segment(3));
$this->db->group_by('i.c_id');
$c_query = $this->db->get();
$this->db->from('categories c');
$this->db->join('ids i', 'c.c_id = i.c_id', 'inner');
$this->db->join('hosts h', 'h.h_id = i.h_id', 'inner');
$this->db->where('i.t_id', $this->uri->segment(3));
$this->db->where('i.c_id', 2);
$h_query = $this->db->get();
$data = array(
'c_values' => $c_query->result(),
'h_values' => $h_query->result()
);
$this->parser->parse('roster_view', $data);
}
else
{
redirect('roster');
}
}
In view:
Code: <table width="100%">
<thead>
<tr>
<td>IP</td>
<td>CIDR</td>
<td>Hostname</td>
<td>Login</td>
<td>Password</td>
<td>Type</td>
<td>Services</td>
<td>OS</td>
<td>Version</td>
<td>Status</td>
<td>Note</td>
</tr>
</thead>
<tbody>
{c_values}
<tr>
<td colspan="11">{c_name}</td>
</tr>
{h_values}
<tr>
<td>{h_ip}</td>
<td>{h_cidr}</td>
<td>{h_hostname}</td>
<td>{h_login}</td>
<td>{h_password}</td>
<td>{h_type}</td>
<td>{h_services}</td>
<td>{h_os}</td>
<td>{h_os_version}</td>
<td>{h_status}</td>
<td>{h_desc}</td>
</tr>
{/h_values}
{/c_values}
</tbody>
</table>
Currently, I can not create a dynamic id for hosts connected to the categories ...
In the SQL query I entered an id but it appears three times the same entries in all three categories when it should not be the case, of course!
You can see the result it gives here: http://88.190.215.218/blackcurrant/index...ter/view/1
Thanks for your help and i'm sorry for my bad english :/
[eluser]gRoberts[/eluser]
Am I right in thinking that you just want to display the unique ID for the category within your table?
If so, can't you just use {c_id} as it's a column your currently selecting?
Failing that, you can always use
Code: $this->db->select('c.c_id as CategoryID, h.*');
to specify which actual columns you need and you can also rename those that may be overwritten by other columns?
[eluser]Amalgame[/eluser]
Hi gRoberts,
That's right, I want to display entries in each category that are bound by the table bc_ids!
Currently with only one request I get my display has three categories and my entries but when I use the group_by for "Frontend" it shows me an input on two ...
Code: public function view()
{
$this->db->where('t_id', $this->uri->segment(3));
$query = $this->db->get('ids');
if ( $query->num_rows() >= 1 )
{
$this->db->from('categories c');
$this->db->join('ids i', 'c.c_id = i.c_id', 'inner');
$this->db->join('hosts h', 'h.h_id = i.h_id', 'inner');
$this->db->where('i.t_id', $this->uri->segment(3));
$this->db->group_by('i.c_id');
$query = $this->db->get();
$data = array(
'values' => $query->result()
);
$this->parser->parse('roster_view', $data);
}
else
{
redirect('roster');
}
}
I do not understand the purpose of your select?
Thank you for your help
[eluser]gRoberts[/eluser]
If you don't "group" your query, what results do you see? I'm not sure if I understand what you mean by "it shows me an input on two".
I assume this means that without the group, you get one result and with the group, you two two results, or vice versa?
Grouping will consolidate multiple rows with the same values into one row. Are you looking at doing this, or are you simply wanting to display the list of items in order of Category?
[eluser]Amalgame[/eluser]
In the "Frontend" there are two entries, when I use group_by, I shall post the name of the class but only one entry on both...
Without group_by he displays well the two entrances but it appears to me twice "Frontend"...
With group_by:
Quote:IP CIDR Hostname Login Password Type Services OS Version Status Note
Deadline
192.168.1.1 24 router.totore.local admin admin vm gw, ns, vpn freebsd 9.0 on freebsd router
Frontend
192.168.1.10 24 www.totore.local admin admin vm http freebsd 9.0 on freebsd web server
Backend
192.168.1.200 24 esxi.totore.local admin admin server vm vmware esxi 5.0.0 on vmware vsphere
Without group_by:
Quote:IP CIDR Hostname Login Password Type Services OS Version Status Note
Deadline
192.168.1.1 24 router.totore.local admin admin vm gw, ns, vpn freebsd 9.0 on freebsd router
Frontend
192.168.1.10 24 www.totore.local admin admin vm http freebsd 9.0 on freebsd web server
Frontend
192.168.1.20 24 mail.totore.local admin admin vm imap, smtp freebsd 9.0 on freebsd mail server
Backend
192.168.1.200 24 esxi.totore.local admin admin server vm vmware esxi 5.0.0 on vmware vsphere
[eluser]gRoberts[/eluser]
Ah, I think I see the problem... Because you are selecting from Categories, I think the group demotes the data from ids or hosts.
Instead, what you should really be doing is selecting from the Hosts table and then joining the others after.
try
Code: $this->db->join('ids i', 'h.h_id = i.h_id');
$this->db->join('categories c', 'c.c_id = i.c_id');
$this->db->where('i.t_id', $this->uri->segment(3));
$this->db->group_by('c.c_id');
$query = $this->db->get('hosts h');
instead?
[eluser]Amalgame[/eluser]
I made the change with your order, here is the result ...
It does not display the way I want :/
Quote:IP CIDR Hostname Login Password Type Services OS Version Status Note
Deadline
192.168.1.1 24 router.totore.local admin admin vm gw, ns, vpn freebsd 9.0 on freebsd router
192.168.1.10 24 www.totore.local admin admin vm http freebsd 9.0 on freebsd web server
192.168.1.200 24 esxi.totore.local admin admin server vm vmware esxi 5.0.0 on vmware vsphere
Frontend
192.168.1.1 24 router.totore.local admin admin vm gw, ns, vpn freebsd 9.0 on freebsd router
192.168.1.10 24 www.totore.local admin admin vm http freebsd 9.0 on freebsd web server
192.168.1.200 24 esxi.totore.local admin admin server vm vmware esxi 5.0.0 on vmware vsphere
Backend
192.168.1.1 24 router.totore.local admin admin vm gw, ns, vpn freebsd 9.0 on freebsd router
192.168.1.10 24 www.totore.local admin admin vm http freebsd 9.0 on freebsd web server
192.168.1.200 24 esxi.totore.local admin admin server vm vmware esxi 5.0.0 on vmware vsphere
Thanks
[eluser]Amalgame[/eluser]
Oops...
I changed my code (completly)
Code: public function view()
{
$this->db->where('t_id', $this->uri->segment(3));
$query = $this->db->get('ids');
if ( $query->num_rows() >= 1 )
{
$this->db->from('hosts h');
$this->db->join('ids i', 'h.h_id = i.h_id');
$this->db->join('categories c', 'c.c_id = i.c_id');
$this->db->where('i.t_id', $this->uri->segment(3));
$this->db->group_by('i.c_id');
$s_query = $this->db->get();
$data = array(
'values' => $s_query->result()
);
$this->parser->parse('roster_view', $data);
}
else
{
redirect('roster');
}
}
The result:
Quote:IP CIDR Hostname Login Password Type Services OS Version Status Note
Deadline
192.168.1.1 24 router.totore.local admin admin vm gw, ns, vpn freebsd 9.0 on freebsd router
Frontend
192.168.1.10 24 www.totore.local admin admin vm http freebsd 9.0 on freebsd web server
Backend
192.168.1.200 24 esxi.totore.local admin admin server vm vmware esxi 5.0.0 on vmware vsphere
The result is beginning to take shape, I'm missing an entry for "Frontend" which is the line with "192.168.1.20" 
[eluser]gRoberts[/eluser]
Try removing the "group_by" now and see how it looks?
Within your template, I noticed you were looping through c_values and then h_values but h_values would technically display all of the rows regardless of the category.
AS a result, you will need to do an if statement to filter out those rows that do not belong to the category your in?
[eluser]Amalgame[/eluser]
When I changed the code in the controller I also changed the code in view
Controller:
Code: public function view()
{
$this->db->where('t_id', $this->uri->segment(3));
$query = $this->db->get('ids');
if ( $query->num_rows() >= 1 )
{
$this->db->from('hosts h');
$this->db->join('ids i', 'h.h_id = i.h_id');
$this->db->join('categories c', 'c.c_id = i.c_id');
$this->db->where('i.t_id', $this->uri->segment(3));
$s_query = $this->db->get();
$data = array(
'values' => $s_query->result()
);
$this->parser->parse('roster_view', $data);
}
else
{
redirect('roster');
}
}
View:
Code: <tbody>
{values}
<tr>
<td colspan="13">{c_name}</td>
</tr>
<tr>
<td>{h_ip}</td>
<td>{h_cidr}</td>
<td>{h_hostname}</td>
<td>{h_login}</td>
<td>{h_password}</td>
<td>{h_type}</td>
<td>{h_services}</td>
<td>{h_os}</td>
<td>{h_os_version}</td>
<td>{h_status}</td>
<td>{h_desc}</td>
<td><?php // echo anchor('roster/edit/{id}', 'edit'); ?></td>
<td><?php // echo anchor('roster/delete/{id}', 'delete'); ?></td>
</tr>
{/values}
</tbody>
Result without group_by:
Quote:IP CIDR Hostname Login Password Type Services OS Version Status Note
Deadline
192.168.1.1 24 router.totore.local admin admin vm gw, ns, vpn freebsd 9.0 on freebsd router
Frontend
192.168.1.10 24 www.totore.local admin admin vm http freebsd 9.0 on freebsd web server
Frontend
192.168.1.20 24 mail.totore.local admin admin vm imap, smtp freebsd 9.0 on freebsd mail server
Backend
192.168.1.200 24 esxi.totore.local admin admin server vm vmware esxi 5.0.0 on vmware vsphere
The result is the same as a few posts before!
|
