How to fetch story_id and user_id from stories tables.

This is PHP Laravel, SQL LeftJoin query problem.
I have 2 tables named as, "stories" and "favorite_story". I want to return all values from "stories" table and matched values from "favorite_story" table based on "story_id", which is a common field in both tables.

Result in array form you can see "story_id", "user_id" result are empty, which should not be empty.
Fav_id is empty, which is OKAY because "favorite_story" table is empty.

I want "story_id" to be fetched from stories table and "user_id" to be fetched from "favorite_story" table.

This is my controller code:

public function fanfiction(){

    $user_id = Session('userid');

    $data['stories'] =  DB::table('stories')

    ->leftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id')

    ->Select('*')

    ->get();



    return view('fanfiction', $data);

}

This is the result of above code in an array form.

        [0] => stdClass Object

            (

                [story_id] => 

                [story_title] => Third Story

                [story_desc] => This is "Times" story summary.

                [img] => 2018-12-26-70pgFK87ZN.png

                [story_genre] => Fighting

                [story_character] => Aoba Y.

                [story_rating] => Adult Only

                [story_warning] => Drug Use

                [story_date] => December 26, 2018

                [series_id] => 9999999

                [user_id] => 

                [fav_id] => 

            )



        [1] => stdClass Object

            (

                [story_id] => 

                [story_title] => Second Story

                [story_desc] => This is summary of this new story.

                [img] => 2018-12-19-e6JnEpo3ky.jpg

                [story_genre] => Friendship

                [story_character] => Aijsai

                [story_rating] => G

                [story_warning] => Rape (Actual)

                [story_date] => December 19, 2018

                [series_id] => 9999999

                [user_id] => 

                [fav_id] => 

            )

Any help would be highly appreciated.

asked Jan 1 at 17:33

Waqqar khan

add a comment |

Result in array form you can see "story_id", "user_id" result are empty, which should not be empty.
Fav_id is empty, which is OKAY because "favorite_story" table is empty.

I want "story_id" to be fetched from stories table and "user_id" to be fetched from "favorite_story" table.

This is my controller code:

public function fanfiction(){

    $user_id = Session('userid');

    $data['stories'] =  DB::table('stories')

    ->leftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id')

    ->Select('*')

    ->get();



    return view('fanfiction', $data);

}

This is the result of above code in an array form.

        [0] => stdClass Object

            (

                [story_id] => 

                [story_title] => Third Story

                [story_desc] => This is "Times" story summary.

                [img] => 2018-12-26-70pgFK87ZN.png

                [story_genre] => Fighting

                [story_character] => Aoba Y.

                [story_rating] => Adult Only

                [story_warning] => Drug Use

                [story_date] => December 26, 2018

                [series_id] => 9999999

                [user_id] => 

                [fav_id] => 

            )



        [1] => stdClass Object

            (

                [story_id] => 

                [story_title] => Second Story

                [story_desc] => This is summary of this new story.

                [img] => 2018-12-19-e6JnEpo3ky.jpg

                [story_genre] => Friendship

                [story_character] => Aijsai

                [story_rating] => G

                [story_warning] => Rape (Actual)

                [story_date] => December 19, 2018

                [series_id] => 9999999

                [user_id] => 

                [fav_id] => 

            )

Any help would be highly appreciated.

asked Jan 1 at 17:33

Waqqar khan

add a comment |

Result in array form you can see "story_id", "user_id" result are empty, which should not be empty.
Fav_id is empty, which is OKAY because "favorite_story" table is empty.

I want "story_id" to be fetched from stories table and "user_id" to be fetched from "favorite_story" table.

This is my controller code:

public function fanfiction(){

    $user_id = Session('userid');

    $data['stories'] =  DB::table('stories')

    ->leftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id')

    ->Select('*')

    ->get();



    return view('fanfiction', $data);

}

