尝试从2个表codeigniter获取数据时出错
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
Undefined indexes and trying to get property non-object more or less means the same thing which is you are not getting proper data or the variables or indexes you are trying to get are not initialized or undefined and cause of this can be error in query or blank data return by query you are running.
i would like to request you to pull your query data like this
$check = $this->db->query("SELECT * FROM SOMETHING AND YOUR CONDITION AND STUFF HERE");
if($check->num_rows()>0){
$result = $check->result();
return $result;
}else{
return false; // or anything you want.
}
let say this query function is stored in model and you are calling your model like this
$someVariable = $this->model_name->function();
if($someVariable!==FALSE){
// handle
}else
{
// handle
}
in the end, not sure why, but i also counter problems with double quotes sometime, YES I KNOW.. variable inside double quotes work, I'm just saying sometime... at least it happens with me, so i would like to request last thing. try debugging your query like this, currently you have
"SELECT * FROM TABLE WHERE THIS = $THAT"
Change this to
"SELECT * FROM TABLE WHERE THIS = '".$THAT."'"
I hope it will work out for you!.
EDITED: (Sorry that i failed to show example from your own code) Your Model file
<?php
class Crudmodel extends CI_Model{
public function __construct(){
parent::__construct();
$this->load->database();
}
function selectStudys()
{
$query= $this->db->query("SELECT * FROM Study");
if($query->num_rows()>0){
$result = $query->result_array();
}else{
$result = "";
// or anything you can use as error handler
return $result;
}
}
function getName($name)
{
$query= $this->db->query("SELECT username FROM Users WHERE id = $name ");
if($query->num_rows()>0){
$result = $query->result_array();
}else{
$result = "";
// or anything you can use as error handler
return $result;
}
}
function getSubName($subject)
{
$query= $this->db->query("SELECT name FROM Subjects WHERE id = $subject ");
if($query->num_rows()>0){
$result = $query->result_array();
}else{
$result = "";
// or anything you can use as error handler
return $result;
}
}
function CombineResults($subject, $name){
// you can also use this
$query = $this->db->query("SELECT sub.name, user.username FROM Subjects sub, Users user WHERE sub.id=$subject AND user.id = $name");
if($query->num_rows()>0){
return $query->result();
}else{
return "";
// or anything you can use as error handler
}
}
}
?>
Your controller file
public function index(){
# get all data in Study table
$selectStudys = $this->Crudmodel->selectStudys();
// we have condition on this model method/function we can validate
// response comming from this method and add handler just like we did
// for queries. your main problem can be this
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
if(!empty($user[0]['username'])){
$data[$key]['user_id'] = $user[0]['username'];
// your main problem can be this. may be it is not getting value from query this is why we have put validation on model function and error handler condition here
}else{
$data[$key]['user_id'] = ''; // or anything as your else condition you can use as error handler
}
if(!empty($subject[0]['name'])){
$data[$key]['subject_id'] = $subject[0]['name'];
// your main problem can be this. may be it is not getting value from query this is why we have put validation on model function and error handler condition here
}else{
$data[$key]["subject_id"] = "";
// or anything you can use as error handler
}
}
$data['records'] = $selectStudys;
$this->load->view('home', $data);
}