My database class. Please try out.

[xenophobia]

db.class.php

class db{
/**Default values**/
var $host = "localhost";
var $username = "db_name";
var $password = "";
var $dbname = "mydb";
var $db;
var $sql = array();	//for multiple sql
var $qry = array();	//usage: $db->sql['first_sql'] = "SELECT ...";  $db->sql['second_sql'] = "INSERT ..."; and so on....
var $qrypointer = "";	//pointer of the query
var $escStr = 1;	//Using function mysql_real_escape_string(). 0-Turn off; 1-Turn on

//constructor
function db()
{
	$this->db = mysql_connect($this->host, $this->username, $this->password) or die(mysql_error());

	mysql_select_db($this->dbname) or die(mysql_error());
}

function GetQuery($qryname = "")
{
	if($qryname == ""){
		$qryname = $this->qrypointer;
	}

	if(isset($this->sql[$qryname])){
		$this->qrypointer = $qryname;
		$this->qry[$qryname] = mysql_query($this->sql[$qryname], $this->db) or die(mysql_error());
		return $this->qry[$qryname];
	}

	//no such a query name existed in the array!
	echo "Empty query.";
	exit();
}

function GetResult($fieldname, $rownum=0, $qryname="")
{
	if($qryname == ""){
		$qryname = $this->qrypointer;
	}

	if(isset($this->qry[$qryname])){
		return mysql_result($this->qry[$qryname], $rownum, $fieldname);
	}

	//no such query name existed in the array!
	echo "Empty query.";
	exit();
}

function GetRow($qryname="")
{
	if($qryname == ""){
		$qryname = $this->qrypointer;
	}

	if(isset($this->qry[$qryname])){
		return mysql_num_rows($this->qry[$qryname]);
	}

	//no such query name existed in the array!
	echo "Empty query.";
	exit();
}

function GetAffectedRow(){
	return mysql_affected_rows($this->db);
}

function DbDisconnect()
{
	mysql_close($this->db);
}

function insert($tbl_name, $field_arry)
{
	//===================================
	//	Date:	22 July 2007
	//	Author:	Kent Lee
	//	Description:
	//	Get all the field in the array. Used key as the column name.
	//===================================
	if(isempty($tbl_name) || !is_array($field_arry))
		return 0;

	$sql_str = "INSERT INTO $tbl_name (";
	foreach($field_arry as $key => $val){
		$sql_str .= "`$key`, ";
	}
	$sql_str = substr($sql_str, 0, strlen($sql_str)-2);
	$sql_str .= ") VALUES (";
	foreach($field_arry as $key => $val){
		$tval = $val;
		if($this->escStr)
			$tval = mysql_real_escape_string($tval);

		$sql_str .= "'$tval', ";
	}
	$sql_str = substr($sql_str, 0, strlen($sql_str)-2);
	$sql_str .= ");";

	//RUN SQL...
	$sql_name = "_sqlinsert_";
	$temp_sqlname = $this->qrypointer;

	$this->sql[$sql_name] = $sql_str;
	$this->GetQuery($sql_name);

	if($this->GetAffectedRow())
		return 1;
	else
		return 0;

	$this->qrypointer = $temp_sqlname;	//restore back the pointer.
}

function update($tbl_name, $field_arry, $filter_sql)
{
	//===================================
	//	Date:	8 August 2007
	//	Author:	Kent Lee
	//	Description:
	//	Get all the field in the array. Used key as the column name.
	//	$filter_sql = "id='5'";	e.g
	//===================================
	if(isempty($tbl_name) || !is_array($field_arry))
		return 0;

	$sql_str = "UPDATE `$tbl_name` SET ";

	foreach($field_arry as $key => $val)
	{
		if($this->escStr)
			$val = mysql_real_escape_string($val);
		$sql_str .= "`$key`='$val', ";
	}
	$sql_str = substr($sql_str, 0, strlen($sql_str)-2);

	$sql_str .= " WHERE $filter_sql;";

	// RUN SQL...
	$sql_name = "_sqlupdate_";
	$temp_sqlname = $this->qrypointer;

	$this->sql[$sql_name] = $sql_str;
	$this->GetQuery($sql_name);

	$this->qrypointer = $temp_sqlname;	//restore back the pointer.

	if($this->GetAffectedRow())
		return 1;
	else
		return 0;
}
}

 

