subqueries

[eluser]ravindral[/eluser]
Hello all,
I want to execute queries based on result of first query. Can someone please provide example?
I have registered users table. It has country_id in it and subject_id column. subject_id column has comma separated values. In controller i execute query on users table. But for each user i want to display subject names when i am editing the profile. Can you please help me with example? I know this is not a coaching class but to promote codeigniter its necessary to help others.


Thanks,
Ravi.

[eluser]osfan[/eluser]
Hi Ravi,

It is usually better to structure your data in normalised tables within your database, in this case you'd end up with 3 tables; users, subjects, and a linking table user_subjects which would contain user_subjects_id, user_id, subject_id only. There could be many rows with the same user_id, as 1 user could have many subjects. You'd then use a SQL JOIN to retrieve your data in 1 statement. I will leave this as an exercise for you.

If you do end up with csv values in a column, 1 way to approach it is to write code similar to this (off the top of my head).

Code:

// Load your models
$this->load->model('users_model','',TRUE);
$this->load->model('subjects_model','',TRUE);

// Call users_model function to retrieve users
// E.g. SELECT user_id, country_id, subject_id FROM users
$user_entries = $this->users_model->get_users();
// If we have user entries
if ($user_entries) {
    // Loop through the user entries
    foreach ($user_entries as $user_row) {
        $user_id = $user_row->user_id
        $country_id = $user_row->country_id;
        $subject_id_column = $user_row->subject_id;
        
        $related_subjects = explode(",",$subject_id_column);
        // If there is at least 1 entry
        if ($related_subjects[0]) {
            // Loop through the subject entries
            foreach ($related_subjects as $subject_id) {
                // Call subjects model to retrieve individual subjects
                // E.g. SELECT subject_id, subject_name, subject_title FROM subjects WHERE subject_id = ?
                $subject_entries = $this->subjects_model->get_subject($subject_id);
                // If you've got subject entries for the subject id in question
                if ($subject_entries) {
                    // Loop through the subject data
                    foreach ($subject_entries as $subject_row) {
                        $subject_id = $subject_row->subject_id;
                        $subject_name = $subject_row->name;
                        $subject_title = $subject_row->title;
                    }
                }
            }
        }
    }
}

Hope this helps your thinking.

[eluser]ravindral[/eluser]
Great work osfan. Thats what i wanted. One more problem..how to return these combined values of user and subject for display to view?



Thank you very much,
Ravi.

[eluser]osfan[/eluser]
Hi Ravi,

Again there are many ways to do this, ... you could just add all the data you want to a string like $combined_data below, then dump it out.

Code:

// Load your models
$this->load->model('users_model','',TRUE);
$this->load->model('subjects_model','',TRUE);

// Call users_model function to retrieve users
// E.g. SELECT user_id, country_id, subject_id FROM users
$user_entries = $this->users_model->get_users();
// If we have user entries
if ($user_entries) {
    // Loop through the user entries
    foreach ($user_entries as $user_row) {
        
        $combined_data = '';

        $user_id = $user_row->user_id
        $country_id = $user_row->country_id;
        $subject_id_column = $user_row->subject_id;

        // Concatenate the user_id to your combined data string
        $combined_data .= 'User Id: ' . $user_id . '<br />';
        
        $related_subjects = explode(",",$subject_id_column);
        // If there is at least 1 entry
        if ($related_subjects[0]) {

            // As there are subjects add a header
            $combined_data .= 'Subjects: ';

            // Loop through the subject entries
            foreach ($related_subjects as $subject_id) {
                // Call subjects model to retrieve individual subjects
                // E.g. SELECT subject_id, subject_name, subject_title FROM subjects WHERE subject_id = ?
                $subject_entries = $this->subjects_model->get_subject($subject_id);
                // If you've got subject entries for the subject id in question
                if ($subject_entries) {
                    // Loop through the subject data
                    foreach ($subject_entries as $subject_row) {
                        $subject_id = $subject_row->subject_id;
                        $subject_name = $subject_row->name;
                        $subject_title = $subject_row->title;

                        // Add the subject name to your combined_data string
                        $combined_data . = $subject_name . '<br />';

                    }
                }
            }
        }

        echo $combined_data;

    }
}