Maria DB JSON field insert/update

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

Edited March 6 by Adamhumbug

Barand · March 6

When?

Adamhumbug · March 6

4 minutes ago, Barand said:

When?

About a week ago 🙂

Barand · March 7

Took a fair bit of experimenting to get the right incantations for the update, but...

Data

SELECT * FROM json_test;
+----+-------------------------------------------------------------------------------------------------------------------------------+
| id | jstuff                                                                                                                        |
+----+-------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"card": {"4": {"cardName": "This is a card", "iconSelection": "1", "linkLocation": "5", "paragraph": "This is para"}}}       |
+----+-------------------------------------------------------------------------------------------------------------------------------+

Update

UPDATE json_test 
SET jstuff = JSON_SET(jstuff, '$."card".4."cardName"', 'This is a different card')
WHERE id = 1;

Check

SELECT * FROM json_test;
+----+-----------------------------------------------------------------------------------------------------------------------------------+
| id | jstuff                                                                                                                            |
+----+-----------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"card": {"4": {"cardName": "This is a different card", "iconSelection": "1", "linkLocation": "5", "paragraph": "This is para"}}} |
+----+-----------------------------------------------------------------------------------------------------------------------------------+

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

Edited March 18 by Adamhumbug

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

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

Edited March 18 by Adamhumbug

Barand · March 18

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

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

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

Barand · March 18

This is the update example from my earlier post

UPDATE json_test 
SET jstuff = JSON_SET(jstuff, '$."card".4."cardName"', 'This is a different card')
WHERE id = 1;

Note use of ' and " .

5 minutes ago, Adamhumbug said:

how would i go about updating multiple parts of the JSON in one go

I'll need to experiment further. One way would be to update all colors in one JSON_SET

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

Barand · March 18

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

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.

Adamhumbug · March 19

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

Adamhumbug · March 19

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

Edited March 19 by Adamhumbug

Barand · March 19

Yes, you can't put double quotes inside a double-quoted string without excaping them.

mysql> select * from json_test where id = 2;
+----+-----------------------------------------------------------------------------------------------------------------------+
| id | jstuff                                                                                                                |
+----+-----------------------------------------------------------------------------------------------------------------------+
|  2 | {"Portal Content": {"Colours": {"Primary": "Red", "Secondary": "Green", "Tertiary": "Blue", "Quaternary": "String"}}} |
+----+-----------------------------------------------------------------------------------------------------------------------+

