Jump to content

MariaDb JSON Joining Tables

Adamhumbug

By Adamhumbug
in MySQL Help

 Share
Go to solution Solved by Adamhumbug,

Recommended Posts

Adamhumbug Prolific Member

Adamhumbug

Posted
Posted

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?

Link to comment
Share on other sites
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted

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

 

Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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']];

 

  • Great Answer 1
Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted
2 minutes ago, Adamhumbug said:

is this part of the SQL?

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

Link to comment
Share on other sites
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
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.

Link to comment
Share on other sites
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
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

Link to comment
Share on other sites
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted (edited)
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>";
		}

 

Edited by Adamhumbug
Link to comment
Share on other sites
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
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

 

Link to comment
Share on other sites
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted

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

 

Link to comment
Share on other sites
  • Solution
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
  • Solution
Posted

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

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • 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.