DB Assignment 6

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>SQL Display</title>
</head>
<body>
<div style="text-align:center">
<h1>Display SQL Table</h1>

<?php

    $servername = "localhost";
    $username = "webapp17Us3r";
    $password = "5DfJa2aduPkQtmEK";
    $database = "playground17";
    $table = "jma_information_form";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $database);

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    echo "Connected successfully to ". $database."<br><br>";
    echo "Using table " . $table ."<br><br>";

    $headerString = "Showing ";

    if (empty($_REQUEST) || isset($_REQUEST['all'])) {

        $headerString .= "all entries";

        $sql = "* FROM " . $table;
        $countSql = "COUNT(*) FROM " . $table;

    } else {


        if (isset($_REQUEST['child'])) {
            $headerString .= " child entries";
            $sql = "* FROM " . $table . " WHERE age < 18";
            $countSql = "COUNT(*) FROM " . $table . " WHERE age < 18";
        } elseif (isset($_REQUEST['adult'])) {
            $headerString .= " adult entries";
            $sql = "* FROM " . $table . " WHERE age >= 18";
            $countSql = "COUNT(*) FROM " . $table . " WHERE age >= 18";
        } elseif (isset($_REQUEST['organizations'])) {
            $headerString .= " organizations";
            $sql = "organization FROM " . $table;
            $countSql = "COUNT(organization) FROM " . $table;
            $countDistinctSql = "COUNT(DISTINCT organization) FROM " . $table;
        } elseif (isset($_REQUEST['occupations'])) {
            $headerString .= " occupations";
            $sql = "occupation FROM " . $table;
            $countSql = "COUNT(occupation) FROM " . $table;
            $countDistinctSql = "COUNT(DISTINCT occupation) FROM " . $table;
        } else {
            // do nothing
        }

    }

    if (isset($_REQUEST['distinct'])) {
        $headerString .= " (distinct)";
        if (isset($countDistinctSql)) {
            $data = $conn->query('SELECT ' . $countDistinctSql);
        } else {
            $data = $conn->query('SELECT ' . $countSql);
        }
        $sql = 'DISTINCT ' . $sql;
    } else {
        $data = $conn->query('SELECT '. $countSql);
    }

    echo "<h2>".$headerString."</h2>";

    if ($data->num_rows > 0) {
        while($row = $data->fetch_assoc()) {
            echo "<h3>" . $row[array_keys($row)[0]] . " results</h3>";
        }
    }

    $data = $conn->query('SELECT '. $sql);
    echo displayTable($data).'<br>';

    function displayTable($table) {

        $returnString = '<table border="1" style="text-align: left; margin-left: auto; margin-right: auto">';

        $lables = NULL;

        if ($table->num_rows > 0) {
            // output data of each row


            while($row = $table->fetch_assoc()) {

                if (!isset($labels)) {

                    $returnString .= "<tr>";

                    $labels = array_keys($row);

                    for ($i = 0; $i < count($labels); $i++) {
                        $returnString .= "<th>" . $labels[$i] . "</th>";
                    }

                    $returnString .= "</tr>";


                }
                $returnString .= "<tr>";

                for ($i = 0; $i < count($row); $i++) {
                    $returnString .= "<td>" . $row[$labels[$i]] . "</td>";
                }


                $returnString .= "</tr>";

            }
            return $returnString .= "</table>";

        } else {
            return NULL;
        }
    }


    function showLastNameSearch() {
        // implement name search maybe in future
    }


?>

<br><form action="showTable.php" method="post">

        <input type="checkbox" name="distinct" value="checked"
        <?php
            // Makes sure distinct checkbox stays checkd

            if(isset($_REQUEST['distinct'])) {
                echo 'checked="checked"';
            }
        ?>
        > Distinct Values<br>
        <input type="submit" name="all" value="Show All">
        <input type="submit" name="child" value="Show Children Only">
        <input type="submit" name="adult" value="Show Adults Only">
        <input type="submit" name="organizations" value="Show Organizations Only">
        <input type="submit" name="occupations" value="Show Occupations Only">


</form>
<br>
PS: the checkbox stays checked, even when you submit info (it remembers)


</div>
</body>
</html>