Drop-down with option group

[eluser]mdcode[/eluser]
Hopefully this one is going to be easier than the last but it's not quite working the same way as the normal drop-downs on my form.

Here's the issue: I am using the form helper to create a drop-down menu with results pulled from the database, however those results should be pulled from two tables, one for types, and one for subtypes.

Here's tha table schema:

Code:

+-----------+-------------+
| Field     | Type        |
+-----------+-------------+
| ssid      | int(3)      |
| type      | varchar(50) |
+-----------+-------------+

+-----------+-------------+
| Field     | Type        |
+-----------+-------------+
| subtypeid | int(3)      |
| ssid      | int(3)      |
| subType   | varchar(50) |
+-----------+-------------+

Now this query using straight SQL in phpmyadmin works:

Code:

SELECT a.ssid, a.substrate, b.subtypeid, b.ssid, b.subType FROM lip_sstype AS a, lip_sssubtype AS b WHERE a.ssid = b.ssid ORDER BY a.substrate, b.subType ASC

But I am trying to convert this to an AR query.

Here is my model:

Code:

function get_substrates($substrate = '')
            {
                $this->db->from('sstype');
                $this->db->join('sssubtype', 'sssubtype.ssid = sstype.ssid');
                
                $query = $this->db->get();
                $item = $query->row();
                
                $array['substrate'] = $item->substrate;
                $array['subtype'] = $item->subType;
                
                    foreach ($array as $key => $value)
                    {
                        if (empty($value))
                        {
                            unset($array[$key]);
                        }
                    }
                
                $string = implode(' ', $array);
                
                return $string;
            }

Here is my controller:

Code:

$substrates = $this->projects_model->get_substrates();
                
                    if ($substrates->num_rows() > 0)
                    {
                        foreach ($substrates->result() as $substrate)
                        {
                        $data['values']['substrate - subtype'][$substrate->ssid] = $substrate->substrate;
                        }
                    }

And finally the error I am getting:

Code:

Call to a member function num_rows() on a non-object

I'm guessing there is something wrong in the query in my model, but however I have been trying to formulate the query (I've tried too many to remember or list), it fails with errors like the above, or "Unknown table" when the table is there and checked to being there 3 times, or "Ambiguous column 'ssid'" but I think I get that one.

Anywho, this is the result that I would like to see when looking at the source code of the page:

Code:

<select>
<optgroup>Type 1 from database</optgroup>
<option>Type 1 - Subtype 1 from database</option>
<option>Type 1 - Subtype 2 from database</option>
<optgroup>Type 2 from database</optgroup>
<option>Type 2 - Subtype 1 from database</option>
<option>Type 2 - Subtype 2 from database</option>
<option>Type 2 - Subtype 3 from database</option>
</select>

[eluser]bretticus[/eluser]
Please post your complete model. Is it named "projects_model" or is it "Projects_model" with the initial cap?

[eluser]mdcode[/eluser]
Apologies for the lack of reply, apart from the weekend, I have been off sick but that's by the by... Here is the complete model file named "projects_model.php":

Code:

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Projects_model extends Model {
    
    function __construct()
    {
        parent::Model();
    }
    
            function get_customers()
            {
                $query = $this->db->get('customers');
                return $query;
            }
            
            function get_customer_name($id = '')
            {
                $query = $this->db->get_where('customers', array('customerid' => $id));
                if ($query->num_rows() > 0)
                {
                    $row = $query->row();
                    return $row->customerName;
                }
            }
            
            function get_divisions()
            {
                $query = $this->db->get('divisions');
                return $query;
            }
            
            function get_division_name($id = '')
            {
                $query = $this->db->get_where('divisions', array('divisionid' => $id));
                if ($query->num_rows() > 0)
                {
                    $row = $query->row();
                    return $row->divisionName;
                }
            }
            
            function get_techs()
            {
                $query = $this->db->get('techs');
                return $query;
            }
            
            function get_tech_name($id = '')
            {
                $query = $this->db->get_where('techs', array('techid' => $id));
                if ($query->num_rows() > 0)
                {
                    $row = $query->row();
                    return $row->firstName;
                }
            }
            
            function get_reps()
            {
                $query = $this->db->get('reps');
                return $query;
            }
            
            function get_rep_name($id = '')
            {
                $query = $this->db->get_where('reps', array('repid' => $id));
                if ($query->num_rows() > 0)
                {
                    $row = $query->row();
                    return $row->firstName;
                }
            }
            
            function get_job_types()
            {
                $query = $this->db->get('jobtype');
                return $query;
            }
            
            function get_job_type_name($id = '')
            {
                $query = $this->db->get_where('jobtype', array('jobtypeid' => $id));
                if ($query->num_rows() > 0)
                {
                    $row = $query->row();
                    return $row->type;
                }
            }
            
            function get_substrates($ssid = '')
            {
                //$this->db->from('sstype');
                //$this->db->join('sssubtype', 'sssubtype.ssid = sstype.ssid');
                $this->db->select('(SELECT ss.*, st.* FROM sstype AS ss, sssubtype AS st WHERE ss.ssid = st.ssid) ORDER BY ss.substrate, st.subType', FALSE);
                
                $query = $this->db->get();
                $item = $query->row();
                
                $array['substrate'] = $item->substrate;
                $array['subtype'] = $item->subType;
                
                    foreach ($array as $key => $value)
                    {
                        if (empty($value))
                        {
                            unset($array[$key]);
                        }
                    }
                
                $string = implode(' ', $array);
                
                return $string;
            }
            
            function get_print_processes()
            {
                $query = $this->db->get('printprocess');
                return $query;
            }
            
            function get_print_process_name($id = '')
            {
                $query = $this->db->get_where('printprocess', array('ppid' => $id));
                if ($query->num_rows() > 0)
                {
                    $row = $query->row();
                    return $row->printProcess;
                }
            }
            
            function get_cooking_mediums()
            {
                $query = $this->db->get('cookingmedium');
                return $query;
            }
            
            function get_cooking_medium_name($id = '')
            {
                $query = $this->db->get_where('cookingmedium', array('mediumid' => $id));
                if ($query->num_rows() > 0)
                {
                    $row = $query->row();
                    return $row->mediumType;
                }
            }

}

