Page 1 of 1

Making database class

Posted: Thu Nov 10, 2005 1:57 pm
by Luke
OK... here is my database class so far.. I am having trouble with creating a table

Code: Select all

function AddTable($name, $columns, $auto=1){
		//method accepts the name of the table to be created as first argument
		//also accepts an associative array of columns as the second. Keys are column names, values are data types of those columns
		//the third argument is set to one if you want an id column to be automatically inserted as an auto increment. Set to 0 if you don't want this
		$sql = "INSERT INTO " . $table . " VALUES ( ";
		foreach($values as $val){
			$sql .= " '" . mysql_real_escape_string($val) . "',";
		}
		$sql = substr($sql, 0, -1);
		$sql .= ");";
		if(mysql_query($sql)){
			return true;
		}
		logerror("Could not execute query: " . $sql . "\n MySQL says: " . mysql_error());
		return false;
	}
What would be a good way to set the allowed length of the values? (id: VARCHAR(13))

Posted: Thu Nov 10, 2005 3:29 pm
by dallasx
It looks like yer trying to insert a record instead of create a table here.

Posted: Thu Nov 10, 2005 3:34 pm
by Luke
Ooops... sorry I posted the wrong method. Doesn't matter anyway. I figured it out.

Code: Select all

function AddTable($name, $columns, $auto=1){
		//method accepts the name of the table to be created as first argument - the table name and _ is appended to the beginning of each column name
		//also accepts an associative array of columns as the second. Keys are column names, values are data types of those columns (put value lengths
		//in parenthesis next to data type) like this: $array['columnname'] = "VARCHAR(123)";
		//you do not need to make an auto increment id column
		//the third argument is set to one if you want an id column to be automatically inserted as an auto increment. Set to 0 if you don't want this
		$sql = "CREATE TABLE `" . $name . "` (";
		if($auto == 1){
			$id = $name . "_" . "id";
			$sql .= "`" . $id . "` INT NOT NULL AUTO_INCREMENT , PRIMARY KEY ( `" . $id . "` ), ";
		}
		foreach($columns as $key => $val){
			$sql .= "`" . $name . "_" . $key . "` " . $val . ",";
		}
		$sql = substr($sql, 0, -1);
		$sql .= ");";
		if(mysql_query($sql)){
			return true;
		}
		die($sql);
		logerror("Could not execute query: " . $sql . "\n MySQL says: " . mysql_error());
		return false;
	}
Works exactly how it's supposed to. :)

Posted: Thu Nov 10, 2005 3:49 pm
by Luke
OK... here's what I have so far:

Code: Select all

