Nested Json into MySQL

shades · July 12, 2017

Hi Guys,

I am newbie to programming. I have a requirement to get an array of input from user which i have to send to PHP then use this array to store data in MySQL table/s.

Note: I am using mysqli and no framework for PHP.

Current Code:(Example)

//The below forms are dynamic means user can add any number of forms with a basic template of Name Description and Level 1 and user has the option to add more levels

<form id="addform"> // linked to a list name MainList

<label> Name </label> <input type="text name="list[0][0] "/>
<label> Description</label> <input type="text name="list[0][1] "/>
<label> Level 1 </label> <input type="text name="list[0][2][0] "/>
<label> Level 2 </label> <input type="text name="list[0][2][1] "/>
<label> Level 3 </label> <input type="text name="list[0][2][2] "/>

<label> Name </label> <input type="text name="list[1][0] "/>
<label> Description</label> <input type="text name="list[1][1] "/>
<label> Level 1 </label> <input type="text name="list[1][2][0] "/>
<label> Level 2 </label> <input type="text name="list[1][2][1] "/>

<label> Name </label> <input type="text name="list[2][0] "/>
<label> Description</label> <input type="text name="list[2][1] "/>
<label> Level 1 </label> <input type="text name="list[2][2][0] "/>
<label> Level 2 </label> <input type="text name="list[2][2][1] "/>
<label> Level 3 </label> <input type="text name="list[2][2][2] "/>

<input type="submit" name="Send" value="Send" id="Send" />

//I am using ajax as shown below and I am able to post the data

$.ajax({
url: "post.php",
method: "POST",
data: $('#addform').serialize(),
success: function(data) {
alert(data);
}
});

//Using below php I am able to get the data and convert it into Json

<?php

include("dbconnect.php");

$x = json_encode($_POST['list'], JSON_FORCE_OBJECT);

?>

//Now i am getting the data something like below :

{"0":
{ "0":"list1 text",
"1":"list1 descr",
"2":
{ "0":"list1 level1",
"1":"list1 level2",
"2":"list1 level3"
}
},

"1":
{ "0":"list2 text ",
"1":"list2 descr",
"2":
{ "0":"list2 level1",
"1":"list2 level2"
}
},

"2":
{ "0":"list3 text",
"1":"list3 descr",
"2":
{ "0":"list3 level1",
"1":"list3 level2",
"2":"list3 level3"
}
}
}

Requirement 2: I am not sure if to store in 2 separate tables or in a single table

2 tables method:

ListTable:

LID ListName

0

Requirement 3: I need generate a combination of texts from above data and also store this in a separate table.

Text Table Example:(description is not stored)

Text ID ListName Text

1 MainList list1 text1 list2 text1 list3 text1

2 MainList list1 text2 list2 text1 list3 text1

3 MainList list1 text3 list2 text1 list3 text1

4 MainList list1 text1 list2 text2 list3 text1

5 MainList list1 text2 list2 text2 list3 text1

6 MainList list1 text3 list2 text2 list3 text1

7 MainList list1 text1 list2 text1 list3 text2

8 MainList list1 text2 list2 text1 list3 text2

9 MainList list1 text3 list2 text1 list3 text2

10 MainList list1 text1 list2 text2 list3 text2

11 MainList list1 text2 list2 text2 list3 text2

12 MainList list1 text3 list2 text2 list3 text2

13 MainList list1 text1 list2 text1 list3 text3

14 MainList list1 text2 list2 text1 list3 text3

15 MainList list1 text3 list2 text1 list3 text3

16 MainList list1 text1 list2 text2 list3 text3

17 MainList list1 text2 list2 text2 list3 text3

18 MainList list1 text3 list2 text2 list3 text3

shades · July 12, 2017

Sorry I did not know i cannot edit post once submitted :/ Below is the full post

Hi Guys,

I am newbie to programming. I have a requirement to get an array of input from user which i have to send to PHP then use this array to store data in MySQL table/s.

Note: I am using mysqli and no framework for PHP.

Current Code:(Example)

//The below forms are dynamic means user can add any number of forms with a basic template of Name Description and Level 1 and user has the option to add more levels

<form id="addform"> // linked to a list name MainList (got from URL)

<label> Name </label> <input type="text name="list[0][0] "/>
<label> Description</label> <input type="text name="list[0][1] "/>
<label> Level 1 </label> <input type="text name="list[0][2][0] "/>
<label> Level 2 </label> <input type="text name="list[0][2][1] "/>
<label> Level 3 </label> <input type="text name="list[0][2][2] "/>

<label> Name </label> <input type="text name="list[1][0] "/>
<label> Description</label> <input type="text name="list[1][1] "/>
<label> Level 1 </label> <input type="text name="list[1][2][0] "/>
<label> Level 2 </label> <input type="text name="list[1][2][1] "/>

<label> Name </label> <input type="text name="list[2][0] "/>
<label> Description</label> <input type="text name="list[2][1] "/>
<label> Level 1 </label> <input type="text name="list[2][2][0] "/>
<label> Level 2 </label> <input type="text name="list[2][2][1] "/>
<label> Level 3 </label> <input type="text name="list[2][2][2] "/>