$stmt = $pdo->prepare("UPDATE json_test 
                        SET jstuff = JSON_SET(jstuff, ?, ?, ?, ?, ?, ?, ?, ?)
                        WHERE id = ?
                       ");
$stmt->execute([ '$."Portal Content"."Colours"."Primary"', 
                 'Orange', 
                 '$."Portal Content"."Colours"."Secondary"', 
                 'Limegreen', 
                 '$."Portal Content"."Colours"."Tertiary"', 
                 'Hotpink',  
                 '$."Portal Content"."Colours"."Quaternary"', 
                 'Cornsilk',
                 2
                 ]);

mysql> select * from json_test where id = 2;
+----+-----------------------------------------------------------------------------------------------------------------------------------+
| id | jstuff                                                                                                                            |
+----+-----------------------------------------------------------------------------------------------------------------------------------+
|  2 | {"Portal Content": {"Colours": {"Primary": "Orange", "Secondary": "Limegreen", "Tertiary": "Hotpink", "Quaternary": "Cornsilk"}}} |
+----+-----------------------------------------------------------------------------------------------------------------------------------+

Adamhumbug · March 19

2 hours ago, Barand said:

Yes, you can't put double quotes inside a double-quoted string without excaping them.

mysql> select * from json_test where id = 2;
+----+-----------------------------------------------------------------------------------------------------------------------+
| id | jstuff                                                                                                                |
+----+-----------------------------------------------------------------------------------------------------------------------+
|  2 | {"Portal Content": {"Colours": {"Primary": "Red", "Secondary": "Green", "Tertiary": "Blue", "Quaternary": "String"}}} |
+----+-----------------------------------------------------------------------------------------------------------------------+

$stmt = $pdo->prepare("UPDATE json_test 
                        SET jstuff = JSON_SET(jstuff, ?, ?, ?, ?, ?, ?, ?, ?)
                        WHERE id = ?
                       ");
$stmt->execute([ '$."Portal Content"."Colours"."Primary"', 
                 'Orange', 
                 '$."Portal Content"."Colours"."Secondary"', 
                 'Limegreen', 
                 '$."Portal Content"."Colours"."Tertiary"', 
                 'Hotpink',  
                 '$."Portal Content"."Colours"."Quaternary"', 
                 'Cornsilk',
                 2
                 ]);

mysql> select * from json_test where id = 2;
+----+-----------------------------------------------------------------------------------------------------------------------------------+
| id | jstuff                                                                                                                            |
+----+-----------------------------------------------------------------------------------------------------------------------------------+
|  2 | {"Portal Content": {"Colours": {"Primary": "Orange", "Secondary": "Limegreen", "Tertiary": "Hotpink", "Quaternary": "Cornsilk"}}} |
+----+-----------------------------------------------------------------------------------------------------------------------------------+

Ok, this is really helpful i have been able to put some data in and get some data out so progress is being made and i am thankful to you for that.

My next question, which maybe should be its own thread is that if i am getting data from the database like this:

SELECT
json_extract(portal_content_json, '$."Portal Content"."Pages"."Home"."Hero"."Hero Cards"')
FROM portal_content where portal_attachment_id = 43

and the data that is selected with that query looks like this:

{
  "Card 1": {
    "Name": "Card 1",
    "Paragraph": "This is the first card",
    "Icon": "1"
  },
  "Card 2": {
    "Name": "Card 2",
    "Paragraph": "This is the second card",
    "Icon": "2"
  },
  "Card 3": {
    "Name": "Card 3",
    "Paragraph": "This is the third card",
    "Icon": "3"
  },
  "Card 4": {
    "Name": "Card 4",
    "Paragraph": "This is the fourth card",
    "Icon": "4"
  }
}

how do i then in php loop through each of these cards and build an element to return to the page?

Barand · March 19

To process json data in php, json_decode() it to get an array.

$jsonData = '{
  "Card 1": {
    "Name": "Card 1",
    "Paragraph": "This is the first card",
    "Icon": "1"
  },
  "Card 2": {
    "Name": "Card 2",
    "Paragraph": "This is the second card",
    "Icon": "2"
  },
  "Card 3": {
    "Name": "Card 3",
    "Paragraph": "This is the third card",
    "Icon": "3"
  },
  "Card 4": {
    "Name": "Card 4",
    "Paragraph": "This is the fourth card",
    "Icon": "4"
  }
}';
$data = json_decode($jsonData, 1);
echo '<pre>' . print_r($data, 1) . '</pre>';

Array
(
    [Card 1] => Array
        (
            [Name] => Card 1
            [Paragraph] => This is the first card
            [Icon] => 1
        )

    [Card 2] => Array
        (
            [Name] => Card 2
            [Paragraph] => This is the second card
            [Icon] => 2
        )

    [Card 3] => Array
        (
            [Name] => Card 3
            [Paragraph] => This is the third card
            [Icon] => 3
        )

    [Card 4] => Array
        (
            [Name] => Card 4
            [Paragraph] => This is the fourth card
            [Icon] => 4
        )

)

Adamhumbug · March 19

7 minutes ago, Barand said:

To process json data in php, json_decode() it to get an array.

$jsonData = '{
  "Card 1": {
    "Name": "Card 1",
    "Paragraph": "This is the first card",
    "Icon": "1"
  },
  "Card 2": {
    "Name": "Card 2",
    "Paragraph": "This is the second card",
    "Icon": "2"
  },
  "Card 3": {
    "Name": "Card 3",
    "Paragraph": "This is the third card",
    "Icon": "3"
  },
  "Card 4": {
    "Name": "Card 4",
    "Paragraph": "This is the fourth card",
    "Icon": "4"
  }
}';
$data = json_decode($jsonData, 1);
echo '<pre>' . print_r($data, 1) . '</pre>';

Array
(
    [Card 1] => Array
        (
            [Name] => Card 1
            [Paragraph] => This is the first card
            [Icon] => 1
        )

    [Card 2] => Array
        (
            [Name] => Card 2
            [Paragraph] => This is the second card
            [Icon] => 2
        )

    [Card 3] => Array
        (
            [Name] => Card 3
            [Paragraph] => This is the third card
            [Icon] => 3
        )

    [Card 4] => Array
        (
            [Name] => Card 4
            [Paragraph] => This is the fourth card
            [Icon] => 4
        )

)