This is the result of above code in an array form.

        [0] => stdClass Object

            (

                [story_id] => 

                [story_title] => Third Story

                [story_desc] => This is "Times" story summary.

                [img] => 2018-12-26-70pgFK87ZN.png

                [story_genre] => Fighting

                [story_character] => Aoba Y.

                [story_rating] => Adult Only

                [story_warning] => Drug Use

                [story_date] => December 26, 2018

                [series_id] => 9999999

                [user_id] => 

                [fav_id] => 

            )



        [1] => stdClass Object

            (

                [story_id] => 

                [story_title] => Second Story

                [story_desc] => This is summary of this new story.

                [img] => 2018-12-19-e6JnEpo3ky.jpg

                [story_genre] => Friendship

                [story_character] => Aijsai

                [story_rating] => G

                [story_warning] => Rape (Actual)

                [story_date] => December 19, 2018

                [series_id] => 9999999

                [user_id] => 

                [fav_id] => 

            )

Any help would be highly appreciated.

asked Jan 1 at 17:33

Waqqar khan

Result in array form you can see "story_id", "user_id" result are empty, which should not be empty.
Fav_id is empty, which is OKAY because "favorite_story" table is empty.

I want "story_id" to be fetched from stories table and "user_id" to be fetched from "favorite_story" table.

This is my controller code:

public function fanfiction(){

    $user_id = Session('userid');

    $data['stories'] =  DB::table('stories')

    ->leftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id')

    ->Select('*')

    ->get();



    return view('fanfiction', $data);

}

This is the result of above code in an array form.

        [0] => stdClass Object

            (

                [story_id] => 

                [story_title] => Third Story

                [story_desc] => This is "Times" story summary.

                [img] => 2018-12-26-70pgFK87ZN.png

                [story_genre] => Fighting

                [story_character] => Aoba Y.

                [story_rating] => Adult Only

                [story_warning] => Drug Use

                [story_date] => December 26, 2018

                [series_id] => 9999999

                [user_id] => 

                [fav_id] => 

            )



        [1] => stdClass Object

            (

                [story_id] => 

                [story_title] => Second Story

                [story_desc] => This is summary of this new story.

                [img] => 2018-12-19-e6JnEpo3ky.jpg

                [story_genre] => Friendship

                [story_character] => Aijsai

                [story_rating] => G

                [story_warning] => Rape (Actual)

                [story_date] => December 19, 2018

                [series_id] => 9999999

                [user_id] => 

                [fav_id] => 

            )

Any help would be highly appreciated.

php sql laravel join left-join

asked Jan 1 at 17:33

Waqqar khan

asked Jan 1 at 17:33

Waqqar khan

asked Jan 1 at 17:33

Waqqar khan

asked Jan 1 at 17:33

Waqqar khan

asked Jan 1 at 17:33

Waqqar khan

add a comment |

1 Answer
1

active

oldest

votes

Change your query to:

public function fanfiction()

{

    $user_id = session->get('userid');



    $data['stories'] =  DB::table('stories')

        ->leftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id')

        ->select('stories.*') // add other fields that you need from favorite_story table here

        ->get();



    return view('fanfiction', $data);

}

The reason this is happening is because, it is taking the user_id and story_id from the favorite_story table which in this case is null.

answered Jan 1 at 17:57

Mozammil

6,230420

I have made some corrections. Your logic really works thank you. But now I am facing another small issue with the same code. I want to show all stories on a webpage, which is showing there but the stories I marked as favorite for more than two users are showing 2 times which shouldn't be. All stories should be shown one time with favorite/unfavorite for a specific logged in user.

– Waqqar khan
Jan 2 at 0:20

