Adamhumbug Posted February 27 Share Posted February 27 I am making an application where the user can make a form. The first pick a form name and a description (to be saved to the forms table) Then they select the fields that they want to use from various drop downs, set the label and some bootstrap size properties with the aim being that once all of the fields have been chosen i will be able to create a form to show to end users. A simple example of this could be: Field Name ------ Field Label ------ Width ------ Order First Name ----- Given Name ----- 6 ----- 1 Last Name ----- Surname ----- 6 ------ 2 (i wish i could make tables on here) When these fields have been selected and related data amended, i am wanting to store them in the database so that i can query the data and build the form. I have been thinking about the best way to do this as each form with have a different and unknown number of fields. Should i have a link table that has a new row per field added to the form - something like this: form_id ----- field_id 1 ----- 1 1 ----- 2 1 ----- 3 or should i look at storing the fields as JSON in the form table itself. I have never gone down the JSON route and dont know how to interact with it or if it is a good route. Advice here would be appreciated. Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted February 28 Author Share Posted February 28 A simple example of this could be: FieldName Field Label Width Order First Name Given Name 6 1 Last Name Surname 6 2 Should i have a link table that has a new row per field added to the form - something like this: form_id field_id 1 1 1 2 1 3 Quote Link to comment Share on other sites More sharing options...
Barand Posted February 28 Share Posted February 28 6 minutes ago, Adamhumbug said: (i wish i could make tables on here) You can. The trick is use a code block for consistent charater widths. EG... +------------+-------------+--------+-------+ | Field Name | Field Label | Width | Order | +------------+-------------+--------+-------+ |First Name | Given Name | 6 | 1 | |Last Name | Surname | 6 | 2 | +------------+-------------+--------+-------+ I've used json columns in the past. I have found it a good alternative to the EAV data model (PITA). For example, in my sample data below there is a product table with 3 categories of product. Each category has different attribute: Membership - type, duration Book - author, title T-shirt = size, colour, style In a conventional relational table each attribute would require its own column and most of them would be empty. The json version uses a single column. The other table was for testing arrays and accessing and joining on array elements. You would store the metadata in your database to define which attributes each category should have. +--------+-----------+-----------------------------+ | cat_id | cat_name | attributes | +--------+-----------+-----------------------------+ | 1 |Membership | ["Type", "Duration"] | | 2 |Book | ["Title", "Author"] | | 3 |T-shirt | ["Size", "Colour", "Style"] | +--------+-----------+-----------------------------+ TEST DATA CREATE TABLE `product_j` ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `prod_name` varchar(45) DEFAULT NULL, `category_id` int(11) DEFAULT NULL, `attributes` json DEFAULT NULL, PRIMARY KEY (`product_id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; INSERT INTO `product_j` VALUES (1,'Standard Membership (12 months)',1,'{\"Type\": \"S\", \"Duration\": 12}'),(2,'Premium Membership (3 months)',1,'{\"Type\": \"P\", \"Duration\": 3}'),(3,'Brave New World',2,'{\"Title\": \"Brave New World\", \"Author\": \"Aldus Huxley\"}'),(4,'Philosophers Stone',2,'{\"Title\": \"Harry Potter and the Philosophers Stone\", \"Author\": \"JK Rowling\"}'),(5,'T-Shirt 1',3,'{\"Size\": \"M\", \"Color\": \"Red\", \"Style\": \"V-neck\"}'),(6,'T-Shirt 2',3,'{\"Size\": \"L\", \"Color\": \"White\", \"Style\": \"V-neck\"}'),(7,'T-Shirt 3',3,'{\"Size\": \"L\", \"Color\": \"Red\", \"Style\": \"V-neck\"}'),(8,'T-Shirt 4',3,'{\"Size\": \"L\", \"Color\": \"Black\", \"Style\": \"crew-neck\"}'),(9,'Goblet of Fire',2,'{\"Title\": \"Harry Potter and the Goblet of Fire\", \"Author\": \"JK Rowling\"}'); +------------+---------------------------------+-------------+------------------------------------------------------------------------------+ | product_id | prod_name | category_id | attributes | +------------+---------------------------------+-------------+------------------------------------------------------------------------------+ | 1 | Standard Membership (12 months) | 1 | {"Type": "S", "Duration": 12} | | 2 | Premium Membership (3 months) | 1 | {"Type": "P", "Duration": 3} | | 3 | Brave New World | 2 | {"Title": "Brave New World", "Author": "Aldus Huxley"} | | 4 | Philosophers Stone | 2 | {"Title": "Harry Potter and the Philosophers Stone", "Author": "JK Rowling"} | | 5 | T-Shirt 1 | 3 | {"Size": "M", "Color": "Red", "Style": "V-neck"} | | 6 | T-Shirt 2 | 3 | {"Size": "L", "Color": "White", "Style": "V-neck"} | | 7 | T-Shirt 3 | 3 | {"Size": "L", "Color": "Red", "Style": "V-neck"} | | 8 | T-Shirt 4 | 3 | {"Size": "L", "Color": "Black", "Style": "crew-neck"} | | 9 | Goblet of Fire | 2 | {"Title": "Harry Potter and the Goblet of Fire", "Author": "JK Rowling"} | +------------+---------------------------------+-------------+------------------------------------------------------------------------------+ CREATE TABLE `json_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `jdata` json DEFAULT NULL, `role` json DEFAULT NULL, `name` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; INSERT INTO `json_test` VALUES (1,'{\"town\": \"Chester\", \"county\": \"Cheshire\", \"country\": 1}','[1, 2, 3]','Peter'),(2,'{\"town\": \"Tenby\", \"county\": \"Pembrokeshire\", \"country\": 3}','[1, 3]','Paul'),(3,'{\"town\": \"Lancaster\", \"county\": \"Lancashire\", \"country\": 1}','[1, 2, 4]','Mary'),(4,'{\"town\": \"Dorchester\", \"county\": \"Dorset\", \"country\": 1}','[2, 4]','Jane'),(5,'{\"town\": \"Caernarfon\", \"county\": \"Cardigan\", \"country\": 3}','[1, 2, 3, 4]','Fred'); +----+-------------------------------------------------------------+--------------+-------+ | id | jdata | role | name | +----+-------------------------------------------------------------+--------------+-------+ | 1 | {"town": "Chester", "county": "Cheshire", "country": 1} | [1, 2, 3] | Peter | | 2 | {"town": "Tenby", "county": "Pembrokeshire", "country": 3} | [1, 3] | Paul | | 3 | {"town": "Lancaster", "county": "Lancashire", "country": 1} | [1, 2, 4] | Mary | | 4 | {"town": "Dorchester", "county": "Dorset", "country": 1} | [2, 4] | Jane | | 5 | {"town": "Caernarfon", "county": "Cardigan", "country": 3} | [1, 2, 3, 4] | Fred | +----+-------------------------------------------------------------+--------------+-------+ There's also a conventional country table +------------+--------------+ | country_id | country_name | +------------+--------------+ | 1 | England | | 2 | Scotland | | 3 | Wales | | 4 | Ireland | | 5 | France | | 6 | Italy | +------------+--------------+ TEST SQL QUERIES Alternative versions of same query (JSON_UNQUOTE vs double-arrow). Join on json array element. SELECT JSON_UNQUOTE(jdata->'$.town') as Town , JSON_UNQUOTE(jdata->'$.county') as County , country_name as Country FROM json_test j JOIN country c ON c.country_id = j.jdata->'$.country' ORDER BY jdata->'$.country'; SELECT jdata->>'$.town' as Town , jdata->>'$.county' as County , country_name as Country FROM json_test j JOIN country c ON c.country_id = j.jdata->'$.country' ORDER BY jdata->'$.country'; Search on json data SELECT product_id , attributes->>'$.Color' as color , attributes->>'$.Style' as size FROM products.product_j WHERE attributes->>'$.Size' = 'L' AND category_id = 3; Update json data UPDATE product_j SET attributes = JSON_SET(attributes, "$.Style", "Turtleneck") WHERE product_id = 8; I hope this gives a flavour of using json data. I wouldn't recommend using it all the time, as it breaks normalization rules, but it has its uses. 1 Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted February 28 Author Share Posted February 28 18 hours ago, Barand said: You can. The trick is use a code block for consistent charater widths. EG... +------------+-------------+--------+-------+ | Field Name | Field Label | Width | Order | +------------+-------------+--------+-------+ |First Name | Given Name | 6 | 1 | |Last Name | Surname | 6 | 2 | +------------+-------------+--------+-------+ I've used json columns in the past. I have found it a good alternative to the EAV data model (PITA). For example, in my sample data below there is a product table with 3 categories of product. Each category has different attribute: Membership - type, duration Book - author, title T-shirt = size, colour, style In a conventional relational table each attribute would require its own column and most of them would be empty. The json version uses a single column. The other table was for testing arrays and accessing and joining on array elements. You would store the metadata in your database to define which attributes each category should have. +--------+-----------+-----------------------------+ | cat_id | cat_name | attributes | +--------+-----------+-----------------------------+ | 1 |Membership | ["Type", "Duration"] | | 2 |Book | ["Title", "Author"] | | 3 |T-shirt | ["Size", "Colour", "Style"] | +--------+-----------+-----------------------------+ TEST DATA CREATE TABLE `product_j` ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `prod_name` varchar(45) DEFAULT NULL, `category_id` int(11) DEFAULT NULL, `attributes` json DEFAULT NULL, PRIMARY KEY (`product_id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; INSERT INTO `product_j` VALUES (1,'Standard Membership (12 months)',1,'{\"Type\": \"S\", \"Duration\": 12}'),(2,'Premium Membership (3 months)',1,'{\"Type\": \"P\", \"Duration\": 3}'),(3,'Brave New World',2,'{\"Title\": \"Brave New World\", \"Author\": \"Aldus Huxley\"}'),(4,'Philosophers Stone',2,'{\"Title\": \"Harry Potter and the Philosophers Stone\", \"Author\": \"JK Rowling\"}'),(5,'T-Shirt 1',3,'{\"Size\": \"M\", \"Color\": \"Red\", \"Style\": \"V-neck\"}'),(6,'T-Shirt 2',3,'{\"Size\": \"L\", \"Color\": \"White\", \"Style\": \"V-neck\"}'),(7,'T-Shirt 3',3,'{\"Size\": \"L\", \"Color\": \"Red\", \"Style\": \"V-neck\"}'),(8,'T-Shirt 4',3,'{\"Size\": \"L\", \"Color\": \"Black\", \"Style\": \"crew-neck\"}'),(9,'Goblet of Fire',2,'{\"Title\": \"Harry Potter and the Goblet of Fire\", \"Author\": \"JK Rowling\"}'); +------------+---------------------------------+-------------+------------------------------------------------------------------------------+ | product_id | prod_name | category_id | attributes | +------------+---------------------------------+-------------+------------------------------------------------------------------------------+ | 1 | Standard Membership (12 months) | 1 | {"Type": "S", "Duration": 12} | | 2 | Premium Membership (3 months) | 1 | {"Type": "P", "Duration": 3} | | 3 | Brave New World | 2 | {"Title": "Brave New World", "Author": "Aldus Huxley"} | | 4 | Philosophers Stone | 2 | {"Title": "Harry Potter and the Philosophers Stone", "Author": "JK Rowling"} | | 5 | T-Shirt 1 | 3 | {"Size": "M", "Color": "Red", "Style": "V-neck"} | | 6 | T-Shirt 2 | 3 | {"Size": "L", "Color": "White", "Style": "V-neck"} | | 7 | T-Shirt 3 | 3 | {"Size": "L", "Color": "Red", "Style": "V-neck"} | | 8 | T-Shirt 4 | 3 | {"Size": "L", "Color": "Black", "Style": "crew-neck"} | | 9 | Goblet of Fire | 2 | {"Title": "Harry Potter and the Goblet of Fire", "Author": "JK Rowling"} | +------------+---------------------------------+-------------+------------------------------------------------------------------------------+ CREATE TABLE `json_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `jdata` json DEFAULT NULL, `role` json DEFAULT NULL, `name` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; INSERT INTO `json_test` VALUES (1,'{\"town\": \"Chester\", \"county\": \"Cheshire\", \"country\": 1}','[1, 2, 3]','Peter'),(2,'{\"town\": \"Tenby\", \"county\": \"Pembrokeshire\", \"country\": 3}','[1, 3]','Paul'),(3,'{\"town\": \"Lancaster\", \"county\": \"Lancashire\", \"country\": 1}','[1, 2, 4]','Mary'),(4,'{\"town\": \"Dorchester\", \"county\": \"Dorset\", \"country\": 1}','[2, 4]','Jane'),(5,'{\"town\": \"Caernarfon\", \"county\": \"Cardigan\", \"country\": 3}','[1, 2, 3, 4]','Fred'); +----+-------------------------------------------------------------+--------------+-------+ | id | jdata | role | name | +----+-------------------------------------------------------------+--------------+-------+ | 1 | {"town": "Chester", "county": "Cheshire", "country": 1} | [1, 2, 3] | Peter | | 2 | {"town": "Tenby", "county": "Pembrokeshire", "country": 3} | [1, 3] | Paul | | 3 | {"town": "Lancaster", "county": "Lancashire", "country": 1} | [1, 2, 4] | Mary | | 4 | {"town": "Dorchester", "county": "Dorset", "country": 1} | [2, 4] | Jane | | 5 | {"town": "Caernarfon", "county": "Cardigan", "country": 3} | [1, 2, 3, 4] | Fred | +----+-------------------------------------------------------------+--------------+-------+ There's also a conventional country table +------------+--------------+ | country_id | country_name | +------------+--------------+ | 1 | England | | 2 | Scotland | | 3 | Wales | | 4 | Ireland | | 5 | France | | 6 | Italy | +------------+--------------+ TEST SQL QUERIES Alternative versions of same query (JSON_UNQUOTE vs double-arrow). Join on json array element. SELECT JSON_UNQUOTE(jdata->'$.town') as Town , JSON_UNQUOTE(jdata->'$.county') as County , country_name as Country FROM json_test j JOIN country c ON c.country_id = j.jdata->'$.country' ORDER BY jdata->'$.country'; SELECT jdata->>'$.town' as Town , jdata->>'$.county' as County , country_name as Country FROM json_test j JOIN country c ON c.country_id = j.jdata->'$.country' ORDER BY jdata->'$.country'; Search on json data SELECT product_id , attributes->>'$.Color' as color , attributes->>'$.Style' as size FROM products.product_j WHERE attributes->>'$.Size' = 'L' AND category_id = 3; Update json data UPDATE product_j SET attributes = JSON_SET(attributes, "$.Style", "Turtleneck") WHERE product_id = 8; I hope this gives a flavour of using json data. I wouldn't recommend using it all the time, as it breaks normalization rules, but it has its uses. Thanks so much for this - i think it answers a lot of questions that i would have no doubt have been asking later. I feel like it will be a good use for JSON as i dont know how many entries there will be or what they will be. I will give it a go! Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted February 29 Author Share Posted February 29 OK, so i have got data going in which is great. I am trying to get the key in the array as well as the value (it should be fieldId) - currently when submitting 2 objects with id 1 and 2 i am getting ["1","2"] The function is below if (!$_POST['formId']) { $sql2 = "INSERT INTO form (name, description, event_site_attachment_id, field_selection) VALUES (:name, :description, :esId, :fieldSelection)"; $stmt2 = $pdo->prepare($sql2); foreach ($_POST['field'] as $k => $v) { $fieldSelection[$k] = $v; } $stmt2->execute([ ':name' => $_POST['formName'], ':description' => $_POST['formDescription'], ':esId' => $_GET['esId'], ':fieldSelection' => json_encode($fieldSelection) ]); $out = $pdo->LastInsertId(); return $out; } Quote Link to comment Share on other sites More sharing options...
Barand Posted February 29 Share Posted February 29 30 minutes ago, Adamhumbug said: i am getting ["1","2"] What do you expect to be getting? What's in $_POST['field'] ? Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted March 1 Author Share Posted March 1 8 minutes ago, Barand said: What do you expect to be getting? What's in $_POST['field'] ? This is what is posted. array(9) { ["formName"] => string(3) "asa" ["formId"] => string(0) "" ["formDescription"] => string(5) "asasa" ["field"] => array(2) { [0] => string(1) "1" [1] => string(1) "2" } ["fieldName"] => array(2) { [0] => string(11) "Custom 1111" [1] => string(8) "Custom 2" } ["fieldLabel"] => array(2) { [0] => string(8) "Custom 1" [1] => string(8) "Custom 2" } ["fieldType"] => array(2) { [0] => string(1) "1" [1] => string(1) "2" } ["fieldWidth"] => array(2) { [0] => string(1) "6" [1] => string(1) "6" } ["ajax"] => string(23) "saveFormAndCustomFields" } Quote Link to comment Share on other sites More sharing options...
Barand Posted March 1 Share Posted March 1 The keys were 0, 1. This what you have in your table. Examples... $sql2 = "INSERT INTO test_1 (field_selection) VALUES (:fids)"; $stmt2 = $pdo->prepare($sql2); $fieldIds = [0=>1, 1=>2]; $stmt2->execute([ ':fids' => json_encode($fieldIds) ]); $fieldIds = [42=>1, 57=>2]; $stmt2->execute([ ':fids' => json_encode($fieldIds) ]); // GIVES: // +----+---------------------+--------------------+ // | id | created | field_selection | // +----+---------------------+--------------------+ // | 1 | 2024-03-01 00:09:04 | [1, 2] | // | 2 | 2024-03-01 00:09:04 | {"42": 1, "57": 2} | // +----+---------------------+--------------------+ Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted March 1 Share Posted March 1 if you name the sets of fields differently, with the same root name, an incrementing numerical index, then the element name, the submitted data will already be in a format that you can json_encode(). assuming these are all text fields, the markup would look like - <input type='text' name='fieldSelection[0][field]'> <input type='text' name='fieldSelection[0][fieldName]'> <input type='text' name='fieldSelection[0][fieldLabel]'> <input type='text' name='fieldSelection[0][fieldType]'> <input type='text' name='fieldSelection[0][fieldWidth]'> <input type='text' name='fieldSelection[1][field]'> <input type='text' name='fieldSelection[1][fieldName]'> <input type='text' name='fieldSelection[1][fieldLabel]'> <input type='text' name='fieldSelection[1][fieldType]'> <input type='text' name='fieldSelection[1][fieldWidth]'> you can then simply loop over $_POST['fieldSelection'] and use each set of values - foreach($_POST['fieldSelection'] as $row) { echo '<pre>'; print_r($row); echo '</pre>'; echo json_encode($row); echo '<br>'; } Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted March 2 Author Share Posted March 2 On 3/1/2024 at 6:05 AM, mac_gyver said: if you name the sets of fields differently, with the same root name, an incrementing numerical index, then the element name, the submitted data will already be in a format that you can json_encode(). assuming these are all text fields, the markup would look like - <input type='text' name='fieldSelection[0][field]'> <input type='text' name='fieldSelection[0][fieldName]'> <input type='text' name='fieldSelection[0][fieldLabel]'> <input type='text' name='fieldSelection[0][fieldType]'> <input type='text' name='fieldSelection[0][fieldWidth]'> <input type='text' name='fieldSelection[1][field]'> <input type='text' name='fieldSelection[1][fieldName]'> <input type='text' name='fieldSelection[1][fieldLabel]'> <input type='text' name='fieldSelection[1][fieldType]'> <input type='text' name='fieldSelection[1][fieldWidth]'> you can then simply loop over $_POST['fieldSelection'] and use each set of values - foreach($_POST['fieldSelection'] as $row) { echo '<pre>'; print_r($row); echo '</pre>'; echo json_encode($row); echo '<br>'; } Hi All, Sorry but i am really struggling with this...damn arrays. I dont think this option is going to work for me as the user has the ability to add as many fields as they want and move them around in order - the order is important so i would have to be constantly updating field names based on the order. The method which i am using now posts them in order negating this step. What is being posted is: array(9) { ["formName"]=> string(1) "q" ["formId"]=> string(0) "" ["formDescription"]=> string(1) "q" ["field"]=> array(2) { [0]=> string(1) "1" [1]=> string(1) "2" } ["fieldName"]=> array(2) { [0]=> string(11) "Custom 1111" [1]=> string(8) "Custom 2" } ["fieldLabel"]=> array(2) { [0]=> string(8) "Custom 1" [1]=> string(8) "Custom 2" } ["fieldType"]=> array(2) { [0]=> string(1) "1" [1]=> string(1) "2" } ["fieldWidth"]=> array(2) { [0]=> string(1) "6" [1]=> string(1) "6" } ["ajax"]=> string(23) "saveFormAndCustomFields" } I am trying to get the ids of the fields into the json column in my database ( to be clear i am able to do this but the keys are the issue). The data that is being posted - for example the below: ["field"]=> array(2) { [0]=> string(1) "1" [1]=> string(1) "2" } using the following code if (!$_POST['formId']) { $sql2 = "INSERT INTO form (name, description, event_site_attachment_id, field_selection) VALUES (:name, :description, :esId, :fieldSelection)"; $stmt2 = $pdo->prepare($sql2); foreach ($_POST['field'] as $k => $v) { $fieldSelection[$k] = $v; } $stmt2->execute([ ':name' => $_POST['formName'], ':description' => $_POST['formDescription'], ':esId' => $_GET['esId'], ':fieldSelection' => json_encode($fieldSelection) ]); $out = $pdo->LastInsertId(); return $out; } is putting the following into the database ["1","2"] This is not unexpected based on the keys and values. What i would like to be in the database is something like the following { "formFields": [ { "id": 1, }, { "id": 2, }, { "id": 3, } ] } I am also posting other values that i will want to be in here at some point such as fieldWidth I think i am clearly misunderstanding the help that i have been given here so i apologise for that. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 2 Share Posted March 2 Store the field sequence number as an attribute and don't rely on its position in the array. (This would apply equally to a conventional database table where you shouldn't rely on id for position). Also, have your sequence numbers initially incrementing by 10s (or, even, 100s). If you increment by 1, changing the sequence is a problem. EG if you start with firstname lastname date_of_birth username password and you then decide you want username to be first, you have to increment 1, 2, and 3 then change 4 to 1 (and run the risk of creating illegal duplicates on the way if you are using array positions or id keys). If you started with 100, 200, 300, .... then moving the username to the top would just be a matter of changing 400 to 50. Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted March 2 Author Share Posted March 2 OK, i think i have got to where i wanted to be - i dont know if i am being efficient at all. @Barand - i will take your advice on the order and increment once i have addressed my immediate problem. I now have the following function: function saveFormAndCustomFields($pdo) { file_put_contents("../post.log", print_r($_POST, true)); //if the form has not been submitted before if (!$_POST['formId']) { $sql2 = "INSERT INTO form (name, description, event_site_attachment_id, field_selection) VALUES (:name, :description, :esId, :fieldSelection)"; $stmt2 = $pdo->prepare($sql2); foreach ($_POST['field'] as $k => $v) { $fieldSelection = ["fieldId" => $v, "fieldName" => $_POST['fieldName'][$k], "fieldLabel" => $_POST['fieldLabel'][$k], "fieldWidth" => $_POST['fieldWidth'][$k]]; } $stmt2->execute([ ':name' => $_POST['formName'], ':description' => $_POST['formDescription'], ':esId' => $_GET['esId'], ':fieldSelection' => json_encode($fieldSelection) ]); $out = $pdo->LastInsertId(); return $out; } //if the form has been submitted before $sql2 = "UPDATE form SET name = :name, description = :description, field_selection = :fieldSection WHERE id = :formId"; $stmt2 = $pdo->prepare($sql2); foreach ($_POST['field'] as $k => $v) { $fieldSelection = ["fieldId" => $v, "fieldName" => $_POST['fieldName'][$k], "fieldLabel" => $_POST['fieldLabel'][$k], "fieldWidth" => $_POST['fieldWidth'][$k]]; } $stmt2->execute([ ':name' => $_POST['formName'], ':description' => $_POST['formDescription'], ':fieldSelection' => json_encode($fieldSelection), ':formId' => $_POST['formId'], ]); return $_POST['formId']; } the insert is working and the data looks like this { "fieldId": "4", "fieldName": "Custom 4", "fieldLabel": "Custom 4", "fieldWidth": "6" } The update, which in this instance i want to just completly replace the json contents is saying Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: The error code is $stmt2->execute([ ':name' => $_POST['formName'], ':description' => $_POST['formDescription'], ':fieldSelection' => json_encode($fieldSelection), ':formId' => $_POST['formId'], ]); i have var dumped post before the update showing me array(9) { ["formName"]=> string(3) "asa" ["formId"]=> string(3) "117" ["formDescription"]=> string(1) "a" ["field"]=> array(2) { [0]=> string(1) "1" [1]=> string(1) "2" } ["fieldName"]=> array(2) { [0]=> string(11) "Custom 1111" [1]=> string(8) "Custom 2" } ["fieldLabel"]=> array(2) { [0]=> string(8) "Custom 1" [1]=> string(8) "Custom 2" } ["fieldType"]=> array(2) { [0]=> string(1) "1" [1]=> string(1) "2" } ["fieldWidth"]=> array(2) { [0]=> string(1) "6" [1]=> string(1) "6" } ["ajax"]=> string(23) "saveFormAndCustomFields" } It looks to me like all of the values are available for this update so i am not sure what i am missing. I am not ignoring your advice above - i know it looks like i am. Apologioes forf that Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted March 2 Share Posted March 2 45 minutes ago, Adamhumbug said: the insert is working and the data looks like this except, that's only the last field definition, not all of them, because you are reassigning $fieldSelection each pass through the loop. you want to add a new array entry to $fieldSelection each pass through the loop. 48 minutes ago, Adamhumbug said: Invalid parameter number: you have a typo/spelling mistake in the :fieldSection place-holder in the sql vs :fieldSelection in the execute() call. Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted March 2 Author Share Posted March 2 1 hour ago, mac_gyver said: except, that's only the last field definition, not all of them, because you are reassigning $fieldSelection each pass through the loop. you want to add a new array entry to $fieldSelection each pass through the loop. you have a typo/spelling mistake in the :fieldSection place-holder in the sql vs :fieldSelection in the execute() call. i tired .= but that didnt work so i went for foreach ($_POST['field'] as $k => $v) { $fieldSelection[$k] = ["fieldId" => $v, "fieldName" => $_POST['fieldName'][$k], "fieldLabel" => $_POST['fieldLabel'][$k], "fieldWidth" => $_POST['fieldWidth'][$k]]; } but i dont feel like this is the "right" way of doing it Thank you for finding the typo - been staring at this code for a long time now. Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted March 3 Author Share Posted March 3 I also discovered that i was using mariaDB which i have now changes to MYSQL as some of the JSON commends are different Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.