/* End of file */
/* Location: models/projects_model.php */

[eluser]mdcode[/eluser]
Ok, I'm attempting to go back to basics on this so have come back to the base SQL query that works and is tested to be working in phpmyadmin:

Code:

$this->db->query("SELECT ss.*, st.* FROM sstype AS ss, sssubtype AS st WHERE ss.ssid = st.ssid ORDER BY ss.substrate, st.subType");
                
$query = $this->db->get();
$row = $query->row();
return $row->subType;

This, I think in theory should return just the subTypes from the query. This I cannot test as when attempting to view the form, all I get is an error message which says:

Code:

A Database Error Occurred
Error Number: 1146
Table 'liproduction.sstype' doesn't exist
SELECT ss.*, st.* FROM sstype AS ss, sssubtype AS st WHERE ss.ssid = st.ssid ORDER BY ss.substrate, st.subType

So I thought, fine, the sstype table does exist, perhaps because it's just doing base SQL it's not including the table prefixes that are set correctly in the config, and so I add on the "lip_" prefixes to the table names. Then all I get is this:

Code:

A Database Error Occurred
Error Number: 1096
No tables used
SELECT *

I could really use some help here, I'm not feeling too good and this is not making things any better for me. Thanks.

[eluser]bretticus[/eluser]
You're returning a string from get_substrates(). You can't call a method ( num_rows() ) on a non-object (a string.)

[eluser]mdcode[/eluser]
But the string is just a variable name to display the result, i.e.

Code:

$string = 'Paper - Thermal';

[eluser]bretticus[/eluser]
I haven't looked at your code in depth but if I were you I'd make two calls (one to each table) and then return each as an array. Use CI's database class to call result_array(). Then use the native php function array_combine to build a new associative array. Return that and send it to your view, It'll be ready made for using the form helper function: form_dropdown(). Now, if you need opt groups and such, I suppose you'll have to build the select out yourself.

[eluser]bretticus[/eluser]
[quote author="mdcode" date="1237875445"]But the string is just a variable name to display the result, i.e.

Code:

$string = 'Paper - Thermal';

[/quote]

Are you still using this code:

Code:

$substrates = $this->projects_model->get_substrates();
                
                    if ($substrates->num_rows() > 0)
                    {
                        foreach ($substrates->result() as $substrate)
                        {
                        $data['values']['substrate - subtype'][$substrate->ssid] = $substrate->substrate;
                        }
                    }

If get_substrates() returns a string, you most definitely cannot call $substrates->num_rows().

[eluser]mdcode[/eluser]
In the controller, yes I am, but with the errors that I am recieving right now it won't make a difference what's in the controller as it's not even getting that far, since it apparently can't run a perfectly valid query in the model.

[eluser]mdcode[/eluser]
[quote author="bretticus" date="1237875570"]I haven't looked at your code in depth but if I were you I'd make two calls (one to each table) and then return each as an array. Use CI's database class to call result_array(). Then use the native php function array_combine to build a new associative array. Return that and send it to your view, It'll be ready made for using the form helper function: form_dropdown(). Now, if you need opt groups and such, I suppose you'll have to build the select out yourself.[/quote]
I'll have to look into this and try it out, and by this I take it by your experience that this cannot be done in the one or two functions as all the others queries are? Optgroups aren't really essential, but it would make things easier when viewing the drop-down and I'm wondering if this is a let down of the CI form helper function...?