Adamhumbug

Escaping seemed to work here function savePortalOptions($pdo, $defaultColour, $primaryColour, $secondaryColour) { $portalId = $_GET['portalId']; $sql = "UPDATE portal_content SET portal_content_json = JSON_SET(portal_content_json, '$.\"Portal Content\".\"Colours\".\"Primary\"', :primaryColour, '$.\"Portal Content\".\"Colours\".\"Secondary\"', :secondaryColour, '$.\"Portal Content\".\"Colours\".\"Default\"', :defaultColour) WHERE portal_attachment_id = :portalId"; $stmt = $pdo->prepare($sql); $stmt->execute([ ':defaultColour' => $defaultColour, ':primaryColour' => $primaryColour, ':secondaryColour' => $secondaryColour, ':portalId' => $portalId ]); return "Portal Options Updated"; }

This is my current function for reference. function savePortalOptions($pdo, $defaultColour, $primaryColour, $secondaryColour) { $portalId = $_GET['portalId']; $sql = "UPDATE portal_content SET portal_content_json = JSON_SET(portal_content_json, '$."Portal Content"."Colours"."Primary"', :primaryColour, '$."Portal Content"."Colours"."Secondary"', :secondaryColour, '$."Portal Content"."Colours"."Default"', :defaultColour,) WHERE portal_attachment_id = :portalId"; $stmt = $pdo->prepare($sql); $stmt->execute([ ':defaultColour' => $defaultColour, ':primaryColour' => $primaryColour, ':secondaryColour' => $secondaryColour, ':portalId' => $portalId ]); return "Portal Options Updated"; }

So this works great putting it directly into phpmyadmin but when trying to put this into php as a prepared statement and put the variables in, i run into an issue with the quotes used. I have tried combinations of single, double and backticks but cannot get this to run from php.

Is that going to be an issue in the php $sql ="UPDATE portal_content SET portal_content_json = JSON_SET(portal_content_json, "$.'Portal Content'.'Colours'.'Primary'", 'Green') where `portal_attachment_id` = 25"; $stmt = $pdo->prepare($sql); im not going to be able to have the double and single quotes inside the sql in php right?

So now that i have this working, how would i go about updating multiple parts of the JSON in one go. So as an example if i have the primary, secondary and tertiary colours to update in one query?

UPDATE portal_content SET portal_content_json = JSON_SET(portal_content_json, '$."Portal Content"."Colours"."Primary"', 'Green') where `portal_attachment_id` = 25 This worked in the end - typo my side

I amended the original JSON to be {'Portal Content':{'Colours':{'Primary':'String','Secondary':'String','Tertiary':'String','Quaternary':'String'}}} Removed the start and end ". Now when i run the query, it is just blanking the column.

I am having some trouble with this. I have a column called portal_content_json with the content "{'Portal Content':{'Colours':{'Primary':'String','Secondary':'String','Tertiary':'String','Quaternary':'String'}}}" and i am trying to update the primary colour through phpMyAdmin with the following: UPDATE portal_content SET portal_content_json = JSON_SET(portal_content_json, '$.PortalContent.Colours.Primary', 'Green') where `portal_attachment_id` = 25 I am getting no errors but nothing is happening. Is there something obvious that is wrong here?

About a week ago 🙂

Hi All, I have asked a similar question to this which i will link below. The reason for the new post is due to me using mariaDB. I have a form with the following fileds Name, Icon, Link , Paragraph, order. I am wanting to store these in a JSON field in a MariaDB. The function that i am using that is puttnig data in the database is: function addHeroCard($pdo, $cardName, $iconSelection, $linkLocation, $paragraph, $order) { $portalId = $_GET['portalId']; $sql = "UPDATE portal_content SET hero_cards = :hero_cards WHERE portal_attachment_id = :portalId"; $stmt = $pdo->prepare($sql); $hero_cards["card"][$order] = [ 'cardName' => $cardName, 'iconSelection' => $iconSelection, 'linkLocation' => $linkLocation, 'paragraph' => $paragraph ]; $hero_cards = json_encode($hero_cards); $stmt->execute([ ':hero_cards' => $hero_cards, ':portalId' => $portalId ]); return "Hero Card Added"; } As the order value will only be 1,2,3,4 i want to know how i either insert new date or update existing data depending on the order value. If order 1 exists it should be overwritten if not it should be added. The data that is being put into the database is currently looking like this. { "card": { "4": { "cardName": "This is a card", "iconSelection": "1", "linkLocation": "5", "paragraph": "This is para" } } }

I also discovered that i was using mariaDB which i have now changes to MYSQL as some of the JSON commends are different

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.

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

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.

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" }

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; }

turn out the show modal needed to be in the success of the ajax - makes perfect sense.. function generateFormModal($formId) { console.log($formId) $.ajax({ type: 'post', data: { 'ajax': 'generateFormModal', 'formId': $formId }, success: function(resp) { $('#createNewFormModal').html(resp) $('#createNewFormModal').modal('show') } }) }

