04 Sessions Mysql 20 6.01 Mysqli_order_02

04 Sessions Mysql20 6.01 Mysqli_order_02

SELECT * 
FROM php_a04_images
ORDER BY image_id ASC
Sort Table
ID filename caption
1 basin.jpg Water basin at Ryoanji temple, Kyoto.
2 fountains.jpg 🚀 Fountain's in central Tokyo
3 kinkakuji.jpg The Golden Pavilion in Kyoto
4 maiko.jpg Maiko—trainee geishas in Kyoto
5 maiko_phone.jpg Every maiko should have one—a mobile, of course
6 menu.jpg Menu outside restaurant in Pontocho, Kyoto
7 monk.jpg Monk begging for alms in Kyoto
8 ryoanji.jpg 🛕 Autumn leaves at Ryoanji temple, Kyoto 
$_SERVER["SCRIPT_NAME"]
/php/assignments/04-sessions-mysql/20-6.01-mysqli_order_02.php

print_r($_GET)
Array
(
)


print_r($_POST)
Array
(
)


print_r($_SESSION)
Array
(
)


print_r($_FILES)
Array
(
)



<?php
require_once '../../includes/connection.php';
require_once '../../includes/utility_funcs.php';

// connect to MySQL
$conn = dbConnect('read');

// set default values
$col = 'image_id';
$dir = 'ASC';

// create arrays of permitted values
$columns = ['image_id', 'filename', 'caption'];
$direction = ['ASC', 'DESC'];

// if the form has been submitted, use only expected values
if (isset($_GET['column']) && in_array($_GET['column'], $columns)) {
    $col = $_GET['column'];
}
if (isset($_GET['direction']) && in_array($_GET['direction'], $direction)) {
    $dir = $_GET['direction'];
}

// prepare the SQL query using sanitized variables
$sql = "SELECT * 
FROM php_a04_images
ORDER BY $col $dir";



try {
    // submit the query and capture the result
    $result = $conn->query($sql);
    $num_rows = $result->num_rows;
} catch (Exception $e) {
    $error = $e->getMessage();
}


$tools = true;
include("../../includes/header.php");
?>

<main>
    <h2><?php echo $folder_name; ?><span><?php echo $file_name; ?></span></h2>

    <?php
    if (isset($error)) {
        echo "<p class=\"error\">$error</p>";
    }
    ?>

    <figure class="code">
        <pre class="language-sql"><code><?= $sql ?></code></pre>
    </figure>

    <form method="get">
        <fieldset>
            <legend>Sort Table</legend>
            <ol>
                <li class="choose-sort">
                    <label for="column">Column:</label>
                    <select name="column" id="column">
                        <option <?php if ($col == 'image_id') echo 'selected'; ?>>image_id</option>
                        <option <?php if ($col == 'filename') echo 'selected'; ?>>filename</option>
                        <option <?php if ($col == 'caption') echo 'selected'; ?>>caption</option>
                    </select>
                    <label for="direction">Direction:</label>
                    <select name="direction" id="direction">
                        <option value="ASC" <?php if ($dir == 'ASC') echo 'selected'; ?>>Ascending</option>
                        <option value="DESC" <?php if ($dir == 'DESC') echo 'selected'; ?>>Descending</option>
                    </select>
                    <input type="submit" name="change" id="change" value="Change Order">
                </li>
            </ol>
        </fieldset>
    </form>

    <?php if (isset($num_rows)) { ?>
        <table id="output-sql">
            <tr>
                <th>ID</th>
                <th>filename</th>
                <th>caption</th>
            </tr>
            <?php while ($row = $result->fetch_assoc()) { ?>
                <tr>
                    <td><?= $row['image_id']; ?></td>
                    <td><?= safe($row['filename']); ?></td>
                    <td><?= safe($row['caption']); ?></td>
                </tr>
            <?php } ?>
        </table>
    <?php } ?>

</main>
<?php
# The side-bar section of the layout use custom path to load from a different folder.
include("../../includes/sidebar.php");

# The footer section of the layout.
include("../../includes/footer.php");
?>