0

Result I get opd_employeesduty_asign result without where condition This is my code in model

function load_roster_by_date()
{
    $date = $this->input->post('date');
    $this->db->select('*');
    $this->db->from('duty_assign');
    $this->db->where('date',$date);
    $this->db->join('opd_employees','(opd_employees.badgenumber = duty_assign.emp_id)','right');

    $query = $this->db->get();

    return $query->result_array();
}

The code perfectly works without adding $this->db->where('date',$date);. (Which means the output is the expected right joined table without a where condition). But when I add the where condition, the result is similar to the regular join, not as the right join. No errors in the console. Any help to solve this, please?

All I want is whether a duty added or not all the employee names should be displayed in the table

TDJ
  • 74
  • 2
  • 8
  • is it working without join? – M.Hemant Jan 21 '20 at 03:29
  • Yes. Perfectly right join works. But when add the where condition, it gives a table similar to regular join result not the right joined result. – TDJ Jan 21 '20 at 03:35
  • then check you may have `date` field in both table, so use `opd_employees.date` – Devsi Odedra Jan 21 '20 at 04:35
  • Only the duty_assign table is having a date field. I tried duty_assign.date. But no luck :( – TDJ Jan 21 '20 at 04:58
  • @TDJ Please check with `date format` date format must be same as your database field and your post value – Abhishek Desai Jan 21 '20 at 05:32
  • Try `$date = $this->input->post('date'); $this->db->select('*'); $this->db->from('duty_assign'); $this->db->join('opd_employees','(opd_employees.badgenumber = duty_assign.emp_id)','right');$this->db->where('date',$date);` – Amanjot Kaur Jan 21 '20 at 05:59
  • 1
    Check your input `$date` is in the format of "yyyy-mm-dd" – Arun pandian M Jan 21 '20 at 06:05
  • @ Abhishek Desai, @Arun pandian M - I added the screen shots. Please go through. Seems no issue with date format. – TDJ Jan 21 '20 at 06:27
  • @ Amanjot Kaur - Tried but no luck :( – TDJ Jan 21 '20 at 06:27
  • @TDJ From your Screenshot the date format is "mm/dd/yyyy" but in your database the date format is "yyyy-mm-dd" change the date format and your query will work – Arun pandian M Jan 21 '20 at 06:52
  • You should change your POST date to format `Y-m-d` Mysql Supports this date format. – Amanjot Kaur Jan 21 '20 at 07:00
  • If the format is wrong I guess that single row result also will not be returned. Isn't it? – TDJ Jan 21 '20 at 07:11
  • @TDJ Try with `$date = date('Y-m-d',strtotime($this->input->post('date')));` – Abhishek Desai Jan 21 '20 at 07:14
  • $postDate = $this->input->post('date'); $convertDate = date("Y-m-d", strtotime($postDate)); @TDJ pls try this. – ashokan Jan 21 '20 at 07:17
  • tried date('Y-m-d',strtotime($this->input->post('date'))); just now and didn't work :( – TDJ Jan 21 '20 at 07:22
  • Please try with `$date = explode("/", $this->input->post('date')); $date = $date[2]."-".$date[0]."-".$date[1];` – Abhishek Desai Jan 21 '20 at 07:27
  • I think the join is not working perfectly, can you please comment it and try the query.@TDJ – ashokan Jan 21 '20 at 07:28
  • I just seen in your table the badgeumebr is `000000007` And in your join the table emp_id is `7` please check with data-type of badgenumber and looking into your record. @TDJ – Abhishek Desai Jan 21 '20 at 07:31
  • @Abhishek Desai if so, will that one row will not be returned. Isn't it? – TDJ Jan 21 '20 at 07:49
  • @shokan - Yes clearly problem is with the join. Actually right join is not working when i add a where condition – TDJ Jan 21 '20 at 07:52

3 Answers3

0

I think this will help you understand the difference between Joins. There must be no data in your database.

$this->db->get_compiled_select(); => It will help you see the query made be CI which you can execute directly on your server to see the results.

You can see this answer What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? to understand "Joins".

Amanjot Kaur
  • 2,028
  • 4
  • 18
  • 33
Atif
  • 64
  • 8
  • Data is available in the database and gives the correct result without using the where condition. But when I add the where condition, the right join act similar as regular join. – TDJ Jan 21 '20 at 05:01
  • $this->db->select('*'); $this->db->from('duty_assign'); => This will just fetch the record duty_assign $this->db->join('opd_employees','(opd_employees.badgenumber = duty_assign.emp_id)','right'); => Add this line will join two tables and fetch record from opd_employees. Now as the table is going to fetch the record from opd_employee. $this->db->where('date',$date); Adding this date condition will get record from opd_employee. => Even if it exists in "duty_assign". It will not fetch because you are trying to get records from "opd_employee". – Atif Jan 21 '20 at 05:07
  • Or you can show some sample data in your table. It will help me explain it to you better. – Atif Jan 21 '20 at 05:12
  • I shall have to take a look at the records. Or the same solution.I have mentioned above. try to put this line before $this->db->get() statement. and it will show you query like this. "SELECT * FROM duty_assign right join opt_......." and you can paste that query in PHPMYADMIN or SQL server etc to see what record is it fetching. – Atif Jan 21 '20 at 05:37
  • I added the picture. Please go through. All I want is whether a duty added or not all the employee names should be displayed in the table in a selected date – TDJ Jan 21 '20 at 05:54
  • I have seen it the issue is you need to store 000007 in emp_id. You may have set emp_id as integer. Just change the datatype to string. And it will store the emo_id as 000007 if you are sending the same. and the same code with same join should work. – Atif Jan 21 '20 at 06:05
  • I added the result without where condition. It gives the correct result without changing that id. Please go through it. – TDJ Jan 21 '20 at 06:22
  • I apologize for that. you shall either have to run two different queries. Because if you will put where condition it will get conditional record for sure. Get the record from DB without condition and handle in PHP. It is much better then executing two queries for no reason. – Atif Jan 21 '20 at 06:40
  • You want to put a condition of date and still want query to get records without date? It shall not work i guess. – Atif Jan 21 '20 at 06:41
  • Quary returns with date. Isn't it? I have added the date to the table – TDJ Jan 21 '20 at 07:00
0
$this->db->where('date',$date);

can you try to:

$this->db->where('opd_employees.date',$date);

or

$this->db->where('duty_assign.date',$date);

cause this db already select join

Danish Ali
  • 2,354
  • 3
  • 15
  • 26
Wandi Tiger
  • 31
  • 3
  • There is only one column called date in two tables. But I tried duty_assign.date. Still no luck :( – TDJ Jan 21 '20 at 05:15
  • Can you try to make sure the data output is appropriate, converting to string: ` $date = $this->input->post('date'); $date = str_replace('/','-',$date); $timestamp1 = strtotime($date); $date = date('Y-m-d', $timestamp1); ` – Wandi Tiger Jan 21 '20 at 08:48
  • I checked the date type. Both are same and no issue :( – TDJ Jan 21 '20 at 13:51
0

Please add where condition below the join.

function load_roster_by_date()
{
    $date = $this->input->post('date');
    $this->db->select('*');
    $this->db->from('duty_assign');
    $this->db->join('opd_employees','(opd_employees.badgenumber = duty_assign.emp_id)','right');
    $this->db->where('date',$date);

    $query = $this->db->get();

    return $query->result_array();
}
Mahesh Sanjay Tirmali
  • 1
  • 4