Matthew!!!

Propel looks something similar to what I need, but it looks far too elaborate for what the site will do. Do you think it would be best if I just abstracted away the UPDATE / INSERT / DELETE methods and the SELECT method accepts an sql statement?

Hey fellows, this is my first post here, so hopefully I didn't post it in the wrong section. Basically, this is what I am planning on doing, I want to make a Database Abstraction layer for MySQL. I'm trying to abstract the logic from the mysql calls, because I plan on having a few different people working on this project with me, and wish to create some API's for them to use so they will not need to directly access the database themselves. I have my UPDATE, INSERT, and DELETE commands working fine (although the Update function is pretty lame), the problem I am having is with the theory behind working on the SELECT statements. With INSERT, UPDATE, and DELETE, they just need to be called and they take no parameters.  The problem I have is that the SELECT statement has many different properties (group by, having, where, limit, etc...) that I can't figure out a practical way to add all that functionality without writing 100's of Select[ByUsername/ByEmail/WhereJoindate] functions. What I would like to know if it is possible to add that functionality without having the developers write fields in the table, as well as joins, because I would rather have them just call a few functions to get the result they want. Can I do it without having to send an SQL statement to my object, or is it a necessary thing to do? Hopefully my question isn't totally stupid, anyway heres the code I have so far. [code] <?php // This code was created by Matthew!!! (splizxer@hotmail.com) /* DROP TABLE IF EXISTS users; CREATE TABLE users ( userid INT UNSIGNED NOT NULL AUTO_INCREMENT, userlevel TINYINT UNSIGNED NOT NULL DEFAULT 2, username VARCHAR(31) NOT NULL, password CHAR(32) NOT NULL, email VARCHAR(63) NOT NULL, joindate INT UNSIGNED NOT NULL, PRIMARY KEY(userid), UNIQUE(username), UNIQUE(email) ); */ class CUsers { private $m_hSQL; private $m_hResult; private $m_userID = -1; // primary key private $m_userLevel; private $m_username; private $m_password; private $m_email; private $m_joinDate; public function __construct($hSQL) { $this->m_hSQL = $hSQL; } /* -------------------------------------------------------------------------------------------------------------- Select -------------------------------------------------------------------------------------------------------------- */ public function Select($where, $group, $limit, $offset, $having) { $sql = 'SELECT userid, userlevel, username, password, email, joindate FROM users WHERE '; return false; } /* public function SelectByUserID($userID) { if( is_numeric($userID) && $userID < 0xFFFFFFFF && $userID >= 0 ) { $sql = 'SELECT userid, userlevel, username, password, email, joindate FROM users WHERE userid=' . mysql_real_escape_string($userID, $this->m_hSQL); if( $this->m_hResult = mysql_query($sql, $this->m_hSQL) ) { $this->Next(); return true; } } return false; } public function SelectByUsername($username, $caseSensitive = false) { if( strlen($username) < 32 ) { $sql = 'SELECT userid, userlevel, username, password, email, joindate FROM users WHERE lower(username)=\'' . mysql_real_escape_string(strtolower($username), $this->m_hSQL) . '\''; if( $this->m_hResult = mysql_query($sql, $this->m_hSQL) ) { $this->Next(); return true; } } return false; } public function SelectByEmail($email) { if( strlen($email) < 64 ) { $sql = 'SELECT userid, userlevel, username, password, email, joindate FROM users WHERE lower(email)=\'' . mysql_real_escape_string(strtolower($email), $this->m_hSQL) . '\''; if( $this->m_hResult = mysql_query($sql, $this->m_hSQL) ) { $this->Next(); return true; } } return false; } */ // result count public function ResultCount() { if( $this->m_hResult ) return mysql_num_rows($this->m_hResult); } // next record public function Next() { if( $this->m_hResult ) { $row = mysql_fetch_row($this->m_hResult); if( $row ) { $m_userID = $row[0]; $m_userLevel = $row[1]; $m_username = $row[2]; $m_password = $row[3]; $m_email = $row[4]; $m_joinDate = $row[5]; return true; } } return false; } // seek public function Seek($index) { mysql_data_seek($index, $this->m_hResult); return $this->Next(); } /* -------------------------------------------------------------------------------------------------------------- Insert -------------------------------------------------------------------------------------------------------------- */ public function Insert() { $sql = 'INSERT INTO users(userlevel, username, password, email, joindate) VALUES(' . mysql_real_escape_string($this->m_userLevel, $this->m_hSQL) . ', ' . '\'' . mysql_real_escape_string($this->m_username, $this->m_hSQL) . '\', ' . '\'' . mysql_real_escape_string($this->m_password, $this->m_hSQL) . '\', ' . '\'' . mysql_real_escape_string($this->m_email, $this->m_hSQL) . '\', ' . mysql_real_escape_string($this->m_joinDate, $this->m_hSQL) . ')'; if( mysql_query($this->sql, $this->m_hSQL) == true ) { // retrieve generated auto_increment ID $this->m_userID = mysql_insert_id($this->m_hSQL); return true; } return false; } /* -------------------------------------------------------------------------------------------------------------- Update -------------------------------------------------------------------------------------------------------------- */ public function Update() { $sql = 'UPDATE users SET ' . 'userlevel=' . mysql_real_escape_string($this->m_userLevel, $this->m_hSQL) . ', ' . 'username=\'' . mysql_real_escape_string($this->m_username, $this->m_hSQL) . '\', ' . 'password=\'' . mysql_real_escape_string($this->m_password, $this->m_hSQL) . '\', ' . 'email=\'' . mysql_real_escape_string($this->m_email, $this->m_hSQL) . '\', ' . 'joindate=' . mysql_real_escape_string($this->m_joinDate, $this->m_hSQL) . ' WHERE userid=' . mysql_real_escape_string($this->m_userID, $this->m_hSQL); return (mysql_query($sql, $this->m_hSQL) != false); } /* -------------------------------------------------------------------------------------------------------------- Delete -------------------------------------------------------------------------------------------------------------- */ public function Delete() { $sql = 'DELETE FROM users WHERE userid=' . mysql_real_escape_string($this->m_userID, $this->m_hSQL); return (mysql_query($sql, $this->m_hSQL) != false); } /* -------------------------------------------------------------------------------------------------------------- Setters -------------------------------------------------------------------------------------------------------------- */ public function SetUserLevel($userLevel) { if( is_numeric($userLevel) && $userLevel >= 0 && $userLevel <= 0xFF ) { $this->m_userLevel = $userLevel; return true; } return false; } public function SetUsername($username) { if( strlen($username) < 32 ) { $this->m_username = $username; return true; } return false; } public function SetPassword($password) { if( strlen($password) <= 32 ) { $this->m_password = $password; return true; } return false; } public function SetEmail($email) { if( strlen($email) < 64 ) { $this->m_email = $email; return true; } return false; } public function SetJoinDate($joinDate) { if( is_numeric($joinDate) && $joinDate >= 0 && $joinDate <= 0xFFFFFFFF ) { $this->m_joinDate = $joinDate; return true; } return false; } /* -------------------------------------------------------------------------------------------------------------- Getters -------------------------------------------------------------------------------------------------------------- */ public function GetUserID() { return $this->m_userID; } public function GetUserLevel() { return $this->m_userLevel; } public function GetUsername() { return $this->m_username; } public function GetPassword() { return $this->m_password; } public function GetEmail() { return $this->m_email; } public function GetJoinDate() { return $this->m_joinDate; } } ?> [/code] Thanks for taking the time to read my post, Matthew