Create multidimensional array from two database tables for form_dropdown()

[eluser]suntrop[/eluser]
I need a multidimensional array for a nested select field with form_dropdown().

I've got two tables, the first (customers) are for the optgroups. The seccond table holds all projects.
Currently my code looks like this …

Code:

$this->db->select('
   c.id AS customer_id,
   c.name AS customer_name,
   p.id AS project_id,
   p.title AS project_title
');

$this->db->from('customers AS c');
$this->db->join('projects AS p', 'p.customer_id = c.id', 'inner');
#$this->db->group_by("customer_id");
$this->db->order_by("c.name");
$query = $this->db->get();
        
if ($query->num_rows() > 0)
{
   return $query->result_array();
}
        
return FALSE;

But that code produces just a normal array. How can I turn it into a multi array to user with form_dropdown()?

[eluser]cpass78[/eluser]
Might need two queries, try something like this, might not be functional, basically creating a new array( $rows ) containing arrays of the results:

Code:

$this->db->select( 'c.id AS customer_id, c.name AS customer_name' );

$this->db->from( 'customers AS c' );
$this->db->join( 'projects AS p', 'p.customer_id = c.id', 'inner' );
$this->db->order_by( "c.name" );
$query_c = $this->db->get();
        
if ( $query_c->num_rows() > 0 )
{
   $rows[] = $query_c->result_array();
}

$this->db->select( 'p.id AS project_id, p.title AS project_title' );
$this->db->from( 'customers AS c');
$this->db->join( 'projects AS p', 'p.customer_id = c.id', 'inner' );
$this->db->order_by( "c.name" );
$query_p = $this->db->get();
        
if ( $query_p->num_rows() > 0 )
{
   $rows[] = $query_p->result_array();
}

return $rows;

[eluser]suntrop[/eluser]
Thanks for your help. I don't know if I made something wrong, but it doesn't work as expected. I get an array like this …

Code:

Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [customer_id] => 2
                    [customer_name] => Client A
                )

            [1] => Array
                (
                    [customer_id] => 1
                    [customer_name] => Client B
                )

            [2] => Array
                (
                    [customer_id] => 1
                    [customer_name] => Client B
                )

            [3] => Array
                (
                    [customer_id] => 3
                    [customer_name] => Client C
                )

        )

    [1] => Array
        (
            [0] => Array
                (
                    [project_id] => 2
                    [project_title] => Project 2
                )

            [1] => Array
                (
                    [project_id] => 1
                    [project_title] => Project 1
                )

            [2] => Array
                (
                    [project_id] => 4
                    [project_title] => Project 4
                )

            [3] => Array
                (
                    [project_id] => 3
                    [project_title] => Project 3
                )

        )

)

What I need for from_dropdown() is …

Code:

Array
(
    [Client_A] => Array
        (
            [p_id_1] => Project 1
            [p_id_2] => Project 2
            [p_id_3] => Project 3
        )

    [Client_B] => Array
        (
            [p_id_4] => Project 1
            [p_id_6] => Project 2
            [p_id_8] => Project 3
        )

    [Client_C] => Array
        (
            [p_id_5] => Project 1
            [p_id_7] => Project 2
            [p_id_9] => Project 3
        )

)

[eluser]cpass78[/eluser]
Oh i see, well if the result you poseted was from the code I gave you, it worked perfectly, just formatted wrong. Youll need to build you own array in this case. *Thinks something like this will work.

Not done yet have to think about this a bit, give me a min

Code:

$this->db->select( 'c.id AS customer_id, c.name AS customer_name' );
$this->db->from( 'customers AS c' );
$this->db->join( 'projects AS p', 'p.customer_id = c.id', 'inner' );
$this->db->order_by( "c.name" );
$query_c = $this->db->get();
        
if ( $query_c->num_rows() > 0 )
{
   $row['customer'] = $query_c->result_array();
}

$this->db->select( 'p.id AS project_id, p.title AS project_title' );
$this->db->from( 'customers AS c');
$this->db->join( 'projects AS p', 'p.customer_id = c.id', 'inner' );
$this->db->order_by( "c.name" );
$query_p = $this->db->get();
        
if ( $query_p->num_rows() > 0 )
{
   $row['project'] = $query_p->result_array();
}

return $row;

[eluser]suntrop[/eluser]
I think I get it. With a Little Help from My Friends :-) Thanks cpass78

Code:

$this->db->select( 'p.id AS project_id, p.title AS project_title, c.name AS customer_name' );
    $this->db->from( 'projects AS p' );
    $this->db->join( 'customers AS c', 'p.customer_id = c.id', 'left' );
    $this->db->order_by( "c.name" );
    $query = $this->db->get();

    if ( $query->num_rows() > 0 )
    {
       $projects = $query->result_array();
    }
    
    foreach ($projects as $row) {
        $p[$row['customer_name']][$row['project_id']] = $row['project_title'];
    }

    
    echo form_dropdown('projects_array', $p);

The $p array looks a bit ugly, but it works.

It produces …

Code:

<select name="projects">
<optgroup label="Customer A">
<option value="2">Project Name</option>
</optgroup>
<optgroup label="Customer B">
<option value="1">Project Name 2</option>
<option value="4">Project Name 3</option>
</optgroup>
<optgroup label="Customer C">
<option value="3">Project Name 4</option>
</optgroup>
</select>

[eluser]cpass78[/eluser]
No sweat, yeah arrays can get a bit messy like that, I wonder if returning $this->db->result() would have been a bit cleaner since its an object..

Anyhow glad its working.

[eluser]suntrop[/eluser]
Thanks for the hint. I'll change it to an object.