Error while trying to retrieve data from 2 codeigniter tables

I gotta fetch data from 2 tables.. My tables are "Study","Users" and "Subjects"

"Study" includes:(id, user_id[is the foreign key to the column "id" of the table "Users"], subject_id[is the foreign key to the column "id" of the table "Subjects"], grade, date)

"Users" includes:(id,username,name,lastname,password,type,status,date)

"Subjects" includes:(id, career_id, name, description, hours)

I wanna get something like this at the end:

I got this errors:

Here is my code: My view file ("home"):

    <html>

    <head>

    </head>

<body>

    <div class="container">
    <div class="row">
    <div class="col-md-12">

        <h2 align="center">TABLE:Study</h2>

        <input id="busqueda_tabla" type="text">
            <table class="table table-hover" align="center" border="1" cellspacing="0" cellpadding="0" width="700" id="tabla_busqueda">
                <thead>
                    <th>id</th>
                    <th>User</th>
                    <th>Subject</th>
                    <th>Grade</th>
                    <th>Date</th>
                    <th>Action</th>
                </thead>

<tbody>
    <?php

    if (count($records) > 0 && $records != false) {
        foreach($records as $record) {

            echo "<tr>
                      <td>".$record['id']."</td>
                      <td>".$record['user']."</td>
                      <td>".$record['subject']."</td>
                      <td>".$record['grade']."</td>
                      <td>".$record['date']."</td>
                      <td align='center'>

                         <button type='button' class='btn btn-primary'>EDITAR</button></a> |

                         <button type='button' class='btn btn-danger'>BORRAR</button></a>

                  </tr>";
        }

       }
    ?>

</tbody>

    </table>

        </div>
        </div>
        </div>

</body>
</html>

Here is my Controller file ("Home"):

    <?php

    class Home extends CI_Controller{

         public function __construct(){
             parent::__construct();

             $this->load->model("Crudmodel");

        }

        public function index(){

    # get all data in Study table
    $selectStudys = $this->Crudmodel->selectStudys();

    foreach ($selectStudys as $key => $study)
    {
        # get UserNames
        $user = $this->Crudmodel->getName($study['user_id']);

        #get Subject Names
        $subject = $this->Crudmodel->getSubName($study['subject_id']);

        #append both NEW VALUES to same array

        $data[$key]['user_id'] = $user[0]['username'];
        $data[$key]['subject_id'] = $subject[0]['name'];

    }

    $data['records'] = $selectStudys;
    $this->load->view('home', $data);

}

   }

?>

And my Model file ("Crudmodel"):

  <?php

    class Crudmodel extends CI_Model{

        public function __construct(){
         parent::__construct();

         $this->load->database();

        }

        function selectStudys()
{
    $query= $this->db->query("SELECT * FROM Study");
    $result = $query->result_array();
    return $result;
}

function getName($name)
{
    $query= $this->db->query("SELECT username FROM Users WHERE id = $name ");
    $result = $query->result_array();
    return $result;
}

function getSubName($subject)
{
    $query= $this->db->query("SELECT name FROM Subjects WHERE id = $subject ");
    $result = $query->result_array();
    return $result;
}

}
?>

Hope you can help me :/

Iam changed your query to join query, Simply change your code to below

public function index(){

    # get all data in Study table

    $query = $this->db->query("SELECT sd.user_id as id,sd.grade as grade,sd.date as date,sd.subject_id as subject,ur.username as user FROM Study as sd,Users as ur,Subjects as sb WHERE ur.id=sd.user_id and sb.id=sd.subject_id");

    $result = $query->result_array();

    $data['records'] = $result;

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

}

and now run the code

Related Articles