Multidimensional arrays

[eluser]PvOostrom[/eluser]
Hello there,

I have been struggling with this one for quiet some time now, but still can't get it done somehow in CodeIgniter.

Any idea on how, and if it is even possible to generate the following array from my database:

Code:

- [0]
   -[client_details]
          -name
          -adress
          -etc
   -[client_sites]
          -[0]
               -name
               -url
               -ftp
               -etc
          -[1]
               -name
               -url
               -ftp
               -etc
          -[2]
               -name
               -url
               -ftp
               -etc
   -[client_contacts]
          -[0]
               -firstname
               -lastname
               -address
               -phone
          -[1]
               -firstname
               -lastname
               -address
               -phone
- [1]
   -[client_details]
          -name
          -adress
          -etc
   -[client_sites]
          -[0]
               -name
               -url
               -ftp
               -etc
          -[1]
               -name
               -url
               -ftp
               -etc
   -[client_contacts]
          -[0]
               -firstname
               -lastname
               -address
               -phone
          -[1]
               -firstname
               -lastname
               -address
               -phone
          -[2]
               -firstname
               -lastname
               -address
               -phone

Any idea if there is some kind of libary or function for this available in codeigniter?

Thanks in advance

[eluser]hvalente13[/eluser]
Hi PvOostrom,

I guess that the info you want to retrieve from db is in different table such as

client_details
client_sites
client_contacts

You can do a multidimentional array by doing a query with joins like this:

Code:

$this->db->select('client_details.*,client_sites.*,client_contacts.*')
$this->db->from('client_details');
$this->db->join('client_sites','client_sites.clientid = client_details.client_details','left');
$this->db->join('client_contacts','client_contacts.clientid = client_details.client_details','left');
$query = $this->db->get();

Hope this helps,
Good luck

[eluser]PvOostrom[/eluser]
I tried that, but instead of making an array for each client, it makes an array for each row in the sites table, details table and contacts table. The array looks like this:

Code:

array(
       [0] => array(
                   -name
                   -adress
                   -clientsite1
                   -clientcontacts1
               )
       [1] => array(
                   -name
                   -adress
                   -clientsite2
                   -clientcontacts2
               )    
)

Where the clientsite and contacts gives every row in the very same array.

[eluser]PvOostrom[/eluser]
The system is like this,

A client has many sites and has many contacts (as in contact persons)

Now I want an query which gives an array for every client, and within that array, the sites and contacts of that client in a (sub) array.

[eluser]hvalente13[/eluser]
If that doesn't work you could do it more manually in your model:

Code:

function get_client_details($id){
  $query = //get the data from the client_details table
}

do the same for the other tables:

Code:

function get_client_sites($id){
  $query = //get the data from the client_sites table
}

function get_client_contacts($id){
  $query = //get the data from the client_contacts table
}

Then in your controller you build your array:

Code:

$id = $this->uri->segment(3); // this is the client id that's the same for all tables... assuming that the tables are built in this way

$$data['client_details'] = array('0' => $this->mymodel->get_client_details($id),
  'client_sites' => $this->mymodel->get_client_sites($id),
  'client_contacts' => $this->mymodel->get_client_contacts($id),
);

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

Then you'll have to work them as database results in your view:

Code:

foreach ($client_details[0]->result() as $row){}

and

Code:

foreach ($client_details['client_sites']->result() as $row){}

Of course you have to do some data validation like some ifs

Hope this helps

[eluser]Bogdan Tanase[/eluser]
As far as i know, mysql returns rows. So, even if you join the three tables, you'll only get back rows with multiple columns, corresponding to the joined tables.

So, maybe a way would be to create your array manually (like hvalente13 suggested).

Something like this:

//aprox code

Code:

//get client list in an array

$client_details_array=array(); //define the array to hold all the data

$client_list=$this->clients->GetList(); //define GetList in model clients (query DB for results)

foreach($client_list as $item)
{
  $client_details_array[]['name']=$item->name;
  $client_details_array[]['address']=$item->address;
  // ...
  
  //get client sites
  $client_details_array[]['client_sites']=$this->sites->GetListByClient($item->client_id);
  //get client contacts
  $client_details_array[]['client_contacts']=$this->contacts->GetListByClient($item->client_id);

}

in my example i assumed you have 3 models, one for each table.

The function GetList returns an array with the full content of clients table
The functions GetListByClient returns an array for tables sites and contacts only for the given client_id.

If you need more help with de models part i'll elaborate further.

[eluser]PvOostrom[/eluser]
will try this out instead.

might be trying group_concat as well.

[eluser]Colin Williams[/eluser]
The good thing about going with join is that you only do one query, which causes less load on the database server. All you really need to do after you get your join query result is loop through it and construct your array just like you have above. All the data is there for you to process, you just need to take the correct logic at it and set it up. Do this in your model and then pass it back to your controller. This means all the heavy lifting happens in the model, and the controller and views can always expect the nice multidimensional array.

[eluser]PvOostrom[/eluser]
problem is that I get everything in one row.The thing which makes it not possible to construct the array is because all the second entries of the other tables will start a new row.

[eluser]Colin Williams[/eluser]
Absolutely, PvOostrom, that is expected. It is this array of rows that you must loop through and build your multidimensional array. Let me try to construct a very simple one for you:

We query a user table and join a user_attribute table. Our result looks like this:

Code:

$result = array(
  object ('uid' => '1', 'email' => '[email protected]', 'name' => 'nickname', 'value' => 'Pablo'),
  object ('uid' => '1', 'email' => '[email protected]', 'name' => 'birthdate', 'value' => '01/01/1980'),
);

Let's get a multidimensional array where each item contains uid, email, and an array of attributes:

Code:

$users = array();
foreach ($result as $row)
{
  if (!isset($users[$row->uid]))
  {
    $users[$row->uid] = array();
  }
  $users[$row->uid]['uid'] = $row->uid;
  $users[$row->uid]['email'] = $row->email;
  if (!isset($users[$row->uid]['attribute'])
  {
    $users[$row->uid]['attribute'] = array();
  }
  $users[$row->uid]['attribute'][$row->name] = $row->value;
}

By always updating the $users array with $users[$row->uid], we essentially create and maintain an array grouped by uid.

The result:

Code:

$users = array(
'1' => array(
  'uid' => '1',
  'email' => '[email protected]',
  'attribute' = array('nickname' => 'Pablo', 'birthdate' => '01/01/1980'),
));