include/database.php

   1 <?
   2 /**
   3  * Database.php
   4  *
   5  * The Database class is meant to simplify the task of accessing
   6  * information from the website's database.
   7  *
   8  * Written by: Jpmaster77 a.k.a. The Grandmaster of C++ (GMC)
   9  * Last Updated: August 17, 2004
  10  */
  11 include("constants.php");
  12
  13 class MySQLDB
  14 {
  15    var $connection;         //The MySQL database connection
  16    var $num_active_users;   //Number of active users viewing site
  17    var $num_active_guests;  //Number of active guests viewing site
  18    var $num_members;        //Number of signed-up users
  19    /* Note: call getNumMembers() to access $num_members! */
  20
  21    /* Class constructor */
  22    function MySQLDB(){
  23       /* Make connection to database */
  24       $this->connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());
  25       mysql_select_db(DB_NAME, $this->connection) or die(mysql_error());
  26
  27       /**
  28        * Only query database to find out number of members
  29        * when getNumMembers() is called for the first time,
  30        * until then, default value set.
  31        */
  32       $this->num_members = -1;
  33
  34       if(TRACK_VISITORS){
  35          /* Calculate number of users at site */
  36          $this->calcNumActiveUsers();
  37
  38          /* Calculate number of guests at site */
  39          $this->calcNumActiveGuests();
  40       }
  41    }
  42
  43    /**
  44     * confirmUserPass - Checks whether or not the given
  45     * username is in the database, if so it checks if the
  46     * given password is the same password in the database
  47     * for that user. If the user doesn't exist or if the
  48     * passwords don't match up, it returns an error code
  49     * (1 or 2). On success it returns 0.
  50     */
  51    function confirmUserPass($username, $password){
  52       /* Add slashes if necessary (for query) */
  53       if(!get_magic_quotes_gpc()) {
  54               $username = addslashes($username);
  55       }
  56
  57       /* Verify that user is in database */
  58       $q = "SELECT password FROM ".TBL_USERS." WHERE username = '$username'";
  59       $result = mysql_query($q, $this->connection);
  60       if(!$result || (mysql_numrows($result) < 1)){
  61          return 1; //Indicates username failure
  62       }
  63
  64       /* Retrieve password from result, strip slashes */
  65       $dbarray = mysql_fetch_array($result);
  66       $dbarray['password'] = stripslashes($dbarray['password']);
  67       $password = stripslashes($password);
  68
  69       /* Validate that password is correct */
  70       if($password == $dbarray['password']){
  71          return 0; //Success! Username and password confirmed
  72       }
  73       else{
  74          return 2; //Indicates password failure
  75       }
  76    }
  77
  78    /**
  79     * confirmUserID - Checks whether or not the given
  80     * username is in the database, if so it checks if the
  81     * given userid is the same userid in the database
  82     * for that user. If the user doesn't exist or if the
  83     * userids don't match up, it returns an error code
  84     * (1 or 2). On success it returns 0.
  85     */
  86    function confirmUserID($username, $userid){
  87       /* Add slashes if necessary (for query) */
  88       if(!get_magic_quotes_gpc()) {
  89               $username = addslashes($username);
  90       }
  91
  92       /* Verify that user is in database */
  93       $q = "SELECT userid FROM ".TBL_USERS." WHERE username = '$username'";
  94       $result = mysql_query($q, $this->connection);
  95       if(!$result || (mysql_numrows($result) < 1)){
  96          return 1; //Indicates username failure
  97       }
  98
  99       /* Retrieve userid from result, strip slashes */
 100       $dbarray = mysql_fetch_array($result);
 101       $dbarray['userid'] = stripslashes($dbarray['userid']);
 102       $userid = stripslashes($userid);
 103
 104       /* Validate that userid is correct */
 105       if($userid == $dbarray['userid']){
 106          return 0; //Success! Username and userid confirmed
 107       }
 108       else{
 109          return 2; //Indicates userid invalid
 110       }
 111    }
 112
 113    /**
 114     * usernameTaken - Returns true if the username has
 115     * been taken by another user, false otherwise.
 116     */
 117    function usernameTaken($username){
 118       if(!get_magic_quotes_gpc()){
 119          $username = addslashes($username);
 120       }
 121       $q = "SELECT username FROM ".TBL_USERS." WHERE username = '$username'";
 122       $result = mysql_query($q, $this->connection);
 123       return (mysql_numrows($result) > 0);
 124    }
 125
 126    /**
 127     * usernameBanned - Returns true if the username has
 128     * been banned by the administrator.
 129     */
 130    function usernameBanned($username){
 131       if(!get_magic_quotes_gpc()){
 132          $username = addslashes($username);
 133       }
 134       $q = "SELECT username FROM ".TBL_BANNED_USERS." WHERE username = '$username'";
 135       $result = mysql_query($q, $this->connection);
 136       return (mysql_numrows($result) > 0);
 137    }
 138
 139    /**
 140     * addNewUser - Inserts the given (username, password, email)
 141     * info into the database. Appropriate user level is set.
 142     * Returns true on success, false otherwise.
 143     */
 144    function addNewUser($username, $password, $email, $nombre, $encargado, $catedratico, $tipo, $departamento){
 145       $time = time();
 146       /* If admin sign up, give admin user level */
 147       if(strcasecmp($username, ADMIN_NAME) == 0){
 148          $ulevel = ADMIN_LEVEL;
 149       }else{
 150          $ulevel = USER_LEVEL;
 151       }
 152       $q = "INSERT INTO ".TBL_USERS." VALUES ('$username', '$password', '0', $ulevel, '$email', $time, '$nombre', '$encargado', '$catedratico','$tipo','$departamento')";
 153       return mysql_query($q, $this->connection);
 154    }
 155
 156    /**
 157     * updateUserField - Updates a field, specified by the field
 158     * parameter, in the user's row of the database.
 159     */
 160    function updateUserField($username, $field, $value){
 161       $q = "UPDATE ".TBL_USERS." SET ".$field." = '$value' WHERE username = '$username'";
 162       return mysql_query($q, $this->connection);
 163    }
 164
 165    /**
 166     * getUserInfo - Returns the result array from a mysql
 167     * query asking for all information stored regarding
 168     * the given username. If query fails, NULL is returned.
 169     */
 170    function getUserInfo($username){
 171       $q = "SELECT * FROM ".TBL_USERS." WHERE username = '$username'";
 172       $result = mysql_query($q, $this->connection);
 173       /* Error occurred, return given name by default */
 174       if(!$result || (mysql_numrows($result) < 1)){
 175          return NULL;
 176       }
 177       /* Return result array */
 178       $dbarray = mysql_fetch_array($result);
 179       return $dbarray;
 180    }
 181
 182    /**
 183     * getNumMembers - Returns the number of signed-up users
 184     * of the website, banned members not included. The first
 185     * time the function is called on page load, the database
 186     * is queried, on subsequent calls, the stored result
 187     * is returned. This is to improve efficiency, effectively
 188     * not querying the database when no call is made.
 189     */
 190    function getNumMembers(){
 191       if($this->num_members < 0){
 192          $q = "SELECT * FROM ".TBL_USERS;
 193          $result = mysql_query($q, $this->connection);
 194          $this->num_members = mysql_numrows($result);
 195       }
 196       return $this->num_members;
 197    }
 198
 199    /**
 200     * calcNumActiveUsers - Finds out how many active users
 201     * are viewing site and sets class variable accordingly.
 202     */
 203    function calcNumActiveUsers(){
 204       /* Calculate number of users at site */
 205       $q = "SELECT * FROM ".TBL_ACTIVE_USERS;
 206       $result = mysql_query($q, $this->connection);
 207       $this->num_active_users = mysql_numrows($result);
 208    }
 209
 210    /**
 211     * calcNumActiveGuests - Finds out how many active guests
 212     * are viewing site and sets class variable accordingly.
 213     */
 214    function calcNumActiveGuests(){
 215       /* Calculate number of guests at site */
 216       $q = "SELECT * FROM ".TBL_ACTIVE_GUESTS;
 217       $result = mysql_query($q, $this->connection);
 218       $this->num_active_guests = mysql_numrows($result);
 219    }
 220
 221    /**
 222     * addActiveUser - Updates username's last active timestamp
 223     * in the database, and also adds him to the table of
 224     * active users, or updates timestamp if already there.
 225     */
 226    function addActiveUser($username, $time){
 227       $q = "UPDATE ".TBL_USERS." SET timestamp = '$time' WHERE username = '$username'";
 228       mysql_query($q, $this->connection);
 229
 230       if(!TRACK_VISITORS) return;
 231       $q = "REPLACE INTO ".TBL_ACTIVE_USERS." VALUES ('$username', '$time')";
 232       mysql_query($q, $this->connection);
 233       $this->calcNumActiveUsers();
 234    }
 235
 236    /* addActiveGuest - Adds guest to active guests table */
 237    function addActiveGuest($ip, $time){
 238       if(!TRACK_VISITORS) return;
 239       $q = "REPLACE INTO ".TBL_ACTIVE_GUESTS." VALUES ('$ip', '$time')";
 240       mysql_query($q, $this->connection);
 241       $this->calcNumActiveGuests();
 242    }
 243
 244    /* These functions are self explanatory, no need for comments */
 245
 246    /* removeActiveUser */
 247    function removeActiveUser($username){
 248       if(!TRACK_VISITORS) return;
 249       $q = "DELETE FROM ".TBL_ACTIVE_USERS." WHERE username = '$username'";
 250       mysql_query($q, $this->connection);
 251       $this->calcNumActiveUsers();
 252    }
 253
 254    /* removeActiveGuest */
 255    function removeActiveGuest($ip){
 256       if(!TRACK_VISITORS) return;
 257       $q = "DELETE FROM ".TBL_ACTIVE_GUESTS." WHERE ip = '$ip'";
 258       mysql_query($q, $this->connection);
 259       $this->calcNumActiveGuests();
 260    }
 261
 262    /* removeInactiveUsers */
 263    function removeInactiveUsers(){
 264       if(!TRACK_VISITORS) return;
 265       $timeout = time()-USER_TIMEOUT*60;
 266       $q = "DELETE FROM ".TBL_ACTIVE_USERS." WHERE timestamp < $timeout";
 267       mysql_query($q, $this->connection);
 268       $this->calcNumActiveUsers();
 269    }
 270
 271    /* removeInactiveGuests */
 272    function removeInactiveGuests(){
 273       if(!TRACK_VISITORS) return;
 274       $timeout = time()-GUEST_TIMEOUT*60;
 275       $q = "DELETE FROM ".TBL_ACTIVE_GUESTS." WHERE timestamp < $timeout";
 276       mysql_query($q, $this->connection);
 277       $this->calcNumActiveGuests();
 278    }
 279
 280    /**
 281     * query - Performs the given query on the database and
 282     * returns the result, which may be false, true or a
 283     * resource identifier.
 284     */
 285    function query($query){
 286       return mysql_query($query, $this->connection);
 287    }
 288 };
 289
 290 /* Create database connection */
 291 $database = new MySQLDB;
 292
 293 ?>