Usage

 

Simple sql:
<?php
include("db.class.php");
$db = new db();

$db->sql["sql_1"] = "SELECT * FROM table_1";
$db->GetQuery("sql_1"); //run the sql statement

$db->sql["sql_2"] = "SELECT * FROM table_2";
$db->GetQuery("sql_2"); //run the another sql statement

$db->qrypointer = "sql_1"; //point back to sql_1
echo $db->GetRow();  //Print out the number of records.
?>

Insert record
<?php

$db->insert("table_name", array(
                                            "field_1" => "val_1",
                                            "field_2" => "val_2",
                                            "field_3" => "val_3"
                                            ));
// insert data to the `table_name`
?>

Update record
<?php
$db->update("table_name", array(
                                            "field_1" => "val_1",
                                            "field_2" => "val_2",
                                            "field_3" => "val_3"
                                            ),
                                            "id='1'"
                                            );
?>

 

I know the usage is abit blur. Im not good in preparing tutorial. Any question please post over here. I will glad to answer. ^^

 

Try out!

[php_tom]

What kind of SQL statments does your code implement (LIMIT, WHERE, SUM(), AVG(), LIKE, DROP ?)

And do you support all the standard types (INT, CHAR, VARCHAR, TIME, etc.)?

 

I'm very interested in your code. I've been using PHP-TXT-DB-API (http://www.c-worker.ch/txtdbapi/index_eng.php) (on servers which don't have SQL) which frankly stinks. It's convoluted, only supports some standard keywords, and has limited types. Plus, you can't DROP DATABASE ... and stuff.

 

You should do a full SQL parser, I think that would be very useful for lots of people! I tried it a while ago, but ended up being busy with other stuff...

 

It looks like your code (posted below) doesn't actually store the data in a file or anything. What is it's use for?

 

Thanks,

Tom

[xenophobia]

It actually accept all kind of SQL statement. I just pass the SQL statement to the mysql_query() function to do the query.

 

This:

$db->sql

is an array to store all your sql statement.

This:

$db->qry

is also an array to store all your query that had been executed.

 

so instead of using this:

$sql = "sql statement";

 

you use this:

$db->sql["your_sql_name"] = "sql_statement";

 

The good things is you can get back your query after you executed your sql statement.

 

This code will executed the query:

$db->GetQuery("your_sql_name");

 

This code is get the query and store into an variable:

$qry = $db->qry["your_sql_name"];

 

You can even do this:

echo mysql_num_rows($db->qry["your_sql_name"]);

 

you can also get back your previous sql statement:

echo $db->sql["your_sql_name"];

 

I also provided some function in the class:

This is to get the number of rows after you executed an sql statement.

db::GetRow("your_sql_name")

 

This is to get the field value of the database table.

db::GetResult("field_name", row_number)

Example:

$db->sql["1"] = "SELECT * FROM users;";
$db->GetQuery("1");
$db->GetResult("name", 2);  //retrieve record number 3 of the name.

$db->sql["1"] = "SELECT * FROM foods;";
$db->GetQuery("1");
$db->GetResult("type");      //retrieve first record (0) of the type in the foods table.

$db->qrypointer = "name";  //point back to first query.
$db->GetResult("name", 1); //retrieve record number 2 of the name.

 

This is to ease the programmer when inserting a record to a table.

The key(index) of the array will be the name of your table's column name.

db::insert

Example:

$field_array = array("field_1"=>"value_1", "field_2"=>"value_2", "field_3"=>"value_3");
$db->insert("table_name", $field_array);

 

This function is similar with the insert. Only that it required one more parameter, the filter argument.

id='1' will converted to sql: WHERE id='1'.

db::update

Example:

$field_array = array("field_1"=>"value_1", "field_2"=>"value_2", "field_3"=>"value_3");
$db->insert("table_name", $field_array, "id='1'");

 

Basically this is all my database class do. Thanks you.