How to convert mysql query to codeigniter using query builder?
up vote
1
down vote
favorite
The following is my mysql query:
select * from db_posts LEFT JOIN db_followers ON db_posts_user_id = db_followers_following AND db_followers_user_id = 276
How can I convert it to codeigniter using query builder?
$this->db->where('db_posts_user_id', 'db_followers_following');
$this->db->where('db_followers_user_id', 276);
$this->db->order_by('db_posts.db_posts_id', 'DESC');
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id');
$query2 = $this->db->get('db_posts')->result_array();
I get the required result when I use mysql query. But I get a blank array when I use codeigniter queries. What is wrong in my CI Query?
php mysql codeigniter mysqli codeigniter-3
edited Nov 20 at 12:40
Kristjan Kica
2,0741826
asked Nov 20 at 11:20
compulsive coder
426
add a comment |
up vote
1
down vote
favorite
The following is my mysql query:
select * from db_posts LEFT JOIN db_followers ON db_posts_user_id = db_followers_following AND db_followers_user_id = 276
How can I convert it to codeigniter using query builder?
$this->db->where('db_posts_user_id', 'db_followers_following');
$this->db->where('db_followers_user_id', 276);
$this->db->order_by('db_posts.db_posts_id', 'DESC');
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id');
$query2 = $this->db->get('db_posts')->result_array();
I get the required result when I use mysql query. But I get a blank array when I use codeigniter queries. What is wrong in my CI Query?
php mysql codeigniter mysqli codeigniter-3
edited Nov 20 at 12:40
Kristjan Kica
2,0741826
asked Nov 20 at 11:20
compulsive coder
426
1
oops i forgot that. but still i get only 10 rows as result while i have total 400 rows in db_posts. is there any mistakes in my query?
– compulsive coder
Nov 20 at 11:25
2
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id', 'Left');
– Amit Sahu
Nov 20 at 11:26
1
also i guess that is not mandatory as i get same result when i omit that statement also!
– compulsive coder
Nov 20 at 11:27
2
there is something wrong with your $this->db->where('db_posts_user_id', 'db_followers_following'); $this->db->where('db_followers_user_id', 276); Please verify it
– Amit Sahu
Nov 20 at 11:28
3
First, Try your query without any conditions. And then use 1 condition and then second. It will tell you where is the actual problem.
– Danish Ali
Nov 20 at 11:33
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
The following is my mysql query:
select * from db_posts LEFT JOIN db_followers ON db_posts_user_id = db_followers_following AND db_followers_user_id = 276
How can I convert it to codeigniter using query builder?
$this->db->where('db_posts_user_id', 'db_followers_following');
$this->db->where('db_followers_user_id', 276);
$this->db->order_by('db_posts.db_posts_id', 'DESC');
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id');
$query2 = $this->db->get('db_posts')->result_array();
I get the required result when I use mysql query. But I get a blank array when I use codeigniter queries. What is wrong in my CI Query?
php mysql codeigniter mysqli codeigniter-3
edited Nov 20 at 12:40
Kristjan Kica
2,0741826
asked Nov 20 at 11:20
compulsive coder
426
The following is my mysql query:
select * from db_posts LEFT JOIN db_followers ON db_posts_user_id = db_followers_following AND db_followers_user_id = 276
How can I convert it to codeigniter using query builder?
$this->db->where('db_posts_user_id', 'db_followers_following');
$this->db->where('db_followers_user_id', 276);
$this->db->order_by('db_posts.db_posts_id', 'DESC');
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id');
$query2 = $this->db->get('db_posts')->result_array();
I get the required result when I use mysql query. But I get a blank array when I use codeigniter queries. What is wrong in my CI Query?
php mysql codeigniter mysqli codeigniter-3
php mysql codeigniter mysqli codeigniter-3
edited Nov 20 at 12:40
Kristjan Kica
2,0741826
asked Nov 20 at 11:20
compulsive coder
426
edited Nov 20 at 12:40
Kristjan Kica
2,0741826
asked Nov 20 at 11:20
compulsive coder
426
edited Nov 20 at 12:40
Kristjan Kica
2,0741826
edited Nov 20 at 12:40
Kristjan Kica
2,0741826
edited Nov 20 at 12:40
Kristjan Kica
2,0741826
2,0741826
asked Nov 20 at 11:20
compulsive coder
426
asked Nov 20 at 11:20
compulsive coder
426
asked Nov 20 at 11:20
compulsive coder
426
426
1
oops i forgot that. but still i get only 10 rows as result while i have total 400 rows in db_posts. is there any mistakes in my query?
– compulsive coder
Nov 20 at 11:25
2
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id', 'Left');
– Amit Sahu
Nov 20 at 11:26
1
also i guess that is not mandatory as i get same result when i omit that statement also!
– compulsive coder
Nov 20 at 11:27
2
there is something wrong with your $this->db->where('db_posts_user_id', 'db_followers_following'); $this->db->where('db_followers_user_id', 276); Please verify it
– Amit Sahu
Nov 20 at 11:28
3
First, Try your query without any conditions. And then use 1 condition and then second. It will tell you where is the actual problem.
– Danish Ali
Nov 20 at 11:33
add a comment |
1
oops i forgot that. but still i get only 10 rows as result while i have total 400 rows in db_posts. is there any mistakes in my query?
– compulsive coder
Nov 20 at 11:25
2
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id', 'Left');
– Amit Sahu
Nov 20 at 11:26
1
also i guess that is not mandatory as i get same result when i omit that statement also!
– compulsive coder
Nov 20 at 11:27
2
there is something wrong with your $this->db->where('db_posts_user_id', 'db_followers_following'); $this->db->where('db_followers_user_id', 276); Please verify it
– Amit Sahu
Nov 20 at 11:28
3
First, Try your query without any conditions. And then use 1 condition and then second. It will tell you where is the actual problem.
– Danish Ali
Nov 20 at 11:33
1
1
oops i forgot that. but still i get only 10 rows as result while i have total 400 rows in db_posts. is there any mistakes in my query?
– compulsive coder
Nov 20 at 11:25
oops i forgot that. but still i get only 10 rows as result while i have total 400 rows in db_posts. is there any mistakes in my query?
– compulsive coder
Nov 20 at 11:25
2
2
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id', 'Left');
– Amit Sahu
Nov 20 at 11:26
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id', 'Left');
– Amit Sahu
Nov 20 at 11:26
1
1
also i guess that is not mandatory as i get same result when i omit that statement also!
– compulsive coder
Nov 20 at 11:27
also i guess that is not mandatory as i get same result when i omit that statement also!
– compulsive coder
Nov 20 at 11:27
2
2
there is something wrong with your $this->db->where('db_posts_user_id', 'db_followers_following'); $this->db->where('db_followers_user_id', 276); Please verify it
– Amit Sahu
Nov 20 at 11:28
there is something wrong with your $this->db->where('db_posts_user_id', 'db_followers_following'); $this->db->where('db_followers_user_id', 276); Please verify it
– Amit Sahu
Nov 20 at 11:28
3
3
First, Try your query without any conditions. And then use 1 condition and then second. It will tell you where is the actual problem.
– Danish Ali
Nov 20 at 11:33
First, Try your query without any conditions. And then use 1 condition and then second. It will tell you where is the actual problem.
– Danish Ali
Nov 20 at 11:33
add a comment |
2 Answers
2
active
oldest
votes
up vote
3
down vote
accepted
why are you using those where clause? if you are using it as condition for your 'JOIN' process, try it like this:
$this->db->order_by('db_posts.db_posts_id', 'DESC');
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id and db_followers_user_id = 276', 'left');
$query2 = $this->db->get('db_posts')->result_array();
Points to Note
1)as previous comments said, you should 'Left Join' if you want to get all posts from 'db_posts'.
2) You can add Multiple conditions in ON Clause using and but within ''. all your conditions should be specified before second comma(,) which is before mentioning 'LEFT'.
Hope this will help. Lemme know if this help
answered Nov 20 at 11:32
Rishad
179110
1
Lemme try it :)
– compulsive coder
Nov 20 at 11:34
1
That worked man. Yous saved my hours. i never thought we could use and conditions in 'join' query builder. thanks man :) Accepting this answer
– compulsive coder
Nov 20 at 11:36
Yeah we can use 'AND' conditions. Happy to know it worked for you :)
– Rishad
Nov 20 at 11:39
add a comment |
up vote
3
down vote
Try adding the "Left" option to tell it what kind of join to do:
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id', 'Left');
Also I'm not sure you need
$this->db->where('db_posts_user_id', 'db_followers_following');
- this is already covered by your JOIN statement.
Source:
http://www.bsourcecode.com/codeigniter/codeigniter-join-query/#left-join and https://www.codeigniter.com/userguide3/database/query_builder.html
answered Nov 20 at 11:22
ADyson
22.5k112443
1
oo lemme try that
– compulsive coder
Nov 20 at 11:26
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53391899%2fhow-to-convert-mysql-query-to-codeigniter-using-query-builder%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
accepted
why are you using those where clause? if you are using it as condition for your 'JOIN' process, try it like this:
$this->db->order_by('db_posts.db_posts_id', 'DESC');
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id and db_followers_user_id = 276', 'left');
$query2 = $this->db->get('db_posts')->result_array();
Points to Note
1)as previous comments said, you should 'Left Join' if you want to get all posts from 'db_posts'.
2) You can add Multiple conditions in ON Clause using and but within ''. all your conditions should be specified before second comma(,) which is before mentioning 'LEFT'.
Hope this will help. Lemme know if this help
answered Nov 20 at 11:32
Rishad
179110
1
Lemme try it :)
– compulsive coder
Nov 20 at 11:34
1
That worked man. Yous saved my hours. i never thought we could use and conditions in 'join' query builder. thanks man :) Accepting this answer
– compulsive coder
Nov 20 at 11:36
Yeah we can use 'AND' conditions. Happy to know it worked for you :)
– Rishad
Nov 20 at 11:39
add a comment |
up vote
3
down vote
accepted
why are you using those where clause? if you are using it as condition for your 'JOIN' process, try it like this:
$this->db->order_by('db_posts.db_posts_id', 'DESC');
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id and db_followers_user_id = 276', 'left');
$query2 = $this->db->get('db_posts')->result_array();
Points to Note
1)as previous comments said, you should 'Left Join' if you want to get all posts from 'db_posts'.
2) You can add Multiple conditions in ON Clause using and but within ''. all your conditions should be specified before second comma(,) which is before mentioning 'LEFT'.
Hope this will help. Lemme know if this help
answered Nov 20 at 11:32
Rishad
179110
1
Lemme try it :)
– compulsive coder
Nov 20 at 11:34
1
That worked man. Yous saved my hours. i never thought we could use and conditions in 'join' query builder. thanks man :) Accepting this answer
– compulsive coder
Nov 20 at 11:36
Yeah we can use 'AND' conditions. Happy to know it worked for you :)
– Rishad
Nov 20 at 11:39
add a comment |
up vote
3
down vote
accepted
up vote
3
down vote
accepted
why are you using those where clause? if you are using it as condition for your 'JOIN' process, try it like this:
$this->db->order_by('db_posts.db_posts_id', 'DESC');
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id and db_followers_user_id = 276', 'left');
$query2 = $this->db->get('db_posts')->result_array();
Points to Note
1)as previous comments said, you should 'Left Join' if you want to get all posts from 'db_posts'.
2) You can add Multiple conditions in ON Clause using and but within ''. all your conditions should be specified before second comma(,) which is before mentioning 'LEFT'.
Hope this will help. Lemme know if this help
answered Nov 20 at 11:32
Rishad
179110
why are you using those where clause? if you are using it as condition for your 'JOIN' process, try it like this:
$this->db->order_by('db_posts.db_posts_id', 'DESC');
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id and db_followers_user_id = 276', 'left');
$query2 = $this->db->get('db_posts')->result_array();
Points to Note
1)as previous comments said, you should 'Left Join' if you want to get all posts from 'db_posts'.
2) You can add Multiple conditions in ON Clause using and but within ''. all your conditions should be specified before second comma(,) which is before mentioning 'LEFT'.
Hope this will help. Lemme know if this help
answered Nov 20 at 11:32
Rishad
179110
answered Nov 20 at 11:32
Rishad
179110
answered Nov 20 at 11:32
Rishad
179110
answered Nov 20 at 11:32
Rishad
179110
179110
1
Lemme try it :)
– compulsive coder
Nov 20 at 11:34
1
That worked man. Yous saved my hours. i never thought we could use and conditions in 'join' query builder. thanks man :) Accepting this answer
– compulsive coder
Nov 20 at 11:36
Yeah we can use 'AND' conditions. Happy to know it worked for you :)
– Rishad
Nov 20 at 11:39
add a comment |
1
Lemme try it :)
– compulsive coder
Nov 20 at 11:34
1
That worked man. Yous saved my hours. i never thought we could use and conditions in 'join' query builder. thanks man :) Accepting this answer
– compulsive coder
Nov 20 at 11:36
Yeah we can use 'AND' conditions. Happy to know it worked for you :)
– Rishad
Nov 20 at 11:39
1
1
Lemme try it :)
– compulsive coder
Nov 20 at 11:34
Lemme try it :)
– compulsive coder
Nov 20 at 11:34
1
1
That worked man. Yous saved my hours. i never thought we could use and conditions in 'join' query builder. thanks man :) Accepting this answer
– compulsive coder
Nov 20 at 11:36
That worked man. Yous saved my hours. i never thought we could use and conditions in 'join' query builder. thanks man :) Accepting this answer
– compulsive coder
Nov 20 at 11:36
Yeah we can use 'AND' conditions. Happy to know it worked for you :)
– Rishad
Nov 20 at 11:39
Yeah we can use 'AND' conditions. Happy to know it worked for you :)
– Rishad
Nov 20 at 11:39
add a comment |
up vote
3
down vote
Try adding the "Left" option to tell it what kind of join to do:
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id', 'Left');
Also I'm not sure you need
$this->db->where('db_posts_user_id', 'db_followers_following');
- this is already covered by your JOIN statement.
Source:
http://www.bsourcecode.com/codeigniter/codeigniter-join-query/#left-join and https://www.codeigniter.com/userguide3/database/query_builder.html
answered Nov 20 at 11:22
ADyson
22.5k112443
1
oo lemme try that
– compulsive coder
Nov 20 at 11:26
add a comment |
up vote
3
down vote
Try adding the "Left" option to tell it what kind of join to do:
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id', 'Left');
Also I'm not sure you need
$this->db->where('db_posts_user_id', 'db_followers_following');
- this is already covered by your JOIN statement.
Source:
http://www.bsourcecode.com/codeigniter/codeigniter-join-query/#left-join and https://www.codeigniter.com/userguide3/database/query_builder.html
answered Nov 20 at 11:22
ADyson
22.5k112443
1
oo lemme try that
– compulsive coder
Nov 20 at 11:26
add a comment |
up vote
3
down vote
up vote
3
down vote
Try adding the "Left" option to tell it what kind of join to do:
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id', 'Left');
Also I'm not sure you need
$this->db->where('db_posts_user_id', 'db_followers_following');
- this is already covered by your JOIN statement.
Source:
http://www.bsourcecode.com/codeigniter/codeigniter-join-query/#left-join and https://www.codeigniter.com/userguide3/database/query_builder.html
answered Nov 20 at 11:22
ADyson
22.5k112443
Try adding the "Left" option to tell it what kind of join to do:
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id', 'Left');
Also I'm not sure you need
$this->db->where('db_posts_user_id', 'db_followers_following');
- this is already covered by your JOIN statement.
Source:
http://www.bsourcecode.com/codeigniter/codeigniter-join-query/#left-join and https://www.codeigniter.com/userguide3/database/query_builder.html
answered Nov 20 at 11:22
ADyson
22.5k112443
answered Nov 20 at 11:22
ADyson
22.5k112443
answered Nov 20 at 11:22
ADyson
22.5k112443
answered Nov 20 at 11:22
ADyson
22.5k112443
22.5k112443
1
oo lemme try that
– compulsive coder
Nov 20 at 11:26
add a comment |
1
oo lemme try that
– compulsive coder
Nov 20 at 11:26
1
1
oo lemme try that
– compulsive coder
Nov 20 at 11:26
oo lemme try that
– compulsive coder
Nov 20 at 11:26
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53391899%2fhow-to-convert-mysql-query-to-codeigniter-using-query-builder%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
oops i forgot that. but still i get only 10 rows as result while i have total 400 rows in db_posts. is there any mistakes in my query?
– compulsive coder
Nov 20 at 11:25
2
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id', 'Left');
– Amit Sahu
Nov 20 at 11:26
1
also i guess that is not mandatory as i get same result when i omit that statement also!
– compulsive coder
Nov 20 at 11:27
2
there is something wrong with your $this->db->where('db_posts_user_id', 'db_followers_following'); $this->db->where('db_followers_user_id', 276); Please verify it
– Amit Sahu
Nov 20 at 11:28
3
First, Try your query without any conditions. And then use 1 condition and then second. It will tell you where is the actual problem.
– Danish Ali
Nov 20 at 11:33