db_backend.php

<?php

include_once "User.php";
include_once "config.php";

use \User as User;

class DB
{

    public $pdo;
    public $log;

    function __construct($log)
    {
        global $DB, $DB_USER, $DB_PASS;
        $this->log = $log;
        try {
            $this->pdo = new PDO($DB, $DB_USER, $DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") );
            if($log)
                $log->info("Connected to database $DB");
        } catch (PDOException $e) {
            echo $e->getMessage();
            if($log)
                $log->error($e->getMessage());
        }
    }

    function add_user(User $user)
    {
        $columns = $this->getColumnNames("person");
        foreach ((array) $user as $key => $value) {
            if (array_key_exists($key, (array) $columns)) {
                $a[$key] = $value;
            }
        }
        if ($user->participation_confirmed) {
            $a["participation_confirmed_at"] = date("Y-m-d H:i:s UTC");
        }
        $keys = array_keys($a);
        $sql = "INSERT INTO person (" . implode(", ", $keys) . ") \n";
        $sql .= "VALUES ( :" . implode(", :", $keys) . ")";
        $q = $this->pdo->prepare($sql);
        $i = 1;
        foreach ($a as $value)
            $q->bindParam($i++, $value);
        $this->log->debug($sql);
        $res = $q->execute($a);
        $user->id = $this->pdo->lastInsertId();
        return $res;
    }

    function getColumnNames($table)
    {
        $sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = :table";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindValue(':table', $table, PDO::PARAM_STR);
            $stmt->execute();
            $persons = array();
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $persons[$row['COLUMN_NAME']] = $row['COLUMN_NAME'];
            }
            return $persons;
        } catch (PDOException $pe) {
            trigger_error('Could not connect to MySQL database. ' . $pe->getMessage(), E_USER_ERROR);
        }
    }

    function updateInvoice($user) {
        $this->log->debug("updateInvoice");
        $sql = "UPDATE person SET invoice = :invoice WHERE id = :id;";
        $stmt = $this->pdo->prepare($sql);

        $stmt->bindParam(':invoice', $user->invoice, PDO::PARAM_LOB);
        $stmt->bindParam(':id', $user->id);

        return $stmt->execute();
    }

    function mail_count($email)
    {
        $sql = "SELECT COUNT(*) FROM person WHERE email = :email AND participation_confirmed";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindValue(':email', $email, PDO::PARAM_STR);
            $stmt->execute();
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            return $row['COUNT(*)'];
        } catch (PDOException $pe) {
            trigger_error('Could not connect to MySQL database. ' . $pe->getMessage(), E_USER_ERROR);
        }
    }

    function dinner_count()
    {
        $sql = "SELECT COUNT(*) FROM person WHERE conference_dinner";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute();
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            return $row['COUNT(*)'];
        } catch (PDOException $pe) {
            trigger_error('Could not connect to MySQL database. ' . $pe->getMessage(), E_USER_ERROR);
        }
    }

    function get_persons($filter)
    {
        $sql = "SELECT *, NULL as invoice FROM person " . ($filter?  "WHERE " . $filter : '');   

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute();
            $persons = array();
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $user = new User();
                $user->init_from_array($row);
                $persons[] = $user;
            }
            return $persons;
        } catch (PDOException $pe) {
            trigger_error('Could not connect to MySQL database. ' . $pe->getMessage(), E_USER_ERROR);
        }
    }
}