How to sort and put in tables according to customer with php.

Question

I have following db in mysql.

CREATE TABLE IF NOT EXISTS `be_users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL,
...

`active` tinyint(1) unsigned NOT NULL DEFAULT '0',
`group` int(10) unsigned DEFAULT NULL,
  ...

);


INSERT INTO `be_users` (`id`, `username`, `password`, `email`, `active`, `group`, `activation_key`, `last_visit`, `created`, `modified`) VALUES
(1, 'admin', '7209..b37', '[email protected]', 1, 2, NULL, '2010-09-26 21:32:46', '2010-07-09 14:16:46', '2010-08-03 14:05:31'),
(2, 'anne', 'a0e8...48ec1', '[email protected]', 1, 1, NULL, '2010-08-02 10:30:42', '2010-07-11 18:12:05', '2010-08-03 13:44:27'),
(3, 'tonje', '9855..be715baa', '[email protected]', 1, 1, NULL, NULL, '2010-07-31 08:20:25', '2010-08-03 13:44:56'),...

CREATE TABLE IF NOT EXISTS `be_user_profiles` (
  `user_id` int(10) unsigned NOT NULL,
  `company_name` varchar(100) NOT NULL,
...
...
  PRIMARY KEY (`user_id`)
) ;

INSERT INTO `be_user_profiles` (`user_id`, `company_name`, `full_name`, `web_address`, `phone_number`, `address`, `city`, `post_code`) VALUES
(1, '', '', '', '', '', '', 0),
(2, 'company1', 'Anne solberg', 'http://www.bla.no', 'test', '', '', 3222),
(3, 'company2', 'Tonje', 'http://www.bladesignshop.no/', '', '', '', 0),...


CREATE TABLE IF NOT EXISTS `omc_projects` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `cutomer_id` int(10) NOT NULL,
  `project_name` varchar(255) NOT NULL,
  `total_hr` int(10) NOT NULL,
  `created_by` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ;

INSERT INTO `omc_projects` (`id`, `cutomer_id`, `project_name`, `total_hr`, `created_by`) VALUES
(1, 2, 'website', 20, 'shin'),
(2, 3, 'website', 20, 'shin'),
(3, 2, 'logo', 4, 'shin');

I want to sort omc_projects according to customer_id like this output.

<table>
<tr><td>Company1</td></tr>
<tr><td>Website</td><td>    20</td></tr>
<tr><td>logo</td><td>     4</td></tr>
</table>
<table>
<tr><td>Company2<</td></tr>
<tr><td>Website</td><td>   20</td></tr>
</table>

etc.

I can get user_id and company_name like this.

function getAllMemberProfile($id=NULL){
    $data = array();
    $this->db->select('user_id,company_name');
    $this->db->from('be_user_profiles');
    $this->db->join('be_users', 'be_users.id = be_user_profiles.user_id');
    $this->db->where('be_users.group', $id);
    $query = $this->db->get();
    if ($query->num_rows() > 0){
       foreach ($query->result_array() as $row){
         $data[]=$row;
       }
    }
    $query->free_result();
    return $data;

}

but I am not sure how to get all project and put them in tables according to customers.

Any help will be appreciated.

Thanks in advance.

Answer 1

Try this once:

$this->db->order_by('be_users.id'); //place this after $this->db->where('be_users.group', $id);