Adamhumbug Posted March 19 Share Posted March 19 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? Quote Link to comment https://forums.phpfreaks.com/topic/319167-mariadb-json-joining-tables/ Share on other sites More sharing options...
Adamhumbug Posted March 19 Author Share Posted March 19 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; } Quote Link to comment https://forums.phpfreaks.com/topic/319167-mariadb-json-joining-tables/#findComment-1618229 Share on other sites More sharing options...
Barand Posted March 19 Share Posted March 19 Sort each row before you create the divs usort($row, fn(a,b)=>$a['order']<=>$b['order']); Quote Link to comment https://forums.phpfreaks.com/topic/319167-mariadb-json-joining-tables/#findComment-1618234 Share on other sites More sharing options...
Barand Posted March 19 Share Posted March 19 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']]; 1 Quote Link to comment https://forums.phpfreaks.com/topic/319167-mariadb-json-joining-tables/#findComment-1618237 Share on other sites More sharing options...
Adamhumbug Posted March 19 Author Share Posted March 19 6 minutes ago, Barand said: Sort each row before you create the divs usort($row, fn(a,b)=>$a['order']<=>$b['order']); Thanks for this - this is new to me - is this part of the SQL? Quote Link to comment https://forums.phpfreaks.com/topic/319167-mariadb-json-joining-tables/#findComment-1618239 Share on other sites More sharing options...
Barand Posted March 19 Share Posted March 19 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 Quote Link to comment https://forums.phpfreaks.com/topic/319167-mariadb-json-joining-tables/#findComment-1618241 Share on other sites More sharing options...
Adamhumbug Posted March 19 Author Share Posted March 19 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. Quote Link to comment https://forums.phpfreaks.com/topic/319167-mariadb-json-joining-tables/#findComment-1618242 Share on other sites More sharing options...
Adamhumbug Posted March 19 Author Share Posted March 19 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 Quote Link to comment https://forums.phpfreaks.com/topic/319167-mariadb-json-joining-tables/#findComment-1618243 Share on other sites More sharing options...
Barand Posted March 19 Share Posted March 19 Sorrry - should be ($a, $b). (Too much javascript recently) Quote Link to comment https://forums.phpfreaks.com/topic/319167-mariadb-json-joining-tables/#findComment-1618244 Share on other sites More sharing options...
Barand Posted March 19 Share Posted March 19 7 minutes ago, Adamhumbug said: I am getting unfeined array key for the icons. probably because of this... It's better to use the same column names. 1 Quote Link to comment https://forums.phpfreaks.com/topic/319167-mariadb-json-joining-tables/#findComment-1618245 Share on other sites More sharing options...
Adamhumbug Posted March 19 Author Share Posted March 19 (edited) 25 minutes ago, Barand said: probably because of this... 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 March 19 by Adamhumbug Quote Link to comment https://forums.phpfreaks.com/topic/319167-mariadb-json-joining-tables/#findComment-1618248 Share on other sites More sharing options...
Adamhumbug Posted March 19 Author Share Posted March 19 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 Quote Link to comment https://forums.phpfreaks.com/topic/319167-mariadb-json-joining-tables/#findComment-1618249 Share on other sites More sharing options...
Adamhumbug Posted March 19 Author Share Posted March 19 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 Quote Link to comment https://forums.phpfreaks.com/topic/319167-mariadb-json-joining-tables/#findComment-1618250 Share on other sites More sharing options...
Adamhumbug Posted March 19 Author Share Posted March 19 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" } } } } Quote Link to comment https://forums.phpfreaks.com/topic/319167-mariadb-json-joining-tables/#findComment-1618251 Share on other sites More sharing options...
Solution Adamhumbug Posted March 19 Author Solution Share Posted March 19 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; } Quote Link to comment https://forums.phpfreaks.com/topic/319167-mariadb-json-joining-tables/#findComment-1618252 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.