Jump to content


Photo

How to code SELECT statements when using Database Abstraction


  • Please log in to reply
3 replies to this topic

#1 Matthew!!!

Matthew!!!
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 23 October 2006 - 09:51 PM

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.

<?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; }
}

?>

Thanks for taking the time to read my post,
Matthew

#2 trq

trq
  • Staff Alumni
  • Advanced Member
  • 31,041 posts

Posted 23 October 2006 - 10:01 PM

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 Propel? It seems like that is exactly what your trying to build.

#3 Matthew!!!

Matthew!!!
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 23 October 2006 - 10:12 PM

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?

#4 trq

trq
  • Staff Alumni
  • Advanced Member
  • 31,041 posts

Posted 23 October 2006 - 10:17 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users