<?php
/*
 * Created on Feb 19, 2008
 *
 * A class encapsulating all the database manipulation required
 */

 class MyDb {
        private $db;      // The database handle
        // Our database uri.  Add a password after the username
        const USER = 'ianw';
        const PASSWORD = 'REDACTED';
        const HOST = 'sql.rn.informatics.scitech.sussex.ac.uk';
        const DATABASE = 'musicChoice';

        // The constructor, which generates a connection to the database
        function __construct() {
                // Connect...
          $this->db = new mysqli(MyDb::HOST,MyDb::USER,MyDb::PASSWORD,MyDb::DATABASE);
                // Whinge if we cannot connect
                if(!$this->db) {
                  die("cannot connect: " . mysql_error());
                }
                // All errors in database connectivity will cause application death
                //$this->db->setErrorHandling(PEAR_ERROR_DIE);
                // $this->db->setFetchMode (DB_FETCHMODE_ASSOC);
        }
    // See if the user and password are correct, and if so return the id and password
        function checkUser($user, $password) {
                // Use statement preparation to check against sql injection
                $stmt = $this->db->prepare('SELECT id,password from users where user = ?');
                // bind parameters for markers
        $stmt->bind_param("s", $user);
        // bind results to output variable
        $stmt->bind_result($userId,$passwd);
        // execute query
                $stmt->execute();
                // We expect users to be unique
                if($stmt->fetch()) {
                        // Notice that we 'crypt' the password before comparing
                        if(crypt($password,$passwd) == $passwd) {
                                return $userId;
                        }
                }
                return false;
        }

        // Does the user exist?
        function isUser($user) {
                // Use statement preparation to check against sql injection
                $stmt = $this->db->prepare('SELECT id from users where user = ?')
;
                // bind parameters for markers
                        $stmt->bind_param("s", $user);
        // bind results to output variable
        $stmt->bind_result($userId);
        // execute query
                $stmt->execute();
                // We expect users to be unique

                $userRow = $stmt->fetch();
                return $userRow;
        }

        // Add the user with the given password
        function addUser($user, $password) {
                // Use statement preparation to check against sql injection
                $stmt = $this->db->prepare('INSERT INTO users (user, password) VALUES (?,?)');
                // bind parameters for markers
        $stmt->bind_param("ss", $user,crypt($password));
        // execute query
                $stmt->execute();
                // return the userId
                return $this->checkUser($user,$password);
        }

        // Add an element description
        function addElement($element) {
                // Use statement preparation to check against sql injection
                $stmt = $this->db->prepare('INSERT INTO elements (description) VALUES (?)');
                // bind parameters for markers
        $stmt->bind_param("s", $element);
        // execute query
                $stmt->execute();
        }

        // Add a link between a user and an element
        function addFavourite($userId,$elementId) {

                // Use statement preparation to check against sql injection
                $stmt = $this->db->prepare('INSERT INTO favourites (userId, elementId) VALUES (?,?)');
                // bind parameters as integers for markers
        $stmt->bind_param("ii", $userId,$elementId);
        // execute query
                $stmt->execute();

        }

        // Return the favourites of the given user as array of id=>description
        function findFavourites($userId) {
                // Use statement preparation to check against sql injection
                $stmt = $this->db->prepare('SELECT favourites.id,description FROM favourites,elements WHERE favourites.userId = ? AND favourites.elementId = elements.id');
                // bind parameters for markers
        $stmt->bind_param("i", $userId);
        // bind results to output variable
        $stmt->bind_result($favouriteId,$description);
                // execute query
                $stmt->execute();
                $favourites = array();
                while($stmt->fetch()) {
                        $favourites[] = array('id' => $favouriteId,'description' => $description);
                }
                return $favourites;
        }

                // Return the elements  as array of id=>description
        function getElements() {
                // No input data, so we'll execute the statement directly
                $result = $this->db->query('SELECT id,description FROM elements');
                $elements = array();
                while($e = $result->fetch_object()) {
                        $elements[] = $e;
                }
                return $elements;

        }

        // Delete the given user record
        function deleteFavourite($id) {
                // Use statement preparation to check against sql injection
                $stmt = $this->db->prepare('DELETE FROM favourites WHERE id = ?');
                // bind parameters for markers
        $stmt->bind_param("i", $id);
        // execute query
                $stmt->execute();
        }
 }

 // provide access to the database
 $GLOBALS['myDb'] = new MyDb();
?>