class DataBase{
	function DataBase($host="localhost", $user="root", $pass="pass", $dbas="pdirect_directory"){
		//Make mysql connection
		if(mysql_connect($host, $user, $pass)){
			//select database to work with
			if(!mysql_select_db($dbas)){
				//if we can't select the database, we log the error
				logerror("Could not select database - " . $dbas . "\n MySQL says: " . mysql_error());
			}
		}
		else{
			//if we can't connect, log the error
			logerror("Could not to " . $host . " connect with user - " . $user . "\n MySQL says: " . mysql_error());
		}
	}
	function AddTable($name, $columns, $auto=1){
		//method accepts the name of the table to be created as first argument - the table name and _ is appended to the beginning of each column name
		//also accepts an associative array of columns as the second. Keys are column names, values are data types of those columns (put value lengths
		//in parenthesis next to data type) like this: $array['columnname'] = "VARCHAR(123)";
		//you do not need to make an auto increment id column
		//the third argument is set to one if you want an id column to be automatically inserted as an auto increment. Set to 0 if you don't want this
		//returns true if successful

		//start the query by creating a table and name it the specified name
		$sql = "CREATE TABLE `" . $name . "` (";
		if($auto == 1){
			//if argument three is set to 1, this will add an auto-increment primary key id
			$id = $name . "_" . "id";
			$sql .= "`" . $id . "` INT NOT NULL AUTO_INCREMENT , PRIMARY KEY ( `" . $id . "` ), ";
		}
		foreach($columns as $key => $val){
			//this will prefix all column names with the table name and add them to the query
			$sql .= "`" . $name . "_" . $key . "` " . $val . ",";
		}
		//remove the comma
		$sql = substr($sql, 0, -1);
		//add the end parenthesis
		$sql .= ");";
		if(mysql_query($sql)){
			return true;
		}
		die($sql);
		logerror("Could not execute query: " . $sql . "\n MySQL says: " . mysql_error());
		return false;
	}
	function DropTable($name){
		//this method accepts a table name to be dropped. returns true if successful
		
		$sql = "DROP TABLE " . $name . ";";
		if(mysql_query($sql)){
			return true;
		}
		logerror("Could not execute query: " . $sql . "\n MySQL says: " . mysql_error());
		return false;
	}
	function Insert($values, $table){
		//method accepts the table name as second argument, and the values to be inserted into that table as the first
		//returns true if successful
		
		//start query with specified table		
		$sql = "INSERT INTO " . $table . " VALUES ( ";
		foreach($values as $val){
			//add each escaped value of the array argument to the sql query
			$sql .= " '" . mysql_real_escape_string($val) . "',";
		}
		//remove the comma from the end of the sql query
		$sql = substr($sql, 0, -1);
		//add end parenthesis to sql query
		$sql .= ");";
		if(mysql_query($sql)){
			return true;
		}
		logerror("Could not execute query: " . $sql . "\n MySQL says: " . mysql_error());
		return false;
	}
	function Delete($column, $values, $table){
		//method accepts the name of the table we are working with as well as an array of the values to be deleted.
		//method also accepts a column name to search for these values in
		//returns true if successful
		
		//start query with specified table and column
		$sql = "DELETE FROM " . $table . " WHERE " . $column . " IN (";
		foreach($values as $val){
			//add each value to be deleted to the query
			$sql .= " '" . $val . "',";
		}
		//remove the comma from the end of the sql query
		$sql = substr($sql, 0, -1);
		//add end parenthesis to sql query
		$sql .= ");";
		if(mysql_query($sql)){
			return true;
		}
		logerror("Could not execute query: " . $sql . "\n MySQL says: " . mysql_error());
		return false;
	}
}
So I have the constructor which opens the connection and then AddTable, DropTable, Insert, and Delete methods. I know I still need a select method, but is there any other type of database interaction I am missing that is commonly needed?
The delete method is pretty cool... it can delete based on any column and can delete multiple rows... pretty cool.

Posted: Thu Nov 10, 2005 5:25 pm
by redmonkey
I suppose it depends on your application but I wouldn't class adding and dropping of tables common functions of the database. Certainly I've never had anything remotely like these methods in any of my database classes. Again dependant on your application but I would think there is a design flaw in your data storage if you have to dynamically create and drop tables.

I would say there are many things still missing from your class. You will need some way of accessing the database stats of number of rows in a result set or number of rows deleted etc....

Different people have different ideas, personally I just have a single query method and build the SQL statements somwehre else in the code. Other prefer an insert method perhaps passing an associative array in where key may be the coloums and the values well.. the values.

I also don't like to make the connection within the constructor, I invariably have my database class included by a 'startup' script which will load a lot of common parts of the code base, however although the database may be used on almost all pages it might not be. Instead I prefer the database connection to be established when the first query is executed that way even if the DB class is loaded and I don't happen to use it, then I haven't wasted anything by establishing a non-required connection.

It's a case of each to their own, but I always feel that by having seperate methods for insert, delete, select etc.. it becomes quite complex and you could end up designing your database structure round the limitations of your handler class.

Posted: Thu Nov 10, 2005 5:32 pm
by Luke
Yes... the create and drop table methods are simply because there is no other way to do so on my server. I don't have any other way to create or drop tables on my server.
Different people have different ideas, personally I just have a single query method and build the SQL statements somwehre else in the code. Other prefer an insert method perhaps passing an associative array in where key may be the coloums and the values well.. the values.
Would you mind explaining this a little better? I really appreciate your input, but I don't think I understand what you mean by that.
I am also wondering what exactly you mean by this...
I also don't like to make the connection within the constructor, I invariably have my database class included by a 'startup' script which will load a lot of common parts of the code base, however although the database may be used on almost all pages it might not be. Instead I prefer the database connection to be established when the first query is executed
Where, then would I initiate the connection?

