How to use 'WHERE' in 'JOIN' sql query

Question

I am trying to fetch data from the request table, but I need only specific row of table. so in normal, I use where('hospital_id',$hospital_id) but I use join query for user's name & blood type, so when I try to put WHERE in join query, it shows:

"This page isn't working" HTTP ERROR 500.

I try to put WHERE in every other way like: after from, after join but the result was remained same.

Here is my model:

 $hospital_id =$this->session->userdata('hospital_id');

 $query=$this->db

 ->select('*, user.name as h_name, blood.btype as blood_type')
 ->where('hospital_id',$hospital_id)
 ->from('request')

->join('user', 'user.id= request.user_id')
->join('blood', 'blood.id= request.blood_id')


->get()->result();

Here is my controller:

   public function view_req()
   {
     if(!$this->session->userdata('hospital_id'))
       {
         return redirect('Login/loginview_load2');

       }
      else {
        $this->load->model('Partner_model');
       //$data['title']="partner profile";
       $data['all_blood']=$this->Partner_model->get_id_req();
       $this->load->view('view_request',$data);
      }
   }

here is my view:

 <?php if(count($all_blood>0))
  { $i=0;
  foreach ($all_blood as $user) {
  $i++;
  ?>
  <tr>
  <td><?php echo $i ?></td>
  <td><?php echo $user->user_id ?></td>
    <td><?php echo $user->h_name ?></td>
  <td><?php echo $user->blood_id?></td>
  <td><?php echo $user->blood_type?></td>

  <?php }
  }?>

Did you have a look at your webserver ogs if you have access to it, it should explain you error with more details — Gregoire Ducharme, Jan 04 '19 at 14:17
`->join('blood', 'blood.id= request.blood_id')` look like it's missing a `;` end the end.so iam vote closing this question to be a **simple typographical error** — Raymond Nijland, Jan 04 '19 at 14:22
@GregoireDucharme sorry I am new here, can you please tell what is 'ogs'? — Sagar Parikh, Jan 04 '19 at 14:23
if tow or all tables have `hospital_id ` - `->where('request.hospital_id',$hospital_id)` ? — splash58, Jan 04 '19 at 14:25
oh yes indeed never mind i didn't notice `->get()->result();` is also part of the method chain.. closevote removed again. — Raymond Nijland, Jan 04 '19 at 14:25
@RaymondNijland not its still not working & i don't think its ; missing,in other code its working fine without ; — Sagar Parikh, Jan 04 '19 at 14:25
Not sure if it's required in codeigniter but place the where function under the join functions? the where functions between select and from function looks a bit wierd and suspect. — Raymond Nijland, Jan 04 '19 at 14:29
@splash58 sorry I don't understand what are you trying to say — Sagar Parikh, Jan 04 '19 at 14:30
To get a proper error message try https://stackoverflow.com/questions/5127838/where-does-php-store-the-error-log-php5-apache-fastcgi-cpanel — Gregoire Ducharme, Jan 04 '19 at 14:31
@RaymondNijland I also try to put where after from & join query — Sagar Parikh, Jan 04 '19 at 14:31
@SagarParikhSGR if more than one tables in query have the same field, f.e. hospital_id, you should explicitly say table name in where clause — splash58, Jan 04 '19 at 14:35
Always nice debugging on webservers which are configured to show a [white screen of death](https://stackoverflow.com/questions/1475297/phps-white-screen-of-death) instead off displaying parsing errors/notices/warnings which you need while developing you need to solve that one first.. If you can't fix yourself it consider contacting your webhost or move to a other webhosting — Raymond Nijland, Jan 04 '19 at 14:36
if @splash58 suggestion did work made a new habit out if it to **always** use aliases or fully quantified table names on the columns in SQL.. Without knowing table structures, errors and a white screen of death this mistake is hard to spot you are lucky splash58 made a educated guess about it. — Raymond Nijland, Jan 04 '19 at 14:48

score 0 · Answer 1 · answered Jan 04 '19 at 14:25

0

This message error could be from other piece of code. Anyway, you can try to debug your query with

    ->get_compiled_select()

before ->get()

Further info https://www.codeigniter.com/userguide3/database/query_builder.html

answered Jan 04 '19 at 14:25

Omar Matijas

71
1
6

i put ->get_compiled_select() before get but page still isn't working – Sagar Parikh Jan 04 '19 at 14:34
It will not solve the problem. It will return the entire sentence that CI builds and you can take a look at it – Omar Matijas Jan 04 '19 at 14:41

Sagar Parikh · Accepted Answer · 2019-01-05T05:47:03.470

0

the page is not working because its has same name of the field more than 1 table, just change the name of field & its working perfectly. there are two tables blood & request, when you use join query it's working perfectly but problem occurred when you applied "where" condition. both table have same name field "hospital_id" so "where" condition is not working & shows that "Page isn't working". Please don't use same field name in different tables

edited Jan 05 '19 at 05:47

answered Jan 04 '19 at 14:43

Sagar Parikh

288
5
20

Please expand on this. If there is a specific line, variable, or string that is causing a problem, please identify it, and the reason it is causing unintended behavior. As your answer stands now, it doesn't provide much value. – kchason Jan 04 '19 at 15:44

score 0 · Answer 3 · answered Jan 05 '19 at 09:00

Have you tried placing the ->where('hospital_id',$hospital_id) after the ->join( ?

$this->db->select("*, user.name as h_name, blood.btype as blood_type");
$this->db->from('request');
$this->db->join('user', 'user.id= request.user_id');
$this->db->join('blood', 'blood.id= request.blood_id');
$this->db->where('hospital_id',$hospital_id);

Keep up the good work!

How to use 'WHERE' in 'JOIN' sql query

3 Answers3