Servatrice Decklist Viewer

database.php

<?php
$dbName = "servatrice";
$dbHost = "localhost";
$dbUser = "servatrice";
$dbPass = "<password>";

$dbConnect = new PDO("mysql:dbname=$dbName;host=$dbHost;charset=utf8",$dbUser,$dbPass);
$dbConnect->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // Enable emulated, prepared statements. Note - false means we will try to use NATIVE prepared statements, but will fall back on emulation.
$dbConnect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Set errors to display if PDO fails.
?>

readDB.php

<?php
require_once("database.php");
if (!empty($_GET['user'])) {
        $user = htmlspecialchars($_GET['user'],ENT_QUOTES);
        if (!empty($_GET['deck'])) {
                $deck = htmlspecialchars($_GET['deck'],ENT_QUOTES);
                $query = "SELECT d.name, u.name user, d.upload_time, d.content FROM cockatrice_decklist_files d, cockatrice_users u WHERE d.id_user = u.id AND LOWER(u.name)=LOWER(:username) AND LOWER(d.name)=LOWER(:deckname)";
                $vars = array("username"=>$user, "deckname"=>$deck);
        } else {
                $query = "SELECT d.name FROM cockatrice_decklist_files d, cockatrice_users u WHERE d.id_user = u.id AND LOWER(u.name)=LOWER(:username)";
                $vars = array("username"=>$user);
        }

        $runQuery = $dbConnect->prepare($query);
        try {
                $runQuery->execute($vars);
//              echo "Completed SQL query <pre>$query</pre><br/>" . PHP_EOL;
        } catch (PDOException $e) {
                exit("SQL exception: " . $e->getMessage() . "<br/>" . PHP_EOL);
        }


        if (empty($_GET['deck'])) {
                echo "<h1>$user</h1>";
                echo "<ul>";
                foreach ($runQuery as $line) {
                        echo "<li><a href='readDB.php?user=" . $user . "&deck=" . urlencode($line['name']) . "'>" . $line['name'] . "</a></li>" . PHP_EOL;
                }
                echo "</ul>";
        } else {
                foreach($runQuery as $line) {
                        echo "<h1><a href='readDB.php?user=" . $line["user"] . "'>" . $line["user"] . "</a>: " . $line["name"] . "</h1>";
                        echo "<h2>" . $line["upload_time"] . "</h2>";
                        $xmlo = xml_parser_create();
                        xml_parse_into_struct($xmlo,$line["content"],$vals,$index);
                        $cards = array();
                        $zone = "";
                        foreach ($vals as $xml) {
                                $card = array();
                                if ($xml["tag"] == "COMMENTS") {
                                        echo "<h3>" . $xml["value"] . "</h3>";
                                } elseif ($xml["tag"] == "ZONE" && $xml["type"] == "open") {
                                        $zone = $xml["attributes"]["NAME"];
                                } elseif ($xml["tag"] == "CARD") {
                                        $card["zone"] = $zone;
                                        $card["name"] = $xml["attributes"]["NAME"];
                                        $card["quantity"] = $xml["attributes"]["NUMBER"];
                                        array_push($cards,$card);
                                }
                        }
                        echo "<ul>";
                        $thisZone = "";
                        foreach ($cards as $card) {
                                if ($card["zone"] != $thisZone) {
                                        echo "<h3>" . $card["zone"] . "</h3>";
                                        $thisZone = $card["zone"];
                                }
                                $gathererLink = "http://gatherer.wizards.com/pages/search/default.aspx";
                                echo "<li>" . $card["quantity"] . "x - <a href='$gathererLink?name=+[\"" . $card["name"] . "\"]' target='_blank'>" . $card["name"] . "</a></li>";
                        }
                        echo "</ul>";
                }
        }
        echo "<a href='readDB.php'>Return to User List</a>";
} else {
        $query = "SELECT u.name user FROM cockatrice_users u";
        $runQuery = $dbConnect->prepare($query);
        try {
                $runQuery->execute($vars);
//              echo "Completed SQL query <pre>$query</pre><br/>" . PHP_EOL;
        } catch (PDOException $e) {
                exit("SQL exception: " . $e->getMessage() . "<br/>" . PHP_EOL);
        }

        echo "<h1>Userlist</h1>";
        echo "<ul>";
        foreach($runQuery as $users) {
                echo "<li><a href='readDB.php?user=" . $users["user"] . "'>" . $users["user"] . "</a></li>";
//              echo "<pre>";
//              print_r($users);
//              echo "</pre>";
        }
        echo "</ul>";
}
?>