Edit: I'm wondering... does anybody have a database class they don't mind sharing so I can see just how things are supposed to be done? (I understand there really is no standard method, but I am having trouble grasping this whole OOP thing. Maybe I should have started with OOP on a different language... this is my first real programming of any kind)

Posted: Thu Nov 10, 2005 5:52 pm
by redmonkey
One armed space goat wrote:
Different people have different ideas, personally I just have a single query method and build the SQL statements somwehre else in the code. Other prefer an insert method perhaps passing an associative array in where key may be the coloums and the values well.. the values.
Would you mind explaining this a little better? I really appreciate your input, but I don't think I understand what you mean by that.
Well, my query method is very similar in usage to the standard call to mysql_query. I have various functions which will build the specific SQL query to exectute. i.e. there could be a function which gets a users email address for a given user id, which will have/create the SQL query then just run it through the database handler.
One armed space goat wrote:I am also wondering what exactly you mean by this...
I also don't like to make the connection within the constructor, I invariably have my database class included by a 'startup' script which will load a lot of common parts of the code base, however although the database may be used on almost all pages it might not be. Instead I prefer the database connection to be established when the first query is executed
Where, then would I initiate the connection?
You don't have to establish a connection within the constructor, I tend to have a little bit of 'magic' :lol: (it's not that complicated) going on that only establishes the connection when it's first needed.

I could show you mine, but I'm not sure it would be of much help. I don't run my DB handler as an instantiated object, mine, although within a class just runs as static. Also, I'm in the process of rewritting it at present so it's a bit 'work in progress' style.

Posted: Thu Nov 10, 2005 6:06 pm
by Luke
redmonkey wrote:Well, my query method is very similar in usage to the standard call to mysql_query. I have various functions which will build the specific SQL query to exectute. i.e. there could be a function which gets a users email address for a given user id, which will have/create the SQL query then just run it through the database handler.
I'm still confused... would you mind posting some code that could clear this up for me?

Posted: Thu Nov 10, 2005 6:14 pm
by redmonkey
I'm probably not explaining it very well and I'm not sure a code example will help but...

Code: Select all

/**
	* @return resource link_id    resource link identifer associated with this query
	* @param  string   sql        the SQL query to execute
	* @desc                       query database and return link identifier
	*/
	function query($sql)
	{
		if (!$link_id = @mysql_query($sql, $conn_id = DB::connection_id()))
		{
			$err_string = "Invalid Query : {$sql} : MySQL said - " . DB::error_string($conn_id);

			DB::error(DB::error_code($conn_id), $err_string, __LINE__);
		}

		return $link_id;
	}
... that is my query function, usage wise, it's pretty much the same as your standard call to MySQL. There is no actual SQL statements generated within my DB class. I'm guessing that probably hasn't helped you much?

Posted: Thu Nov 10, 2005 6:22 pm
by Luke
what builds the sql query?

Posted: Thu Nov 10, 2005 6:34 pm
by redmonkey
The SQL queries are built within other areas of the code (other objects/functions/static classes) then passed through the DB class. As I said, my method probably won't help as I use a static database class. You on the other appear to be looking to create a database object. These are two entirely different approaches.

Posted: Thu Nov 10, 2005 6:35 pm
by s.dot
might want to ***** your password, eh? ;d

Posted: Thu Nov 10, 2005 6:37 pm
by Luke
scrotaye wrote:might want to ***** your password, eh? ;d
Good call.. although it's just my testing server. But yea good call.
redmonkey wrote:The SQL queries are built within other areas of the code (other objects/functions/static classes) then passed through the DB class. As I said, my method probably won't help as I use a static database class. You on the other appear to be looking to create a database object. These are two entirely different approaches.
OK... that helps. I am lost in all of this. Some day I will get it.