<input type="submit" name="Send" value="Send" id="Send" />

//I am using ajax as shown below and I am able to post the data

$.ajax({
url: "post.php",
method: "POST",
data: $('#addform').serialize(),
success: function(data) {
alert(data);
}
});

//Using below php I am able to get the data and convert it into Json

<?php

include("dbconnect.php");

$x = json_encode($_POST['list'], JSON_FORCE_OBJECT);

echo $_POST['listname'];

echo $x;

?>

//Now i am getting the data something like below :

{"0":
{ "0":"list1 text",
"1":"list1 descr",
"2":
{ "0":"list1 level1",
"1":"list1 level2",
"2":"list1 level3"
}
},

"1":
{ "0":"list2 text ",
"1":"list2 descr",
"2":
{ "0":"list2 level1",
"1":"list2 level2"
}
},

"2":
{ "0":"list3 text",
"1":"list3 descr",
"2":
{ "0":"list3 level1",
"1":"list3 level2",
"2":"list3 level3"
}
}
}

Requirement 2: I am considering single table but let me know if it is the right way to go about to achieve requirement 3

Table:

ID ListName ListText ListDescription ListID ListLevel 1 MainList list1text list1decr 1 list1level1 2 MainList list1text list1decr 2 list1level2 3 MainList list1text list1decr 3 list1level3 4 MainList list2text list2descr 1 list2level1 5 MainList list2text list2descr 2 list2level2 6 MainList list3text list3descr 1 list3level1 7 MainList list3text list3descr 2 list3level2 8 MainList list3text list3descr 3 list3level3

Requirement 3: I need generate a combination of texts from above data and also store this in a separate table.

Text Table Example:(description is not considered)

ID ListName Text 1 MainList list1text list1level1 list2text list2level1 list3text list3 level1 2 MainList list1text list1level2 list2text list2level1 list3text list3 level1 3 MainList list1text list1level3 list2text list2level1 list3text list3 level1 4 MainList list1text list1level1 list2text list2level2 list3text list3 level1 5 MainList list1text list1level2 list2text list2level2 list3text list3 level1 6 MainList list1text list1level3 list2text list2level2 list3text list3 level1 7 MainList list1text list1level1 list2text list2level1 list3text list3 level2 8 MainList list1text list1level2 list2text list2level1 list3text list3 level2 9 MainList list1text list1level3 list2text list2level1 list3text list3 level2 10 MainList list1text list1level1 list2text list2level2 list3text list3 level2 11 MainList list1text list1level2 list2text list2level2 list3text list3 level2 12 MainList list1text list1level3 list2text list2level2 list3text list3 level2 13 MainList list1text list1level1 list2text list2level1 list3text list3 level3 14 MainList list1text list1level2 list2text list2level1 list3text list3 level3 15 MainList list1text list1level3 list2text list2level1 list3text list3 level3 16 MainList list1text list1level1 list2text list2level2 list3text list3 level3 17 MainList list1text list1level2 list2text list2level2 list3text list3 level3 18 MainList list1text list1level3 list2text list2level2 list3text list3 level3

I would really appreciate any help in this regards.Thanks in advance !!!! and sorry for the long post :/

Psycho · July 12, 2017

When creating a database table, if you find yourself creating fields such as list1text , list2text , etc. it is almost certain the wrong way. There are many reasons, but here is one.

Let's say you need some functionality to find all records that have a list text item that contains a certain value. You would have to build a query that looks like this

SELECT *
FROM table_name
WHERE list1text LIKE '%searchvalue%'
   OR list2text LIKE '%searchvalue%'
   OR list3text LIKE '%searchvalue%'
   OR list4text LIKE '%searchvalue%'

That's bad enough, but what if you then need to add more list items to records in the future? You would have to find all queries where you implemented this type of logic to update them.

When you have a many-to-one scenario, you should have a separate table for the child elements. Your field names are confusing, and I don't understand what everything is. it looks likethe parent element is a list and the child elements are options - so let's refer to the elements that use " list1text , list2text, . . . " as the options.

You first want a "lists" table with fields such as: ID ListName ListText ListDescription

Then you would have an "options" table with fields such as: list_id, option_text, option_level

The "list_id" in this table will be a foreign key back to the list table for reference. Now you can have one option per list or hundreds. But, if you have a requirement for each list to have exactly n options, you can force that through your logic as well. Now, if you needed to search for list items that have an option with specific text you can write a function that never needs to change based on the number of options a list may have. Example:

SELECT *
FROM lists
INNER JOIN options ON lists.id = options.list_id
WHERE option_text LIKE '%searchvalue%'
GROUP BY list.id

As to your last requirement: I need generate a combination of texts from above data and also store this in a separate table.

I have no clue what that means. You reused the terms 'list' and 'text' on so many elements without any context it doesn't mean anything to anyone but yourself.

shades · July 13, 2017

When creating a database table, if you find yourself creating fields such as list1text , list2text , etc. it is almost certain the wrong way. There are many reasons, but here is one.

