Codeigniter SQL join

up vote
-1
down vote

favorite

I have two tables:

Table 1: dispatch_lists = id, date_of_dispatch, customer_id

Table 2: dispatch_lists_rates - id, dispatch_list_id, item_id, weight, rate

I am trying to get list of records from dispatch_lists_rates table that corresponds to dispatch_lists.date_of_dispatch >= $start_date and dispatch_lists.date_of_dispatch <= $end_date (Basically, I'm trying to get list of items sold during a particular month, along with their rates)

Here is my code:

$this->db->select('dispatch_lists_rates.*, dispatch_lists.customer_id, dispatch_lists.date_of_dispatch')

 ->from('dispatch_lists_rates')                 

 ->where('dispatch_lists.date_of_dispatch >= '.$start_date)             

 ->where('dispatch_lists.date_of_dispatch <= '.$end_date)           

 ->join('dispatch_lists', 'dispatch_lists_rates.dispatch_list_id = dispatch_lists.id');

This doesn't seem to work!
Any help would be greatly appreciated.
Thanks in advance for your time!

[edit] I just realized I had the code quite wrong:

    ->where('dispatch_lists.date_of_dispatch >= '.$start_date)  

should have been  

     ->where('dispatch_lists.date_of_dispatch >= ', $start_date)

Thank you for trying to help!

Much Appreciated!

edited Jan 22 at 9:47

Prabhu

7361720

asked Jan 20 at 2:23

user3592813

1

Glad that your problem got solved. But the syntax you have used doesn't seem to be correct. It is misguiding the community users. Just by correcting the question will not help. Please accept one of the below answers or answer the question.
– Prabhu
Jan 22 at 8:43

Unfortunately, both the answers seem to be wrong. Marking either as correct would possibly misguide others.
– user3592813
Feb 2 at 14:30

Then prove it by answering your own question and accept it to help others. Editing question isn't helping others.
– Prabhu
Feb 2 at 14:32

add a comment |

up vote
-1
down vote

favorite

I have two tables:

Table 1: dispatch_lists = id, date_of_dispatch, customer_id

Table 2: dispatch_lists_rates - id, dispatch_list_id, item_id, weight, rate

Here is my code:

$this->db->select('dispatch_lists_rates.*, dispatch_lists.customer_id, dispatch_lists.date_of_dispatch')

 ->from('dispatch_lists_rates')                 

 ->where('dispatch_lists.date_of_dispatch >= '.$start_date)             

 ->where('dispatch_lists.date_of_dispatch <= '.$end_date)           

 ->join('dispatch_lists', 'dispatch_lists_rates.dispatch_list_id = dispatch_lists.id');

This doesn't seem to work!
Any help would be greatly appreciated.
Thanks in advance for your time!

[edit] I just realized I had the code quite wrong:

    ->where('dispatch_lists.date_of_dispatch >= '.$start_date)  

should have been  

     ->where('dispatch_lists.date_of_dispatch >= ', $start_date)

Thank you for trying to help!

Much Appreciated!

edited Jan 22 at 9:47

Prabhu

7361720

asked Jan 20 at 2:23

user3592813

1

Glad that your problem got solved. But the syntax you have used doesn't seem to be correct. It is misguiding the community users. Just by correcting the question will not help. Please accept one of the below answers or answer the question.
– Prabhu
Jan 22 at 8:43

Unfortunately, both the answers seem to be wrong. Marking either as correct would possibly misguide others.
– user3592813
Feb 2 at 14:30

Then prove it by answering your own question and accept it to help others. Editing question isn't helping others.
– Prabhu
Feb 2 at 14:32

add a comment |

up vote
-1
down vote

favorite

I have two tables:

Table 1: dispatch_lists = id, date_of_dispatch, customer_id

Table 2: dispatch_lists_rates - id, dispatch_list_id, item_id, weight, rate

Here is my code:

$this->db->select('dispatch_lists_rates.*, dispatch_lists.customer_id, dispatch_lists.date_of_dispatch')

 ->from('dispatch_lists_rates')                 

 ->where('dispatch_lists.date_of_dispatch >= '.$start_date)             

 ->where('dispatch_lists.date_of_dispatch <= '.$end_date)           

 ->join('dispatch_lists', 'dispatch_lists_rates.dispatch_list_id = dispatch_lists.id');

This doesn't seem to work!
Any help would be greatly appreciated.
Thanks in advance for your time!

[edit] I just realized I had the code quite wrong:

    ->where('dispatch_lists.date_of_dispatch >= '.$start_date)  

should have been  

     ->where('dispatch_lists.date_of_dispatch >= ', $start_date)

Thank you for trying to help!

Much Appreciated!

edited Jan 22 at 9:47

Prabhu

7361720

asked Jan 20 at 2:23

user3592813

I have two tables:

Table 1: dispatch_lists = id, date_of_dispatch, customer_id

Table 2: dispatch_lists_rates - id, dispatch_list_id, item_id, weight, rate

Here is my code:

$this->db->select('dispatch_lists_rates.*, dispatch_lists.customer_id, dispatch_lists.date_of_dispatch')

 ->from('dispatch_lists_rates')                 

 ->where('dispatch_lists.date_of_dispatch >= '.$start_date)             

 ->where('dispatch_lists.date_of_dispatch <= '.$end_date)           

 ->join('dispatch_lists', 'dispatch_lists_rates.dispatch_list_id = dispatch_lists.id');

This doesn't seem to work!
Any help would be greatly appreciated.
Thanks in advance for your time!

[edit] I just realized I had the code quite wrong:

    ->where('dispatch_lists.date_of_dispatch >= '.$start_date)  

should have been  

     ->where('dispatch_lists.date_of_dispatch >= ', $start_date)

Thank you for trying to help!

Much Appreciated!

mysql codeigniter

edited Jan 22 at 9:47

Prabhu

7361720

asked Jan 20 at 2:23

user3592813

edited Jan 22 at 9:47

Prabhu

7361720

asked Jan 20 at 2:23

user3592813

edited Jan 22 at 9:47

Prabhu

7361720

edited Jan 22 at 9:47

Prabhu

7361720

edited Jan 22 at 9:47

Prabhu

7361720

asked Jan 20 at 2:23

user3592813

asked Jan 20 at 2:23

user3592813

asked Jan 20 at 2:23

user3592813

1

Glad that your problem got solved. But the syntax you have used doesn't seem to be correct. It is misguiding the community users. Just by correcting the question will not help. Please accept one of the below answers or answer the question.
– Prabhu
Jan 22 at 8:43

Unfortunately, both the answers seem to be wrong. Marking either as correct would possibly misguide others.
– user3592813
Feb 2 at 14:30

Then prove it by answering your own question and accept it to help others. Editing question isn't helping others.
– Prabhu
Feb 2 at 14:32

add a comment |

1

Glad that your problem got solved. But the syntax you have used doesn't seem to be correct. It is misguiding the community users. Just by correcting the question will not help. Please accept one of the below answers or answer the question.
– Prabhu
Jan 22 at 8:43

Unfortunately, both the answers seem to be wrong. Marking either as correct would possibly misguide others.
– user3592813
Feb 2 at 14:30

Then prove it by answering your own question and accept it to help others. Editing question isn't helping others.
– Prabhu
Feb 2 at 14:32

Glad that your problem got solved. But the syntax you have used doesn't seem to be correct. It is misguiding the community users. Just by correcting the question will not help. Please accept one of the below answers or answer the question.
– Prabhu
Jan 22 at 8:43

Unfortunately, both the answers seem to be wrong. Marking either as correct would possibly misguide others.
– user3592813
Feb 2 at 14:30

Then prove it by answering your own question and accept it to help others. Editing question isn't helping others.
– Prabhu
Feb 2 at 14:32

add a comment |

3 Answers
3

active

oldest

votes

up vote
0
down vote

Try it code:

$this->db->select('dispatch_lists_rates.*, dispatch_lists.customer_id, 

                    dispatch_lists.date_of_dispatch');



$this->db->from('dispatch_lists_rates');



$this->db->join('dispatch_lists', 'dispatch_lists_rates.dispatch_list_id = 

                dispatch_lists.id');



$this->db->where(array('dispatch_lists.date_of_dispatch' >= $start_date, 

                        'dispatch_lists.date_of_dispatch' <= $end_date));



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

answered Jan 20 at 5:23

Matiur Rahman Mozumdar

11116

add a comment |

up vote
0
down vote

accepted

->where('dispatch_lists.date_of_dispatch >= '.$start_date)

should be

 ->where('dispatch_lists.date_of_dispatch >= ', $start_date)

answered Feb 3 at 14:13

user3592813

add a comment |

up vote
0
down vote

You have to add where condition after joins

Please refer below code

$result = $this->db->select('dispatch_lists_rates.*, dispatch_lists.customer_id, dispatch_lists.date_of_dispatch')

 ->from('dispatch_lists_rates')

 ->join('dispatch_lists', 'dispatch_lists_rates.dispatch_list_id = dispatch_lists.id');       

 ->where('dispatch_lists.date_of_dispatch >= ', $start_date)             

 ->where('dispatch_lists.date_of_dispatch <= ', $end_date)

 ->get();

Hope this can help you.

edited Nov 11 at 6:57

answered Jan 20 at 4:34

Prabhu

7361720

If my answer helped you, please accept my answer to support the community.
– Prabhu
Jan 20 at 8:55

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f48352015%2fcodeigniter-sql-join%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

up vote
0
down vote

Try it code:

$this->db->select('dispatch_lists_rates.*, dispatch_lists.customer_id, 

                    dispatch_lists.date_of_dispatch');



$this->db->from('dispatch_lists_rates');



$this->db->join('dispatch_lists', 'dispatch_lists_rates.dispatch_list_id = 

                dispatch_lists.id');



$this->db->where(array('dispatch_lists.date_of_dispatch' >= $start_date, 

                        'dispatch_lists.date_of_dispatch' <= $end_date));



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

answered Jan 20 at 5:23

Matiur Rahman Mozumdar

11116

add a comment |

up vote
0
down vote

Try it code:

$this->db->select('dispatch_lists_rates.*, dispatch_lists.customer_id, 

                    dispatch_lists.date_of_dispatch');



$this->db->from('dispatch_lists_rates');



$this->db->join('dispatch_lists', 'dispatch_lists_rates.dispatch_list_id = 

                dispatch_lists.id');



$this->db->where(array('dispatch_lists.date_of_dispatch' >= $start_date, 

                        'dispatch_lists.date_of_dispatch' <= $end_date));



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

answered Jan 20 at 5:23

Matiur Rahman Mozumdar

11116

add a comment |

up vote
0
down vote

Try it code:

$this->db->select('dispatch_lists_rates.*, dispatch_lists.customer_id, 

                    dispatch_lists.date_of_dispatch');



$this->db->from('dispatch_lists_rates');



$this->db->join('dispatch_lists', 'dispatch_lists_rates.dispatch_list_id = 

                dispatch_lists.id');



$this->db->where(array('dispatch_lists.date_of_dispatch' >= $start_date, 

                        'dispatch_lists.date_of_dispatch' <= $end_date));



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

answered Jan 20 at 5:23

Matiur Rahman Mozumdar

11116

Try it code:

$this->db->select('dispatch_lists_rates.*, dispatch_lists.customer_id, 

                    dispatch_lists.date_of_dispatch');



$this->db->from('dispatch_lists_rates');



$this->db->join('dispatch_lists', 'dispatch_lists_rates.dispatch_list_id = 

                dispatch_lists.id');



$this->db->where(array('dispatch_lists.date_of_dispatch' >= $start_date, 

                        'dispatch_lists.date_of_dispatch' <= $end_date));



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

answered Jan 20 at 5:23

Matiur Rahman Mozumdar

11116

answered Jan 20 at 5:23

Matiur Rahman Mozumdar

11116

answered Jan 20 at 5:23

Matiur Rahman Mozumdar

11116

answered Jan 20 at 5:23

Matiur Rahman Mozumdar

11116

add a comment |

up vote
0
down vote

accepted

->where('dispatch_lists.date_of_dispatch >= '.$start_date)

should be

 ->where('dispatch_lists.date_of_dispatch >= ', $start_date)

answered Feb 3 at 14:13

user3592813

add a comment |

up vote
0
down vote

accepted

->where('dispatch_lists.date_of_dispatch >= '.$start_date)

should be

 ->where('dispatch_lists.date_of_dispatch >= ', $start_date)

answered Feb 3 at 14:13

user3592813

add a comment |

up vote
0
down vote

accepted

->where('dispatch_lists.date_of_dispatch >= '.$start_date)

should be

 ->where('dispatch_lists.date_of_dispatch >= ', $start_date)

answered Feb 3 at 14:13

user3592813

->where('dispatch_lists.date_of_dispatch >= '.$start_date)

should be

 ->where('dispatch_lists.date_of_dispatch >= ', $start_date)

answered Feb 3 at 14:13

user3592813

answered Feb 3 at 14:13

user3592813

answered Feb 3 at 14:13

user3592813

answered Feb 3 at 14:13

user3592813

add a comment |

up vote
0
down vote

You have to add where condition after joins

Please refer below code

$result = $this->db->select('dispatch_lists_rates.*, dispatch_lists.customer_id, dispatch_lists.date_of_dispatch')

 ->from('dispatch_lists_rates')

 ->join('dispatch_lists', 'dispatch_lists_rates.dispatch_list_id = dispatch_lists.id');       

 ->where('dispatch_lists.date_of_dispatch >= ', $start_date)             

 ->where('dispatch_lists.date_of_dispatch <= ', $end_date)

 ->get();

Hope this can help you.

edited Nov 11 at 6:57

answered Jan 20 at 4:34

Prabhu

7361720

If my answer helped you, please accept my answer to support the community.
– Prabhu
Jan 20 at 8:55

add a comment |

up vote
0
down vote

You have to add where condition after joins

Please refer below code

$result = $this->db->select('dispatch_lists_rates.*, dispatch_lists.customer_id, dispatch_lists.date_of_dispatch')

 ->from('dispatch_lists_rates')

 ->join('dispatch_lists', 'dispatch_lists_rates.dispatch_list_id = dispatch_lists.id');       

 ->where('dispatch_lists.date_of_dispatch >= ', $start_date)             

 ->where('dispatch_lists.date_of_dispatch <= ', $end_date)

 ->get();

Hope this can help you.

edited Nov 11 at 6:57

answered Jan 20 at 4:34

Prabhu

7361720

If my answer helped you, please accept my answer to support the community.
– Prabhu
Jan 20 at 8:55

add a comment |

up vote
0
down vote

You have to add where condition after joins

Please refer below code

$result = $this->db->select('dispatch_lists_rates.*, dispatch_lists.customer_id, dispatch_lists.date_of_dispatch')

 ->from('dispatch_lists_rates')

 ->join('dispatch_lists', 'dispatch_lists_rates.dispatch_list_id = dispatch_lists.id');       

 ->where('dispatch_lists.date_of_dispatch >= ', $start_date)             

 ->where('dispatch_lists.date_of_dispatch <= ', $end_date)

 ->get();

Hope this can help you.

edited Nov 11 at 6:57

answered Jan 20 at 4:34

Prabhu

7361720

You have to add where condition after joins

Please refer below code

$result = $this->db->select('dispatch_lists_rates.*, dispatch_lists.customer_id, dispatch_lists.date_of_dispatch')

 ->from('dispatch_lists_rates')

 ->join('dispatch_lists', 'dispatch_lists_rates.dispatch_list_id = dispatch_lists.id');       

 ->where('dispatch_lists.date_of_dispatch >= ', $start_date)             

 ->where('dispatch_lists.date_of_dispatch <= ', $end_date)

 ->get();

Hope this can help you.

edited Nov 11 at 6:57

answered Jan 20 at 4:34

Prabhu

7361720

edited Nov 11 at 6:57

answered Jan 20 at 4:34

Prabhu

7361720

answered Jan 20 at 4:34

Prabhu

7361720

answered Jan 20 at 4:34

Prabhu

7361720

If my answer helped you, please accept my answer to support the community.
– Prabhu
Jan 20 at 8:55

add a comment |

If my answer helped you, please accept my answer to support the community.
– Prabhu
Jan 20 at 8:55

If my answer helped you, please accept my answer to support the community.
– Prabhu
Jan 20 at 8:55

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ndtyjky