HI All, I have a modal and i want the content to be populated dynamically with info from the database. If the Blank Form button is clicked the same modal is generated but with no content. I though ajax would be the way to do this. $('#openNewFormModalButton, .editFormModalShow').on('click', function() { if (!$(this).data('form-id')) { $formId = null; } else { $formId = $(this).data('form-id'); } generateFormModal($formId); }) function generateFormModal($formId) { console.log($formId) $.ajax({ type: 'post', data: { 'ajax': 'generateFormModal', 'formId': $formId }, success: function(resp) { $('#createNewFormModal').html(resp) } }) $('#createNewFormModal').modal('show') } I have checked the console logs and the correct $formId is being passed, currently either null or 1. I have the container for the content of the modal. <div class='modal modal-xl fade' id='createNewFormModal' tabindex='-1' aria-hidden='true'> </div> I have the switch to call the php function if (isset($_POST['ajax'])) { switch ($_POST['ajax']) { case 'generateFormModal': exit(generateFormModal($pdo, $_POST['formId'])); break; } } and i have the function in php function generateFormModal($pdo, $formId) { if ($formId != null) { $sql = "SELECT name, description from form where id = :id"; $stmt = $pdo->prepare($sql); $stmt->execute([ ':id' => $formId ]); $data = $stmt->fetch(); $formName = $data['name']; } $out = " <div class='modal-dialog'> <div class='modal-content'> <form class='needs-validation' novalidate> <div class='modal-header'> <h5 class='modal-title' id=''>Form Management</h5> <button type='button' class='btn-close' data-bs-dismiss='modal' aria-label='Close'></button> </div> <div class='modal-body'> <form id='form-management'> <div class='row mb-3'> <div class='col-12'> <label for='formName'>Form Name</label> <input id='formName' type='text' class='form-control' name='formName' value='$formName'> </div> </div> <div class='row mb-3'> <div class='col-12'> <label for='formDescription'>Form Description</label> <textarea id='formDescription' class='form-control' name='formDescription'></textarea> </div> </div> <div class='row mb-3'> <div class='col'> <div class='btn btn-primary' id='addNewFieldRow'>Add New Row</div> </div> </div> <div class='row'> <div class='row mb-1'> <div class='col'> <label for='field'>Field</label> </div> <div class='col'> <label for='newName'>Field Name</label> </div> <div class='col'> <label for='fieldLabel'>Field Label</label> </div> <div class='col'> <label for='fieldType'>Field Type</label> </div> <div class='col-2'> <label for='fieldWidth'>Width</label> </div> <div class='col-1 text-center'> <label for='fieldOrder'>Order</label> </div> </div> </div> <div class='row mb-3' id='sortable'> <div class='row mb-1 field-row'> <div class='col'> <select id='field' class='form-select field' Name='field[]'> <option value='0' selected disabled>Please Select</option> <?= getCustomFieldOptions(\$pdo) ?> </select> </div> <div class='col'> <input id='fieldName' type='text' class='form-control' name='fieldName[]'> </div> <div class='col'> <input id='fieldLabel' type='text' class='form-control' name='fieldLabel[]'> </div> <div class='col'> <select id='fieldType' type='text' class='form-select' name='fieldType[]'> <?= getFieldTypes(\$pdo') ?> </select> </div> <div class='col-2'> <input id='fieldWidth' type='number' class='form-control' min='1' max='12' name='fieldWidth[]'> </div> <div class='col-1 text-center handle'> <svg class='icon icon-xl' style='width:2rem;height:2.5rem;'> <use xlink:href='img/svg/free.svg#cil-move'></use> </svg> </div> </div> </div> <div class='row'> <div class='col-10'> <div class='alert alert-success alert-container'></div> </div> <div class='col-2'> <button type='submit' class='btn btn-primary w-100' id='saveForm'>Button</button> </div> </div> </form> </div> <div class='modal-footer'> <button type='button' class='btn btn-secondary' data-bs-dismiss='modal'>Close</button> <div class='btn btn-primary' name='addNewPassType' id='addNewPassType'>Save changes</div> </div> </form> </div> </div> "; return $out; } My issue is that when i run this all together, whether the form id is set to null or 1 the screen greys, showing that the modal is trying to load but there is no other content or actual modal shown. I am also getting a not that helpful JS error of: Uncaught TypeError: 'querySelector' called on an object that does not implement interface Element. When i inspect all of the modal content is there but nothing shown on the screen other than a darkened background. Any pointers for this error?

If i go this way - do i not need a function for each value that just pulls that specific value that is being called. Will that not become a bit overkill if i have a massive form that ends up needing many individual queries to pull each value one by one?

htmlentities() - should i have done this everytime i set a value with php? I have never done this - lots to change if this is the case.

Yes 8.2

I always thought you were generating these from somewhere else!

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!

I have a form that allows a user to populate the fields and add the data to the database. I am wanting to use the same code to edit the same information later. Imagine this code: ... $formName = $data['name']; $resp=" <div class='col-12'> <label for='formName'>Form Name</label> <input id='formName' type='text' class='form-control' name='formName' value='$formName'> </div> "; ... if i remove the $formName from the value the form is created perfectly but in order to populate it when there is already data and i am looking to edit, i need to have the variable in there. I understand that the variable is not set, as in this case there is no data for it to be set to but i have tried things like: $formName = $data['formName'] ?? ""; but i am still getting the undefined message. Is there a proper way to deal with this?

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