How To Make Codeigniter Query Result Into a Table

Question

I would like to create HTML table with my query results. The HTML table should look like this:

I don't really understand about Codeigniter's HTML Table Class, so I just create a loop for each column of the table on my view like this:

<tr>
<?php foreach ($nameloop as $row) { ?> <td><?php echo $row->username; ?> </td><?php } ?>
<?php foreach ($addressloop as $row) { ?> <td><?php echo $row->useraddress; ?> </td><?php } ?>
<?php foreach ($ageloop as $row) { ?> <td><?php echo $row->userage; ?></td> <?php } ?>
<?php foreach ($sexloop as $row) { ?> <td><?php echo $row->usersex; ?></td> <?php } ?>
</tr>

Turns out it was a very bad decision. It makes my table look horizontally instead vertically like it used to.

Here's my model:

<?php class Front_model extends CI_Model {

        public function __construct()
        {
                parent::__construct();
                $CI = &get_instance();
                $this->db2 = $CI->load->database('db2', TRUE);
        }


        function namelist()
        {
            $query = $this->db->query("SELECT name as uername FROM table1 ORDER BY name");
            return $query->result();
        }

        function addresslist()
        {
            $query = $this->db->query("SELECT address as useraddress FROM table1 INNER JOIN name
                                       ON address.id = name.id GROUP BY id ORDER BY name");
            return $query->result();
        }

        function agelist()
        {
            $query = $this->db->query("SELECT age as userage FROM table1 INNER JOIN name
                                       ON age.id = name.id GROUP BY id ORDER BY name");
            return $query->result();
        }

        function sexlist()
        {
            $query = $this->db->query("SELECT sex as usersex FROM table1 INNER JOIN name
                                       ON sex.id = name.id GROUP BY id ORDER BY name");
            return $query->result();
        }

}

And then here's my function on my CONTROLLER:

public function index()
    {

        $data['nameloop'] = $this->front_model->namelist();
        $data['addressloop'] = $this->front_model->addresslist();
        $data['ageloop'] = $this->front_model->agelist();
        $data['sexloop'] = $this->front_model->sexlist();


        $this->load->view('home',$data);
    }

This make me quite confused. Whether it's the HTML tag or I code it wrong.. I don't know... Thank you for your help, guys...

Answer 1

You can really do this in a simpler way.

Your model can be simplified to get all the data with one method

<?php class Front_model extends CI_Model {

        public function __construct()
        {
                parent::__construct();
                $CI = &get_instance();
                $this->db2 = $CI->load->database('db2', TRUE);
        }

        function userInformation()
        {
            $query = $this->db->query("SELECT name as username, address as useraddress, age as userage, sex as usersex FROM table1 INNER JOIN name ON table1.id = name.id GROUP BY id ORDER BY name");
            return $query->result();
        }

}

And then in your controller you can just call this one method instead

public function index()
    {
        $data['information'] = $this->front_model->userInformation();
        $this->load->view('home',$data);
    }

And finally in your view, you can just your one foreach

<tr>
<?php foreach ($information as $row) { ?> 
    <td><?php echo $row->username; ?></td>
    <td><?php echo $row->useraddress; ?></td>
    <td><?php echo $row->userage; ?></td>
    <td><?php echo $row->usersex; ?></td>
<?php } ?>
</tr>

Hope that helped.

Although I recommend using CI's table library!

UPDATE

If you want to work with your existing queries, then I recommend you modify your controller like this -

public function index()
    {

        $names = $this->front_model->namelist();
        $addresses = $this->front_model->addresslist();
        $ages = $this->front_model->agelist();
        $sexes = $this->front_model->sexlist();

        $length = sizeOf($names);
        $data['information'] = [];

        for ($i = 0; $i <= $length; $i++) {
           $data['information'][$i] = [$names[$i], $addresses[$i], $ages[$i], ];
        }

        $this->load->view('home',$data);
    }

Update your view like this -

<tr>
<?php foreach ($information as $info) { ?> 
    <td><?php echo $info[0]->username; ?></td>
    <td><?php echo $info[1]->useraddress; ?></td>
    <td><?php echo $info[2]->userage; ?></td>
    <td><?php echo $info[3]->usersex; ?></td>
<?php } ?>
</tr>