Storing JSON in MySQL database

Adamhumbug · 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.

Adamhumbug · 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

Barand · 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';

image.png.0a81175760daa82b5edc6b43e65b79b5.png

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;

image.png.704e98a7cf9cb73380e763c1d3cc2a04.png

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.

Adamhumbug · 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!

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

Barand · February 29

30 minutes ago, Adamhumbug said:

i am getting

["1","2"]

What do you expect to be getting?

What's in $_POST['field'] ?

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

Barand · 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} |
//    +----+---------------------+--------------------+

mac_gyver · 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>';
}

Adamhumbug · 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.

Barand · 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.

Adamhumbug · 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

mac_gyver · 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.

Adamhumbug · 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.

Adamhumbug · 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

Sign In

Storing JSON in MySQL database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information