CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Aslam Kakkove
Aslam Kakkove

Reputation: 97

SQL selecting from two tables returns duplicate values - Codeigniter

I have to retrieve some all data from two tables. There is no condition. But my code returns duplicated values of data. each table contain 4 rows

Centres tbl:

centres_tbl

Training Course tbl:

tbl_training_course

Output:

View output Screenshot

My Controller code:

    $this->load->model("admindata"); 
    $data ['query'] = $this->admindata->getcentrelist(); 

    $this->load->helper('url');
    $this->load->view('admin/header');
    $this->load->view('admin/training',$data);
    $this->load->view('admin/footer');

My query on Model is: 

public function getcentrelist()
{
    $this->load->database();
    $query=  $this->db->query('SELECT centre_name,course_name from tbl_training_courses, tbl_traning_centres'); 
    return $query->result();
}

View: (training.php)

<?php foreach($query as $row): ?>
        <tr>
            <td><?php echo $row->centre_name; ?></td>             
        </tr>
       <?php endforeach; ?>

Upvotes: 4

Views: 1422

Answers (3)

walid zaidouh
walid zaidouh

Reputation: 111

The data returns by your query is normal because you select One line and you fetch all lines of the second table so if you have 10 lines in the second line you will get each value 10 times and if you even put distinct you will get a problem. To avoid such a problem you need to use condition or Join between two tables.

Upvotes: 0

Yazid Erman
Yazid Erman

Reputation: 1186

This is normal because of the implicit join you made! Why don't you simply request two queries, one per table, and then merge the results in one array?

public function getcentrelist()
{
    $this->load->database();
    $query_courses=  $this->db->query('SELECT course_name as name from tbl_training_courses');  
    $query_centers=  $this->db->query('SELECT centre_name as name from tbl_traning_centres'); 
    $courses = $query_courses->result();
    $centers = $query_centers->result();
    return array_merge($courses,$centers);
}

Note please that I have renamed the fields course_name and center name to a unified name: "name", this is necessary to have a coherent merged table.

Upvotes: 2

Abdulla Nilam
Abdulla Nilam

Reputation: 38584

Use DISTINCT

public function getcentrelist()
{
    $this->load->database();
    $query=  $this->db->query('SELECT DISTINCT centre_name,course_name from tbl_training_courses, tbl_traning_centres GROUP BY center_name'); 
    return $query->result();
}

or use Group By

public function getcentrelist()
{
    $this->load->database();
    $query=  $this->db->query('SELECT centre_name,course_name from tbl_training_courses, tbl_traning_centres GROUP BY center_name'); 
    return $query->result();
}

Upvotes: 1

Related Questions