Let's say you need some functionality to find all records that have a list text item that contains a certain value. You would have to build a query that looks like this
SELECT *
FROM table_name
WHERE list1text LIKE '%searchvalue%'
   OR list2text LIKE '%searchvalue%'
   OR list3text LIKE '%searchvalue%'
   OR list4text LIKE '%searchvalue%'
That's bad enough, but what if you then need to add more list items to records in the future? You would have to find all queries where you implemented this type of logic to update them.

When you have a many-to-one scenario, you should have a separate table for the child elements. Your field names are confusing, and I don't understand what everything is. it looks likethe parent element is a list and the child elements are options - so let's refer to the elements that use " list1text , list2text, . . . " as the options.

You first want a "lists" table with fields such as: ID ListName ListText ListDescription

Then you would have an "options" table with fields such as: list_id, option_text, option_level

The "list_id" in this table will be a foreign key back to the list table for reference. Now you can have one option per list or hundreds. But, if you have a requirement for each list to have exactly n options, you can force that through your logic as well. Now, if you needed to search for list items that have an option with specific text you can write a function that never needs to change based on the number of options a list may have. Example:
SELECT *
FROM lists
INNER JOIN options ON lists.id = options.list_id
WHERE option_text LIKE '%searchvalue%'
GROUP BY list.id
As to your last requirement: I need generate a combination of texts from above data and also store this in a separate table.

I have no clue what that means. You reused the terms 'list' and 'text' on so many elements without any context it doesn't mean anything to anyone but yourself.

Thanks for the reply. I know that having multiple tables with relations are good as we can avoid duplications. In my case there wont be any duplicates as the listname is always unique. So only i prefered a single table and let me know if i am still doing something wrong here.

And for my last requirement the way i showed it is not so clear. I will explain you with proper example below.

1. I have a unique List Name which user enters and is stored in a separate table in db

2. Next with reference to the List Name user has the option to have dynamic Dimensions form with fields: Text, Description and Levels(which is also added dynamic)

3. Once user saves it has to first store data in db with the proper relation like Dimension1 Text Description and Level1 value, as it is dynamic it goes on till the last level say there are 10 levels then it should be Dimension1 Text Description and Level10 value. Similarly the data must be stored for consequent Dimensions and all these Dimensions are linked with List Name which is unique

4. Now regarding my 3rd requirement to make a combination of data from the above table(see the below example)

Example HTML form: List[0] is Dimension 1 , List [1] is DImension 2,

</form>

Once user submits it has to store the above information in Db in the first table/s.

The combination should be something like in attached file which i need to store in a separate table along with ListName as unique ID

So hopefully u get my idea

Edited July 13, 2017 by shades

Jacques1 · July 13, 2017

In my case there wont be any duplicates as the listname is always unique.

You have redundant data all over the place.

In your one-table spreadsheet-like model, you keep repeating the "dimension"-specific data (text and description) for every "dimension" item. So if, for example, you have a "dimension" with 100 items, you store the text and description of that "dimension" 100 times. This not only massively bloats the amount of data. It also leads to anomalies and can make the entire database inconsistent.

When we told you to normalize your database, we weren't joking. It's one of the most fundamental aspects of a correct design.

The combination should be something like in attached file which i need to store in a separate table along with ListName as unique ID

No, you do not store that information, because it's directly derived from the data your already have. When you need to display all possible combinations, just calculate them.

Psycho · July 13, 2017

Thanks for the reply. I know that having multiple tables with relations are good as we can avoid duplications. In my case there wont be any duplicates as the listname is always unique. So only i prefered a single table and let me know if i am still doing something wrong here.

Maybe I missed it, but I don't think I said anything about duplicates in my previous response being the (one) reason why your structure was wrong. that is one reason why you would not store data like that, but it is not the only reason. I can't speak for everyone, but I think I am right in saying that most of us that donate our time to help people on this forum will not waste our time to help someone create a solution that is completely contrary to the most basic best practices.

No code is ever perfect, and sometimes a "working" solution is worth more than the "right" solution. But, for someone that is learning, if you successfully solve a problem using a bad process/structure/etc, you will go back to that same thing when you have a similar problem in the future. So, why should I help someone to be a bad programmer?

shades · July 13, 2017

Maybe I missed it, but I don't think I said anything about duplicates in my previous response being the (one) reason why your structure was wrong. that is one reason why you would not store data like that, but it is not the only reason. I can't speak for everyone, but I think I am right in saying that most of us that donate our time to help people on this forum will not waste our time to help someone create a solution that is completely contrary to the most basic best practices.

No code is ever perfect, and sometimes a "working" solution is worth more than the "right" solution. But, for someone that is learning, if you successfully solve a problem using a bad process/structure/etc, you will go back to that same thing when you have a similar problem in the future. So, why should I help someone to be a bad programmer?

Yes I think I will take your advice and make sure I do the right way, which might be hard for me right now. But I will try. Thanks.

Sign In

Nested Json into MySQL

Recommended Posts

shades

Link to comment

Share on other sites

shades

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

shades

Link to comment

Share on other sites

Jacques1

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

shades

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information