Jump to content

Adamhumbug

Members
  • Posts

    581
  • Joined

  • Last visited

Everything posted by Adamhumbug

  1. foreach ($data as $cards) { $cards = json_decode($data['cards'], 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 $cardName => $cardDetails) { $icon_code = $icons[$cardDetails['Icon']]; $out .= "<div class='$size mb-3'> <div class='card editHeroCard' data-card='$cardName'> <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'>$cardDetails[Name]</div> <div class='card-text text-center'>$cardDetails[Paragraph]</div> </div> </div> <div class='handle text-center'>GRAB</div> </div>"; }
  2. I have a function that contains the following: foreach ($data as $cards => $row) { print_r($data); the data that is printed is as follows: Array ( [cards] => {"Card 1": {"This": "Something 1", "Paragraph": "This is the first card", "Icon": 562, "Order": 5}, "Card 2": {"This": "Something 2", "Paragraph": "This is the second card", "Icon": "559", "Order": 2}, "Card 3": {"This": "Somethihg 3", "Paragraph": "This is the third card", "Icon": "560", "Order": 3}, "Card 4": {"This": "Something 4", "Paragraph": "This is the fourth card", "Icon": "561", "Order": 4}} ) I want to "get" "Card 1", "Card 2"... How in a loop can i access this part of the array. I have tried print_r $data, $cards and $row and none of them give me what i need. When i try and use [0] i just get the first letter.
  3. I have been looking at documentation and it seems that something like this is the route that i need to take - but i am getting no results in php my admin when running this query. SELECT portal_content_json from portal_content where JSON_SEARCH( portal_content_json, 'all', 2, Null, ' $[*]."Hero Cards[*]"."Order"') IS NOT NULL;
  4. This is great - i can confirm that it did what i needed so thanks for that. Now i just need to workout how to select by order Number in order to update the values when the data is edited.
  5. Hi All, I have a function: function createContentBlock($pdo, $contentBlockType, $textBlockTitle, $textBlockContent) { $portalId = $_GET['portalId']; $json = array( "Name" => $textBlockTitle, "Content" => $textBlockContent ); $sql = "UPDATE portal_content set portal_content_json = JSON_SET( portal_content_json,'$.\"Portal Content\".\"Pages\".\"Home\".\"Content Block\"', :json) WHERE portal_attachment_id = :portalId"; $stmt = $pdo->prepare($sql); $stmt->execute([ ':portalId' => $portalId, ':json' => json_encode($json, JSON_UNESCAPED_SLASHES) ]); return "Content Block Created"; } When the data is going into the json, it looks like this: "Content Block": "{\"Name\":\"asa\",\"Content\":\"<p>asa</p>\"}" }, You will see that it is also getting an additional closing bracket. I dont know why it is going in like this as i have another function that is putting data in in the same way without this issue.
  6. Thanks for this - what about when it comes to updating the information after it has been displayed. Again i wont know the exact path so i would have to find the correct element to update.
  7. Hi, Normally when populating an editable form with data from the database, i would include an id as a data attribute that i can use to generate a modal with the data to be edited. I am using JSON. I know how to find the exact data that i need if i know the path to the JSON but i need to select an element in the JSON that contains something (that would have been the id if normalised). If i have the following data { "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": 562, "Order": 5 }, "Card 2": { "Name": "Card 2", "Paragraph": "This is the second card", "Icon": "559", "Order": 2 }, "Card 3": { "Name": "Card 3", "Paragraph": "This is the third card", "Icon": "560", "Order": 3 }, "Card 4": { "Name": "Card 4", "Paragraph": "This is the fourth card", "Icon": "561", "Order": 4 } } } }, "About": { "Text": "This is the about page" } } } } how would i select card 4 for example, when all i know is its order number. normally this would be a select x from y where id = z i expect that i will need something like SELECT json_extract(portal_content_json, '$.\"Portal Content\".\"Pages\".\"Home\".\"Hero\".\"Hero Cards\"') as that is the path to the cards - but selecting the data for the card that i want from here i do not know how to do. something like JSON_CONTAINS? The other option is to set the data attribute to be "Card 1" - the name of the JSON element rather than the title of the element. I am currenly setting the data attribute in this function, which would need to be changed if using the "other option" function getHeroCardsByPortalId($pdo) { $res = $pdo->query("SELECT code, id FROM icon"); $icons = array_column($res->fetchAll(), 'code', 'id'); //avoiding the need to join the tables $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"; } 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>"; } } return $out; }
  8. moving it above the for each does work - thank you x1000 for this. I dont think i would have ever got to this. This is the complete working function: function getHeroCardsByPortalId($pdo) { $res = $pdo->query("SELECT code, id FROM icon"); $icons = array_column($res->fetchAll(), 'code', 'id'); //avoiding the need to join the tables $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"; } 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>"; } } return $out; }
  9. This foreach ($cards as $card) { usort($cards, 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>"; } doesnt give an error but also doesnt appear to be sorting I set card 1 to have the highest order and it is still showing first { "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": 562, "Order": 5 }, "Card 2": { "Name": "Card 2", "Paragraph": "This is the second card", "Icon": "559", "Order": 2 }, "Card 3": { "Name": "Card 3", "Paragraph": "This is the third card", "Icon": "560", "Order": 3 }, "Card 4": { "Name": "Card 4", "Paragraph": "This is the fourth card", "Icon": "561", "Order": 4 } } } }, "About": { "Text": "This is the about page" } } } }
  10. 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
  11. 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
  12. 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>"; }
  13. 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
  14. 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.
  15. Thanks for this - this is new to me - is this part of the SQL?
  16. 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; }
  17. 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?
  18. 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; }
  19. 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.
  20. 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();
  21. 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?
  22. 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"; }
  23. 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"; }
  24. 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.
  25. 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?
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.