How to get the value of specific index inside an array column using mysql?
0
$reports = DB::table('attendance_report_details')
->select('*','project_hours[$index_id]')
->get();
I am passing index id to the function. Is there a way to do it like this?
mysql arrays json laravel-5 mysqli
asked Nov 14 '18 at 7:35
RoshJRoshJ
207117
|
show 1 more comment
0
$reports = DB::table('attendance_report_details')
->select('*','project_hours[$index_id]')
->get();
I am passing index id to the function. Is there a way to do it like this?
mysql arrays json laravel-5 mysqli
asked Nov 14 '18 at 7:35
RoshJRoshJ
207117
Possibly. But why are you storing your data in a non-relational format like this?
– ADyson
Nov 14 '18 at 9:20
I am storing the data as json ecoded data
– RoshJ
Nov 14 '18 at 9:39
I know you are, I can see that. My question was why are you doing it like that, rather than creating a proper relational structure?
– ADyson
Nov 14 '18 at 10:40
Because the project hour is related to a corresponding project.The sequence of project may differ for each att_rep_id.Also, old projects may be deleted. So its needed to store it like this.
– RoshJ
Nov 14 '18 at 11:16
None of that stops you from making a proper structure using separate tables and foreign keys. If you did that you could have many-to-many relationships linking the employee table to the project table via a project hours table which stores the hours, and foreign keys to the employee and projects tables. Then writing queries to get particular hours is trival using SQL. Maybe you first need to study database design in more detail if you didn't realise this kind of thing?
– ADyson
Nov 14 '18 at 11:20
|
show 1 more comment
0
0
0
1
$reports = DB::table('attendance_report_details')
->select('*','project_hours[$index_id]')
->get();
I am passing index id to the function. Is there a way to do it like this?
mysql arrays json laravel-5 mysqli
asked Nov 14 '18 at 7:35
RoshJRoshJ
207117
$reports = DB::table('attendance_report_details')
->select('*','project_hours[$index_id]')
->get();
I am passing index id to the function. Is there a way to do it like this?
mysql arrays json laravel-5 mysqli
mysql arrays json laravel-5 mysqli
asked Nov 14 '18 at 7:35
RoshJRoshJ
207117
asked Nov 14 '18 at 7:35
RoshJRoshJ
207117
edited Nov 14 '18 at 8:01
RoshJ
asked Nov 14 '18 at 7:35
RoshJRoshJ
207117
asked Nov 14 '18 at 7:35
RoshJRoshJ
207117
asked Nov 14 '18 at 7:35
RoshJRoshJ
207117
207117
Possibly. But why are you storing your data in a non-relational format like this?
– ADyson
Nov 14 '18 at 9:20
I am storing the data as json ecoded data
– RoshJ
Nov 14 '18 at 9:39
I know you are, I can see that. My question was why are you doing it like that, rather than creating a proper relational structure?
– ADyson
Nov 14 '18 at 10:40
Because the project hour is related to a corresponding project.The sequence of project may differ for each att_rep_id.Also, old projects may be deleted. So its needed to store it like this.
– RoshJ
Nov 14 '18 at 11:16
None of that stops you from making a proper structure using separate tables and foreign keys. If you did that you could have many-to-many relationships linking the employee table to the project table via a project hours table which stores the hours, and foreign keys to the employee and projects tables. Then writing queries to get particular hours is trival using SQL. Maybe you first need to study database design in more detail if you didn't realise this kind of thing?
– ADyson
Nov 14 '18 at 11:20
|
show 1 more comment
Possibly. But why are you storing your data in a non-relational format like this?
– ADyson
Nov 14 '18 at 9:20
I am storing the data as json ecoded data
– RoshJ
Nov 14 '18 at 9:39
I know you are, I can see that. My question was why are you doing it like that, rather than creating a proper relational structure?
– ADyson
Nov 14 '18 at 10:40
Because the project hour is related to a corresponding project.The sequence of project may differ for each att_rep_id.Also, old projects may be deleted. So its needed to store it like this.
– RoshJ
Nov 14 '18 at 11:16
None of that stops you from making a proper structure using separate tables and foreign keys. If you did that you could have many-to-many relationships linking the employee table to the project table via a project hours table which stores the hours, and foreign keys to the employee and projects tables. Then writing queries to get particular hours is trival using SQL. Maybe you first need to study database design in more detail if you didn't realise this kind of thing?
– ADyson
Nov 14 '18 at 11:20
Possibly. But why are you storing your data in a non-relational format like this?
– ADyson
Nov 14 '18 at 9:20
Possibly. But why are you storing your data in a non-relational format like this?
– ADyson
Nov 14 '18 at 9:20
I am storing the data as json ecoded data
– RoshJ
Nov 14 '18 at 9:39
I am storing the data as json ecoded data
– RoshJ
Nov 14 '18 at 9:39
I know you are, I can see that. My question was why are you doing it like that, rather than creating a proper relational structure?
– ADyson
Nov 14 '18 at 10:40
I know you are, I can see that. My question was why are you doing it like that, rather than creating a proper relational structure?
– ADyson
Nov 14 '18 at 10:40
Because the project hour is related to a corresponding project.The sequence of project may differ for each att_rep_id.Also, old projects may be deleted. So its needed to store it like this.
– RoshJ
Nov 14 '18 at 11:16
Because the project hour is related to a corresponding project.The sequence of project may differ for each att_rep_id.Also, old projects may be deleted. So its needed to store it like this.
– RoshJ
Nov 14 '18 at 11:16
None of that stops you from making a proper structure using separate tables and foreign keys. If you did that you could have many-to-many relationships linking the employee table to the project table via a project hours table which stores the hours, and foreign keys to the employee and projects tables. Then writing queries to get particular hours is trival using SQL. Maybe you first need to study database design in more detail if you didn't realise this kind of thing?
– ADyson
Nov 14 '18 at 11:20
None of that stops you from making a proper structure using separate tables and foreign keys. If you did that you could have many-to-many relationships linking the employee table to the project table via a project hours table which stores the hours, and foreign keys to the employee and projects tables. Then writing queries to get particular hours is trival using SQL. Maybe you first need to study database design in more detail if you didn't realise this kind of thing?
– ADyson
Nov 14 '18 at 11:20
|
show 1 more comment
1 Answer
1
active
oldest
votes
1
Use the arrow operator (MySQL-only):
$reports = DB::table('attendance_report_details')
->select('*', DB::raw("project_hours->'$[".(int) $index_id."]'"))
->get();
Or its alias JSON_EXTRACT() (MySQL & MariaDB):
$reports = DB::table('attendance_report_details')
->select('*', DB::raw("json_extract(project_hours, '$[".(int) $index_id."]')"))
->get();
answered Nov 14 '18 at 13:10
Jonas StaudenmeirJonas Staudenmeir
12.9k2935
Nope.. :( I am getting an error. For the first solution the error is "Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$[1]' fromattendance_report_details' at line 1"
– RoshJ
Nov 19 '18 at 7:09
For the second solution: json functions can be used only in mariaDB version 10.2.3 and above . My version is 10.1.36. I am using xampp
– RoshJ
Nov 19 '18 at 7:12
Anyways, thanks for your answer :) @Jonas Staudenmeir
– RoshJ
Nov 19 '18 at 7:12
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',
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');
);
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%2f53295142%2fhow-to-get-the-value-of-specific-index-inside-an-array-column-using-mysql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Use the arrow operator (MySQL-only):
$reports = DB::table('attendance_report_details')
->select('*', DB::raw("project_hours->'$[".(int) $index_id."]'"))
->get();
Or its alias JSON_EXTRACT() (MySQL & MariaDB):
$reports = DB::table('attendance_report_details')
->select('*', DB::raw("json_extract(project_hours, '$[".(int) $index_id."]')"))
->get();
answered Nov 14 '18 at 13:10
Jonas StaudenmeirJonas Staudenmeir
12.9k2935
Nope.. :( I am getting an error. For the first solution the error is "Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$[1]' fromattendance_report_details' at line 1"
– RoshJ
Nov 19 '18 at 7:09
For the second solution: json functions can be used only in mariaDB version 10.2.3 and above . My version is 10.1.36. I am using xampp
– RoshJ
Nov 19 '18 at 7:12
Anyways, thanks for your answer :) @Jonas Staudenmeir
– RoshJ
Nov 19 '18 at 7:12
add a comment |
1
Use the arrow operator (MySQL-only):
$reports = DB::table('attendance_report_details')
->select('*', DB::raw("project_hours->'$[".(int) $index_id."]'"))
->get();
Or its alias JSON_EXTRACT() (MySQL & MariaDB):
$reports = DB::table('attendance_report_details')
->select('*', DB::raw("json_extract(project_hours, '$[".(int) $index_id."]')"))
->get();
answered Nov 14 '18 at 13:10
Jonas StaudenmeirJonas Staudenmeir
12.9k2935
Nope.. :( I am getting an error. For the first solution the error is "Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$[1]' fromattendance_report_details' at line 1"
– RoshJ
Nov 19 '18 at 7:09
For the second solution: json functions can be used only in mariaDB version 10.2.3 and above . My version is 10.1.36. I am using xampp
– RoshJ
Nov 19 '18 at 7:12
Anyways, thanks for your answer :) @Jonas Staudenmeir
– RoshJ
Nov 19 '18 at 7:12
add a comment |
1
1
1
Use the arrow operator (MySQL-only):
$reports = DB::table('attendance_report_details')
->select('*', DB::raw("project_hours->'$[".(int) $index_id."]'"))
->get();
Or its alias JSON_EXTRACT() (MySQL & MariaDB):
$reports = DB::table('attendance_report_details')
->select('*', DB::raw("json_extract(project_hours, '$[".(int) $index_id."]')"))
->get();
answered Nov 14 '18 at 13:10
Jonas StaudenmeirJonas Staudenmeir
12.9k2935
Use the arrow operator (MySQL-only):
$reports = DB::table('attendance_report_details')
->select('*', DB::raw("project_hours->'$[".(int) $index_id."]'"))
->get();
Or its alias JSON_EXTRACT() (MySQL & MariaDB):
$reports = DB::table('attendance_report_details')
->select('*', DB::raw("json_extract(project_hours, '$[".(int) $index_id."]')"))
->get();
answered Nov 14 '18 at 13:10
Jonas StaudenmeirJonas Staudenmeir
12.9k2935
edited Nov 19 '18 at 14:10
answered Nov 14 '18 at 13:10
Jonas StaudenmeirJonas Staudenmeir
12.9k2935
answered Nov 14 '18 at 13:10
Jonas StaudenmeirJonas Staudenmeir
12.9k2935
answered Nov 14 '18 at 13:10
Jonas StaudenmeirJonas Staudenmeir
12.9k2935
12.9k2935
Nope.. :( I am getting an error. For the first solution the error is "Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$[1]' fromattendance_report_details' at line 1"
– RoshJ
Nov 19 '18 at 7:09
For the second solution: json functions can be used only in mariaDB version 10.2.3 and above . My version is 10.1.36. I am using xampp
– RoshJ
Nov 19 '18 at 7:12
Anyways, thanks for your answer :) @Jonas Staudenmeir
– RoshJ
Nov 19 '18 at 7:12
add a comment |
Nope.. :( I am getting an error. For the first solution the error is "Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$[1]' fromattendance_report_details' at line 1"
– RoshJ
Nov 19 '18 at 7:09
For the second solution: json functions can be used only in mariaDB version 10.2.3 and above . My version is 10.1.36. I am using xampp
– RoshJ
Nov 19 '18 at 7:12
Anyways, thanks for your answer :) @Jonas Staudenmeir
– RoshJ
Nov 19 '18 at 7:12
Nope.. :( I am getting an error. For the first solution the error is "Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$[1]' from
attendance_report_details' at line 1"– RoshJ
Nov 19 '18 at 7:09
Nope.. :( I am getting an error. For the first solution the error is "Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$[1]' from
attendance_report_details' at line 1"– RoshJ
Nov 19 '18 at 7:09
For the second solution: json functions can be used only in mariaDB version 10.2.3 and above . My version is 10.1.36. I am using xampp
– RoshJ
Nov 19 '18 at 7:12
For the second solution: json functions can be used only in mariaDB version 10.2.3 and above . My version is 10.1.36. I am using xampp
– RoshJ
Nov 19 '18 at 7:12
Anyways, thanks for your answer :) @Jonas Staudenmeir
– RoshJ
Nov 19 '18 at 7:12
Anyways, thanks for your answer :) @Jonas Staudenmeir
– RoshJ
Nov 19 '18 at 7:12
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.
draft saved
draft discarded
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%2f53295142%2fhow-to-get-the-value-of-specific-index-inside-an-array-column-using-mysql%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
Possibly. But why are you storing your data in a non-relational format like this?
– ADyson
Nov 14 '18 at 9:20
I am storing the data as json ecoded data
– RoshJ
Nov 14 '18 at 9:39
I know you are, I can see that. My question was why are you doing it like that, rather than creating a proper relational structure?
– ADyson
Nov 14 '18 at 10:40
Because the project hour is related to a corresponding project.The sequence of project may differ for each att_rep_id.Also, old projects may be deleted. So its needed to store it like this.
– RoshJ
Nov 14 '18 at 11:16
None of that stops you from making a proper structure using separate tables and foreign keys. If you did that you could have many-to-many relationships linking the employee table to the project table via a project hours table which stores the hours, and foreign keys to the employee and projects tables. Then writing queries to get particular hours is trival using SQL. Maybe you first need to study database design in more detail if you didn't realise this kind of thing?
– ADyson
Nov 14 '18 at 11:20