Adamhumbug

When i put this here: foreach ($cards as $card) { usort($row, fn ($a, $b) => $a['Order'] <=> $b['Order']); $icon_code = $icons[$card['Icon']]; $out .= "<div class='$size mb-3'> <div class='card editHeroCard' data-order-number='$card[Order]'> <div class='card-body'> <div class='text-center card-title'> <i class='$icon_code 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>"; } i get the error: Uncaught TypeError: usort(): Argument #1 ($array) must be of type array, string given

ahh, turning it around worked $res = $pdo->query("SELECT code, id FROM icon"); $icons = array_column($res->fetchAll(), 'code', 'id'); This is whitchcraft to me - so much to learn

ok, have updated this to match the table $res = $pdo->query("SELECT id, code FROM icon"); $icons = array_column($res->fetchAll(), 'id', 'code'); but i am still getting undefined array key here foreach ($cards as $card) { $icon_code = $icons[$card['Icon']]; $out .= "<div class='$size mb-3'> <div class='card editHeroCard' data-order-number='$card[Order]'> <div class='card-body'> <div class='text-center card-title'> <i class='$icon_code 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>"; }

When i put it here: 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"; } ------------- usort($cards, fn(a,b)=>$a['order']<=>$b['order']); foreach ($cards as $card) { $icon_code = $icons[$card['Icon']]; $out .= "<div class='$size mb-3'> <div class='card editHeroCard' data-order-number='$card[Order]'> <div class='card-body'> <div class='text-center card-title'> <i class='$icon_code 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>"; i get unexpected comma after fn(a

I updated to this: function getHeroCardsByPortalId($pdo) { $res = $pdo->query("SELECT id, code FROM icon"); $icons = array_column($res->fetchAll(), 'icon_code', 'icon_id'); $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) { $icon_code = $icons[$card['Icon']]; $out .= "<div class='$size mb-3'> <div class='card editHeroCard' data-order-number='$card[Order]'> <div class='card-body'> <div class='text-center card-title'> <i class='$icon_code 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; } I am getting unfeined array key for the icons.

Thanks for this - this is new to me - is this part of the SQL?

I would also be interested in how i would order the results. I have updated the JSON to include an order which can be seen here: { "Portal Content": { "Colours": { "Primary": "f85a40", "Secondary": "008374", "Default": "222222" }, "Pages": { "Home": { "Hero": { "Hero On": 1, "Title": "Welcome to the Accreditation Portal", "Paragraph": "This is the home page of the Accreditation Portal.", "Hero Cards": { "Card 1": { "Name": "Card 1", "Paragraph": "This is the first card", "Icon": "1", "Order": 1 }, "Card 2": { "Name": "Card 2", "Paragraph": "This is the second card", "Icon": "2", "Order": 2 }, "Card 3": { "Name": "Card 3", "Paragraph": "This is the third card", "Icon": "3", "Order": 3 }, "Card 4": { "Name": "Card 4", "Paragraph": "This is the fourth card", "Icon": "4", "Order": 4 } } } }, "About": { "Text": "This is the about page" } } } } When i run my foreach it would be great if i could do this in order from 1 to 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'] ]); $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; }

Hi All, I have a query that fetches Json data from a database. SELECT json_extract(portal_content_json, '$.\"Portal Content\".\"Pages\".\"Home\".\"Hero\".\"Hero Cards\"') as cards FROM portal_content where portal_attachment_id = :portalId The outputted data 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"}} I am needing to join the Icon field (which holds the icon ID) to another table to get the icon_code. I cant see how this is going to be possible with me pulling (in this example) 4 sets of data in one. Is there a way that i can do a join to pull the icon_code for each of these pieces of data?

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

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.

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();

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?

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