How to code SELECT statements when using Database Abstraction

[Matthew!!!]

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

[trq]

Your really going to loose alot of flexability if your client code can't build its own sql statements as required. It also seems the way your going about it is going to meen allot of maintainence within the abstraction class itself.

Have you thought of looking at something like [url=http://propel.phpdb.org/trac]Propel[/url]? It seems like that is exactly what your trying to build.

[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?

[trq]

You could create some simple selects but I'de also make available a method for selecting via sql. It depends really... what is the reasoning behind the abstraction? I guess any developer who really wants to is just going to go straight through the mysql functions anyhow.