Adamhumbug

March 19

9 hours ago, Barand said:

or

UPDATE portal_content 
SET portal_content_json = JSON_SET(portal_content_json, 
                                  '$."Portal Content"."Colours"."primary"', 'limegreen', 
                                  '$."Portal Content"."Colours"."secondary"', 'hotpink'), 
                                  '$."Portal Content"."Colours"."tertiary"', 'orange') 
WHERE `portal_attachment_id` = 25 ;

When all else fails...

https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html

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.

March 18

2 minutes ago, Barand said:

Yes, JSON is picky when it comes to single and double quotes preferring "" inside the JSON.

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?

March 18

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?

March 18

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

March 18

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.

March 18

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?

March 6

4 minutes ago, Barand said:

When?

About a week ago 🙂

March 6

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

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

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.

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

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.

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

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

February 28

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')
			}
		})


	}

February 28

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?

February 28

18 hours ago, mac_gyver said:
you also need to repopulate the value (selected options, checked checkbox/radio fields) in the case of adding/creating/inserting new data when there are user/validation errors, so that the user doesn't need to keep reentering data over and over. you also need to apply htmlentities() to the value to help prevent cross site scripting.

if you switch from echoing mostly static html to just echoing the dynamic value, you would end up with something that looks like this -
<div class='col-12'>
  <label>Form Name
  <input type='text' class='form-control' name='formName' value='<?=htmlentities($data['formName']??'',ENT_QUOTES)?>'></label>
</div>
also, stop copying variables to other variables (just use the original variable that data is in) and you can eliminate the for='' and corresponding id='' attributes if you put the closing </label> tag after the field it corresponds with.

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?

February 28

17 hours ago, mac_gyver said:
you also need to repopulate the value (selected options, checked checkbox/radio fields) in the case of adding/creating/inserting new data when there are user/validation errors, so that the user doesn't need to keep reentering data over and over. you also need to apply htmlentities() to the value to help prevent cross site scripting.

if you switch from echoing mostly static html to just echoing the dynamic value, you would end up with something that looks like this -
<div class='col-12'>
  <label>Form Name
  <input type='text' class='form-control' name='formName' value='<?=htmlentities($data['formName']??'',ENT_QUOTES)?>'></label>
</div>
also, stop copying variables to other variables (just use the original variable that data is in) and you can eliminate the for='' and corresponding id='' attributes if you put the closing </label> tag after the field it corresponds with.

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.

February 28

18 hours ago, Barand said:

Do you have php v8?

Yes 8.2

February 28

18 hours ago, Barand said:

Now you provide it!

When I think of the time I've spent building these...

+--------+-----------+-----------------------------+
| cat_id | cat_name  |   attributes                |
+--------+-----------+-----------------------------+
|    1   |Membership | ["Type", "Duration"]        |
|    2   |Book       | ["Title", "Author"]         |
|    3   |T-shirt    | ["Size", "Colour", "Style"] |
+--------+-----------+-----------------------------+

I always thought you were generating these from somewhere else!

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!

February 28

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?

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

February 28

6 minutes ago, requinix said:

How about this?
It's the table button next to the bullet and numbered list buttons.

This	Will
Be	Really
Handy	THANKS!!!!!

February 27

Hi, Would it be possible to have the ability (or instructions if it exists) to make a table in the editor.

Often when trying to explain hypotetical table layouts it would be handy to be able to lay this out in table format.

I love this forum and the people on it but this has troubled me for some time.

Kind Regards

Sign In

Adamhumbug

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by Adamhumbug

Maria DB JSON field insert/update

Maria DB JSON field insert/update

Maria DB JSON field insert/update

Maria DB JSON field insert/update

Maria DB JSON field insert/update

Maria DB JSON field insert/update

Maria DB JSON field insert/update

Maria DB JSON field insert/update

Storing JSON in MySQL database

Storing JSON in MySQL database

Storing JSON in MySQL database

Storing JSON in MySQL database

Storing JSON in MySQL database

Storing JSON in MySQL database

Modal Launching Without Content

Modal Launching Without Content

Preventing Undefined variable Notice

Preventing Undefined variable Notice

Preventing Undefined variable Notice

Tables in posts

Storing JSON in MySQL database

Preventing Undefined variable Notice

Storing JSON in MySQL database

Tables in posts

Tables in posts

Browse

Activity

Important Information