i tried that and got the following error

Fatal error: Uncaught TypeError: json_decode(): Argument #1 ($json) must be of type string

I am getting the values from $data = $stmt->fetch();

Barand · March 19

Insuffucient context. You may as well ask how long a piece of string is.

How are you sending the result to the page?
What type of element do you want to build?
Are you planning on building it with php or javascript?

Adamhumbug · March 19

3 minutes ago, Barand said:

Insuffucient context. You may as well ask how long a piece of string is.

How are you sending the result to the page?

What type of element do you want to build?

Are you planning on building it with php or javascript?

Right now, this is part of the function that is getting the data - and the current output showing on the page is:

{"Card 1": {"Name": "Card 1", "Paragraph": "This is the first card", "Icon": "1"}, "Card 2": {"Name": "Card 2", "Paragraph": "This is the second card", "Icon": "2"}, "Card 3": {"Name": "Card 3", "Paragraph": "This is the third card", "Icon": "3"}, "Card 4": {"Name": "Card 4", "Paragraph": "This is the fourth card", "Icon": "4"}}

function getHeroCardsByPortalId($pdo)
{

	$sql = "SELECT 
			json_extract(portal_content_json, '$.\"Portal Content\".\"Pages\".\"Home\".\"Hero\".\"Hero Cards\"') as cards
			FROM portal_content where portal_attachment_id = :portalId";
	$stmt = $pdo->prepare($sql);
	$stmt->execute([
		':portalId' => $_GET['portalId']
	]);
	$count = $stmt->rowCount();
	$data = $stmt->fetch();

	foreach ($data as $cards) {
		print_r($cards);
	}
}

I am going to be turning each card from the json into a card in bottstrap shown on the page.

something like this

	// foreach ($data as $row) {
	// 	$out .= "<div class='$size mb-3'>
	// 				<div class='card editHeroCard' data-card-id='$row[id]'>
	// 					<div class='card-body'>
	// 						<div class='text-center card-title'>
	// 							<i class='$row[iconCode] fa-2xl'></i>
	// 						</div>
	// 						<div class='card-text text-center my-3 fw-bold'>$row[name]</div>
	// 						<div class='card-text text-center'>$row[paragraph]</div>
	// 					</div>
	// 				</div>
	// 				<div class='handle text-center'>GRAB</div>
	// 			</div>";
	// }

This will all be built in php and i plan on returning $out from this function.

Barand · March 19

Looks like you are probably trying to decode the fetched array and the string that you want to json_decode() is in $data['cards'] .

Adamhumbug · March 19

Ok, i think i am making progress - i have got out what i expected.

I dont know if this is the most clean solution - probably not, but it is working as expected.

function getHeroCardsByPortalId($pdo)
{
	$sql = "SELECT 
			json_extract(portal_content_json, '$.\"Portal Content\".\"Pages\".\"Home\".\"Hero\".\"Hero Cards\"') as cards
			FROM portal_content where portal_attachment_id = :portalId";
	$stmt = $pdo->prepare($sql);
	$stmt->execute([
		':portalId' => $_GET['portalId']
	]);

	$data = $stmt->fetch();
	if (!$data) {
		return "<div class='col-12'>No hero cards available!</div>";
	}

	foreach ($data as $cards => $row) {

		$cards = json_decode($row, true);
		$count = sizeof($cards);
		$out = '';
		if ($count == 4) {
			$size = "col-xl-3 col-md-6";
		} else if ($count == 3) {
			$size = "col-xl-4 col-md-4";
		} else if ($count == 2) {
			$size = "col-xl-6 col-md-6";
		} else if ($count == 1) {
			$size = "col-12";
		}
		foreach ($cards as $card) {
			$out .= "<div class='$size mb-3'>
						<div class='card editHeroCard'>
							<div class='card-body'>
								<div class='text-center card-title'>
									<i class='$card[Icon] fa-2xl'></i>
								</div>
								<div class='card-text text-center my-3 fw-bold'>$card[Name]</div>
								<div class='card-text text-center'>$card[Paragraph]</div>
							</div>
						</div>
						<div class='handle text-center'>GRAB</div>
					</div>";
		}
	}
	return $out;
}

Sign In

Maria DB JSON field insert/update

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

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