PHP SQL Sorting information into HTML div elements-CodePudding

I am trying to sort SQL results into different divs based on the category they are assigned in the database. There are four categories and it works fine as long as there are videos available for all 4 categories. It will create 1 div for each and assign the videos to the correct div. The issue I'm having is that I'd like to also create the div even if there are no videos available within that category. (Pretty new so the code is probably pretty chunky for what it should be)

PHP code

$stmt = $conn->prepare("SELECT * FROM VIDEOS WHERE categorie=:category ORDER BY categorie ASC, subcategorie ASC");
$stmt->bindParam(':category', $_POST['category']);
$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "<div class='spacer'>";

$testcase = "";
for($x = 0; $x < sizeof($result); $x  ) {
    if($result[$x]["subcategorie"] != $testcase) {
        echo    
            "</div><div class='subcategory_container active' id='" . $result[$x]["subcategorie"] . "'>
            <h3 class='subcategory_title'>". $result[$x]["subcategorie"] . "</h3>";
        echo 
            "<div class='video_div'> <iframe width='196' height='350'
            src='" . $result[$x]['linkembed'] . "'>
            </iframe></div>"; 
        $testcase = $result[$x]["subcategorie"];
    } else {
        echo 
            "<div class='video_div'> <iframe width='196' height='350'
            src='" . $result[$x]['linkembed'] . "'>
            </iframe></div>";    
    }
}

I have tried adding multiple if($result[$x]["subcategorie"] == "categoryname") statements but specifying the name within a for loop resulted in there being multiple of the same divs and a repeat of data. So far I've tried to look up SQL group by PHP tutorials but they all show the same result being inside of a table. The goal is to get the information into their own div with the ID of said div being the category name. I'm working with AJAX calls using JS to fix the issue won't work.

CodePudding user response：

This solution builds a $videos array using the static subcategories as keys, it then populates these after looking through the database, then it's a matter of echoing the iframes if records exist.

<?php

/*

Question Author: Terhert
Question Answerer: Jacob Mulquin
Question: PHP SQL Sorting information into HTML div elements
URL: https://stackoverflow.com/questions/74715867/php-sql-sorting-information-into-html-div-elements
Tags: php, mysql

*/

include '../../inc/helpers.php';

try {
    $pdo = new PDO(DB_DSN, DB_USER, DB_PASS, DB_OPTIONS);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

$pdo->query('DROP TABLE table74715867;');
$pdo->query('CREATE TABLE IF NOT EXISTS table74715867 (id TEXT, name TEXT, linkembed TEXT, categorie TEXT, subcategorie TEXT);');

$data = [
    ['id' => '1', 'name' => 'test1','linkembed' => 'http://example.org', 'categorie' => 'abc', 'subcategorie' => 'kracht'],
    ['id' => '2', 'name' => 'test2','linkembed' => 'http://example.org', 'categorie' => 'abc', 'subcategorie' => 'uithouding'],
    ['id' => '9', 'name' => 'test9','linkembed' => 'http://example.org', 'categorie' => 'abc', 'subcategorie' => 'mobiliteit'],
    ['id' => '10', 'name' => 'test10','linkembed' => 'http://example.org', 'categorie' => 'abc', 'subcategorie' => 'mobiliteit'],
    ['id' => '3', 'name' => 'test3','linkembed' => 'http://example.org', 'categorie' => 'xyz', 'subcategorie' => 'majig1'],
    ['id' => '4', 'name' => 'test4','linkembed' => 'http://example.org', 'categorie' => 'xyz', 'subcategorie' => 'majig2'],
    ['id' => '5', 'name' => 'test5', 'linkembed' => 'http://example.org', 'categorie' => '123', 'subcategorie' => 'whatchmacallit1'],
    ['id' => '6', 'name' => 'test6', 'linkembed' => 'http://example.org', 'categorie' => '123', 'subcategorie' => 'whatchmacallit2'],
    ['id' => '7', 'name' => 'test7', 'linkembed' => 'http://example.org', 'categorie' => 'qwerty', 'subcategorie' => 'fizz1'],
    ['id' => '8', 'name' => 'test8', 'linkembed' => 'http://example.org', 'categorie' => 'qwerty', 'subcategorie' => 'buzz1'],
];

$stmt = $pdo->prepare('INSERT INTO table74715867 SET id=:id, name=:name, linkembed=:linkembed, categorie=:categorie, subcategorie=:subcategorie;');
foreach ($data as $record) {
    $stmt->execute([
        ':id' => $record['id'],
        ':name' => $record['name'],
        ':linkembed' => $record['linkembed'],
        ':categorie' => $record['categorie'],
        ':subcategorie' => $record['subcategorie']
    ]);
}

$category = 'abc';

$stmt = $pdo->prepare("SELECT * FROM table74715867 WHERE categorie=:category ORDER BY categorie ASC, subcategorie ASC");
$stmt->bindParam(':category', $category);
$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

$videos = ["kracht" => [], "uithouding" => [], "mobiliteit" => [], "stretching" => []];
foreach ($result as $record) {
    $videos[$record['subcategorie']][] = $record;
}

foreach ($videos as $subcategory => $records) {
    echo '<div  id="' . $subcategory . '">' . 
    '<h3 >' . $subcategory . '</h3>';
    if (!empty($records)) {
        foreach ($records as $record) {
            echo '<div ><iframe width="196" height="350" src="'.$record['linkembed'].'"></iframe></div>';
        }
    }
    echo '</div>';
}

Here's the resulting HTML:

<head></head>
<body>
  <div  id="kracht">
    <h3 >kracht</h3>
    <div >
      <iframe src="http://example.org" width="196" height="350"></iframe>
    </div>
  </div>
  <div  id="uithouding">
    <h3 >uithouding</h3>
    <div >
      <iframe src="http://example.org" width="196" height="350"></iframe>
    </div>
  </div>
  <div  id="mobiliteit">
    <h3 >mobiliteit</h3>
    <div >
      <iframe src="http://example.org" width="196" height="350"></iframe>
    </div>
    <div >
      <iframe src="http://example.org" width="196" height="350"></iframe>
    </div>
  </div>
  <div  id="stretching">
    <h3 >stretching</h3>
  </div>
</body>