This is the method I used now: public function fanfiction(){ $user_id = session('userid'); $data['stories'] = DB::table('stories') ->LeftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id') ->Select('favorite_story.user_id', 'stories.story_id', 'stories.story_title', 'stories.story_desc', 'stories.img') ->OrderBy('stories.story_id', 'desc') ->Get(); return view('fanfiction', $data); }

– Waqqar khan
Jan 2 at 0:22

You could either groupBy('stories.story_id') or add ->distinct()->get();

– Mozammil
Jan 2 at 4:20

groupBy('stories.story_id') shows query exception error "SQLSTATE[42000]: Syntax error or access violation:1055 'narutoworldfanfiction.favorite_story.user_id' isn't in GROUP BY (SQL: select favorite_story.user_id, stories.story_id, stories.story_title, stories.story_desc, stories.img from stories left join favorite_story on favorite_story.story_id = stories.story_id group by stories.story_id order by stories.story_id desc)" distinct() is showing me a repeated result of stories. I need to put a condition for the user who is logged in based on user_id

– Waqqar khan
Jan 2 at 10:32

This is result in array form: [0] => stdClass Object ( [user_id] => 1 [story_id] => 3 ) [1] => stdClass Object ( [user_id] => 3 [story_id] => 3 ) if you see both user_id 1 and 3 from favorite_story is showing which should not be, because I want to show favorite_story for the user who is logged in. I hope this array result will help you to understand my problem.

– Waqqar khan
Jan 2 at 10:35

|
show 1 more 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',
autoActivateHeartbeat: false,
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%2f53997526%2fhow-to-fetch-story-id-and-user-id-from-stories-tables%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Change your query to:

public function fanfiction()

{

    $user_id = session->get('userid');



    $data['stories'] =  DB::table('stories')

        ->leftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id')

        ->select('stories.*') // add other fields that you need from favorite_story table here

        ->get();



    return view('fanfiction', $data);

}

The reason this is happening is because, it is taking the user_id and story_id from the favorite_story table which in this case is null.

answered Jan 1 at 17:57

Mozammil

6,230420

I have made some corrections. Your logic really works thank you. But now I am facing another small issue with the same code. I want to show all stories on a webpage, which is showing there but the stories I marked as favorite for more than two users are showing 2 times which shouldn't be. All stories should be shown one time with favorite/unfavorite for a specific logged in user.

– Waqqar khan
Jan 2 at 0:20

This is the method I used now: public function fanfiction(){ $user_id = session('userid'); $data['stories'] = DB::table('stories') ->LeftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id') ->Select('favorite_story.user_id', 'stories.story_id', 'stories.story_title', 'stories.story_desc', 'stories.img') ->OrderBy('stories.story_id', 'desc') ->Get(); return view('fanfiction', $data); }

– Waqqar khan
Jan 2 at 0:22

You could either groupBy('stories.story_id') or add ->distinct()->get();

– Mozammil
Jan 2 at 4:20

groupBy('stories.story_id') shows query exception error "SQLSTATE[42000]: Syntax error or access violation:1055 'narutoworldfanfiction.favorite_story.user_id' isn't in GROUP BY (SQL: select favorite_story.user_id, stories.story_id, stories.story_title, stories.story_desc, stories.img from stories left join favorite_story on favorite_story.story_id = stories.story_id group by stories.story_id order by stories.story_id desc)" distinct() is showing me a repeated result of stories. I need to put a condition for the user who is logged in based on user_id

– Waqqar khan
Jan 2 at 10:32

This is result in array form: [0] => stdClass Object ( [user_id] => 1 [story_id] => 3 ) [1] => stdClass Object ( [user_id] => 3 [story_id] => 3 ) if you see both user_id 1 and 3 from favorite_story is showing which should not be, because I want to show favorite_story for the user who is logged in. I hope this array result will help you to understand my problem.

– Waqqar khan
Jan 2 at 10:35

|
show 1 more comment

Change your query to:

public function fanfiction()

{

    $user_id = session->get('userid');



    $data['stories'] =  DB::table('stories')

        ->leftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id')

        ->select('stories.*') // add other fields that you need from favorite_story table here

        ->get();



    return view('fanfiction', $data);

}

The reason this is happening is because, it is taking the user_id and story_id from the favorite_story table which in this case is null.

answered Jan 1 at 17:57

Mozammil

6,230420

I have made some corrections. Your logic really works thank you. But now I am facing another small issue with the same code. I want to show all stories on a webpage, which is showing there but the stories I marked as favorite for more than two users are showing 2 times which shouldn't be. All stories should be shown one time with favorite/unfavorite for a specific logged in user.

– Waqqar khan
Jan 2 at 0:20

This is the method I used now: public function fanfiction(){ $user_id = session('userid'); $data['stories'] = DB::table('stories') ->LeftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id') ->Select('favorite_story.user_id', 'stories.story_id', 'stories.story_title', 'stories.story_desc', 'stories.img') ->OrderBy('stories.story_id', 'desc') ->Get(); return view('fanfiction', $data); }

– Waqqar khan
Jan 2 at 0:22

You could either groupBy('stories.story_id') or add ->distinct()->get();

– Mozammil
Jan 2 at 4:20

groupBy('stories.story_id') shows query exception error "SQLSTATE[42000]: Syntax error or access violation:1055 'narutoworldfanfiction.favorite_story.user_id' isn't in GROUP BY (SQL: select favorite_story.user_id, stories.story_id, stories.story_title, stories.story_desc, stories.img from stories left join favorite_story on favorite_story.story_id = stories.story_id group by stories.story_id order by stories.story_id desc)" distinct() is showing me a repeated result of stories. I need to put a condition for the user who is logged in based on user_id

– Waqqar khan
Jan 2 at 10:32

This is result in array form: [0] => stdClass Object ( [user_id] => 1 [story_id] => 3 ) [1] => stdClass Object ( [user_id] => 3 [story_id] => 3 ) if you see both user_id 1 and 3 from favorite_story is showing which should not be, because I want to show favorite_story for the user who is logged in. I hope this array result will help you to understand my problem.

– Waqqar khan
Jan 2 at 10:35

|
show 1 more comment

Change your query to:

public function fanfiction()

{

    $user_id = session->get('userid');



    $data['stories'] =  DB::table('stories')

        ->leftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id')

        ->select('stories.*') // add other fields that you need from favorite_story table here

        ->get();



    return view('fanfiction', $data);

}

The reason this is happening is because, it is taking the user_id and story_id from the favorite_story table which in this case is null.

answered Jan 1 at 17:57

Mozammil

6,230420

Change your query to:

public function fanfiction()

{

    $user_id = session->get('userid');



    $data['stories'] =  DB::table('stories')

        ->leftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id')

        ->select('stories.*') // add other fields that you need from favorite_story table here

        ->get();



    return view('fanfiction', $data);

}

The reason this is happening is because, it is taking the user_id and story_id from the favorite_story table which in this case is null.

answered Jan 1 at 17:57

Mozammil

6,230420

answered Jan 1 at 17:57

Mozammil

6,230420

answered Jan 1 at 17:57

Mozammil

6,230420

answered Jan 1 at 17:57

Mozammil

6,230420

I have made some corrections. Your logic really works thank you. But now I am facing another small issue with the same code. I want to show all stories on a webpage, which is showing there but the stories I marked as favorite for more than two users are showing 2 times which shouldn't be. All stories should be shown one time with favorite/unfavorite for a specific logged in user.

– Waqqar khan
Jan 2 at 0:20

This is the method I used now: public function fanfiction(){ $user_id = session('userid'); $data['stories'] = DB::table('stories') ->LeftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id') ->Select('favorite_story.user_id', 'stories.story_id', 'stories.story_title', 'stories.story_desc', 'stories.img') ->OrderBy('stories.story_id', 'desc') ->Get(); return view('fanfiction', $data); }

– Waqqar khan
Jan 2 at 0:22

You could either groupBy('stories.story_id') or add ->distinct()->get();

– Mozammil
Jan 2 at 4:20

groupBy('stories.story_id') shows query exception error "SQLSTATE[42000]: Syntax error or access violation:1055 'narutoworldfanfiction.favorite_story.user_id' isn't in GROUP BY (SQL: select favorite_story.user_id, stories.story_id, stories.story_title, stories.story_desc, stories.img from stories left join favorite_story on favorite_story.story_id = stories.story_id group by stories.story_id order by stories.story_id desc)" distinct() is showing me a repeated result of stories. I need to put a condition for the user who is logged in based on user_id

– Waqqar khan
Jan 2 at 10:32

This is result in array form: [0] => stdClass Object ( [user_id] => 1 [story_id] => 3 ) [1] => stdClass Object ( [user_id] => 3 [story_id] => 3 ) if you see both user_id 1 and 3 from favorite_story is showing which should not be, because I want to show favorite_story for the user who is logged in. I hope this array result will help you to understand my problem.

– Waqqar khan
Jan 2 at 10:35

|
show 1 more comment

I have made some corrections. Your logic really works thank you. But now I am facing another small issue with the same code. I want to show all stories on a webpage, which is showing there but the stories I marked as favorite for more than two users are showing 2 times which shouldn't be. All stories should be shown one time with favorite/unfavorite for a specific logged in user.

– Waqqar khan
Jan 2 at 0:20

This is the method I used now: public function fanfiction(){ $user_id = session('userid'); $data['stories'] = DB::table('stories') ->LeftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id') ->Select('favorite_story.user_id', 'stories.story_id', 'stories.story_title', 'stories.story_desc', 'stories.img') ->OrderBy('stories.story_id', 'desc') ->Get(); return view('fanfiction', $data); }

– Waqqar khan
Jan 2 at 0:22

You could either groupBy('stories.story_id') or add ->distinct()->get();

– Mozammil
Jan 2 at 4:20

groupBy('stories.story_id') shows query exception error "SQLSTATE[42000]: Syntax error or access violation:1055 'narutoworldfanfiction.favorite_story.user_id' isn't in GROUP BY (SQL: select favorite_story.user_id, stories.story_id, stories.story_title, stories.story_desc, stories.img from stories left join favorite_story on favorite_story.story_id = stories.story_id group by stories.story_id order by stories.story_id desc)" distinct() is showing me a repeated result of stories. I need to put a condition for the user who is logged in based on user_id

– Waqqar khan
Jan 2 at 10:32

This is result in array form: [0] => stdClass Object ( [user_id] => 1 [story_id] => 3 ) [1] => stdClass Object ( [user_id] => 3 [story_id] => 3 ) if you see both user_id 1 and 3 from favorite_story is showing which should not be, because I want to show favorite_story for the user who is logged in. I hope this array result will help you to understand my problem.

– Waqqar khan
Jan 2 at 10:35

I have made some corrections. Your logic really works thank you. But now I am facing another small issue with the same code. I want to show all stories on a webpage, which is showing there but the stories I marked as favorite for more than two users are showing 2 times which shouldn't be. All stories should be shown one time with favorite/unfavorite for a specific logged in user.

– Waqqar khan
Jan 2 at 0:20

This is the method I used now: public function fanfiction(){ $user_id = session('userid'); $data['stories'] = DB::table('stories') ->LeftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id') ->Select('favorite_story.user_id', 'stories.story_id', 'stories.story_title', 'stories.story_desc', 'stories.img') ->OrderBy('stories.story_id', 'desc') ->Get(); return view('fanfiction', $data); }

– Waqqar khan
Jan 2 at 0:22

You could either groupBy('stories.story_id') or add ->distinct()->get();

– Mozammil
Jan 2 at 4:20

groupBy('stories.story_id') shows query exception error "SQLSTATE[42000]: Syntax error or access violation:1055 'narutoworldfanfiction.favorite_story.user_id' isn't in GROUP BY (SQL: select favorite_story.user_id, stories.story_id, stories.story_title, stories.story_desc, stories.img from stories left join favorite_story on favorite_story.story_id = stories.story_id group by stories.story_id order by stories.story_id desc)" distinct() is showing me a repeated result of stories. I need to put a condition for the user who is logged in based on user_id

– Waqqar khan
Jan 2 at 10:32

This is result in array form: [0] => stdClass Object ( [user_id] => 1 [story_id] => 3 ) [1] => stdClass Object ( [user_id] => 3 [story_id] => 3 ) if you see both user_id 1 and 3 from favorite_story is showing which should not be, because I want to show favorite_story for the user who is logged in. I hope this array result will help you to understand my problem.

– Waqqar khan
Jan 2 at 10:35

|
show 1 more 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.

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

Search This Blog

Ufyukyu