Jump to content

Adamhumbug

Members
  • Posts

    581
  • Joined

  • Last visited

Posts 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. On 3/20/2024 at 4:39 AM, Danishhafeez said:

    If you want to select a specific card based on its order number in the JSON structure, you can achieve that by iterating over the cards until you find the one with the desired order number.

    function getHeroCardByOrderNumber($pdo, $orderNumber) {
        $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 null; // or handle the case where no data is found
        }
    
        $cards = json_decode($data['cards'], true);
    
        // Find the card with the given order number
        foreach ($cards as $card) {
            if ($card['Order'] == $orderNumber) {
                return $card;
            }
        }
    
        return null; // or handle the case where the card with the given order number is not found
    }

    You can then call this function to get the card with the specified order number:

    $orderNumber = 4; // Example order number
    $card = getHeroCardByOrderNumber($pdo, $orderNumber);
    if ($card) {
        // Display or use the card data
        echo "Card Name: " . $card['Name'];
        echo "Card Paragraph: " . $card['Paragraph'];
    } else {
        echo "Card not found.";
    }

    This modified function getHeroCardByOrderNumber will return the card data if found, or null if not found.

    Best regard

    Danish Hafeez | QA Assistant

    ICTInnovations

    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. 16 hours ago, Danishhafeez said:

    If you want to select a specific card based on its order number in the JSON structure, you can achieve that by iterating over the cards until you find the one with the desired order number.

    function getHeroCardByOrderNumber($pdo, $orderNumber) {
        $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 null; // or handle the case where no data is found
        }
    
        $cards = json_decode($data['cards'], true);
    
        // Find the card with the given order number
        foreach ($cards as $card) {
            if ($card['Order'] == $orderNumber) {
                return $card;
            }
        }
    
        return null; // or handle the case where the card with the given order number is not found
    }

    You can then call this function to get the card with the specified order number:

    $orderNumber = 4; // Example order number
    $card = getHeroCardByOrderNumber($pdo, $orderNumber);
    if ($card) {
        // Display or use the card data
        echo "Card Name: " . $card['Name'];
        echo "Card Paragraph: " . $card['Paragraph'];
    } else {
        echo "Card not found.";
    }

    This modified function getHeroCardByOrderNumber will return the card data if found, or null if not found.

    Best regard

    Danish Hafeez | QA Assistant

    ICTInnovations

    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. 39 minutes ago, Barand said:

    No - usort() is php function for custom sorts of arrays.

    PS It might be $cards (and not $row) that needs sorting. I get lost reading your code when a variable is used as a key value then a couple of lines further on it becomes reused as an array

    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. 25 minutes ago, Barand said:

    probably because of this...

    image.png.509419bccf80650ad2bf8639d5448036.png

    It's better to use the same column names.

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

     

  12. 2 minutes ago, Barand said:

    No - usort() is php function for custom sorts of arrays.

    PS It might be $cards (and not $row) that needs sorting. I get lost reading your code when a variable is used as a key value then a couple of lines further on it becomes reused as an array

    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

  13. 9 minutes ago, Barand said:

    As for your problem with joins on icon_ids, I wouldn't bother.

    Create an array of icons from the icon table

    $res = $pdo->query("SELECT icon_id, icon_code FROM icon");
    $icons = array_column($res->fetchAll(), 'icon_code', 'icon_id');

    then use the $card['Icon'] to access key to this array when outputting

    $icon_code = $icons[$card['Icon']];

     

    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.

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

     

  15. 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?

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

     

  17. 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.

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

  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?

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

     

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

     

  22. 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.

  23. 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?

×
×
  • 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.