Finally the wait is over. Black iD is into the final testing of the class we have been working on for the last 6 months. We are running it through the phpDocumentor and trying to make the best for you all. The next releases have been planned already. We will be fully supporting the current release in the next one. So we hope you enjoy this class a lot. The code for this class is available here. We will try to expand this class even more.
Another excellent news is that we are now registered as the developers at PEAR website. We are proud to be supporting a community that has worked so hard thus far to keep up the spirits high. We will try to contribute in each way we can to come up with the best pieces of code. For now please accept this beautiful piece of code
<?php
/* =====================================================================================================================
MySQL DATABASE ACCESS CLASS
--> WRITTEN BY JYOT VAKHARIA. [Black iD Solutions(www.blackidsolutions.com)].
--> Open Source GPL Lisence.
--> VERSION 1.1 Release 1
New
--> Added the insert Method.
--> Reduced the number of loops to make the class process faster and improve
upon speed when database size is large.
-->
===================================================================================================================== */
class db
{
var $uname, $pass, $host, $db, $conn, $con_db;
/* USEAGE : $db->connect('host','username','password','databasename');
OR
$db->connect(array('host','username','password','databasename'));
OF
$db->connect(array('host' => 'hostname', 'uname' => 'username', 'pass'=>'password', 'db'=>'db_name'));
*/
function connect()
{
$args = func_get_args();
if(is_array($args[0]))
$args = $args[0];
if(!($this->uname) || !($this->host) || !($this->db))
{
// LOOK OUT FOR CONNECTION SETTINGS IF NOT DEFINED
$this->host = $args['host'];
$this->uname = $args['uname'];
$this->pass = $args['pass'];
$this->db = $args['db'];
}
if(!($this->uname) || !($this->host) || !($this->db))
{
// LOOK OUT FOR CONNECTION SETTINGS IF NOT DEFINED
$this->host = $args[0];
$this->uname = $args[1];
$this->pass = $args[2];
$this->db = $args[3];
}
$this->conn = mysql_connect($this->host,$this->uname, $this->pass) or die("Could not connect to the databae");
$this->conn_db = mysql_select_db($this->db) or die("Could not select the database with the name " . $this->db);
return $this->conn_db;
}
// USAGE $db->query($sql);
// RETURNS THE RESULTANT DATASET.
function query($sql)
{
return mysql_query($sql);
}
// USAGE $db->getFieldsFor('table');
// RETURNS THE field name list as an array.
function getFieldsFor($tablename)
{
$sql = "Select * From $tablename LIMIT 1";
$fld = $this->getFieldsForSQL($sql);
return $fld;
}
// USAGE $db->getFieldsForSQL("Select * from foo where bar='foo'");
// RETURNS THE field name list as an array.
function getFieldsForSQL($sql)
{
$db = $this->query($sql);
$i = 0;
while($i < @mysql_num_fields($db))
{
$fld[] = mysql_field_name($db, $i);
$i++;
}
mysql_free_result($db);
return $fld;
}
// USAGE $db->table_array_from_tablename('table');
// RETURNS THE aray containing the entire table as an array.
// You can sort it as you like. It is currently keyed array.
// To get a pirticular field use $tbl = $db->table_array_from_tablename('table'); $fld = $dbl[rowno]['fldname'];
function table_array_from_tablename($tablename, $conds="1")
{
$sql = "Select * from $tablename WHERE $conds";
$table = $this->table_array_from_sql($sql);
return $table;
}
// USAGE $db->table_array_from_sql('Select * from tablename');
// RETURNS THE aray containing the entire table as an array.
// You can sort it as you like. It is currently keyed array.
// To get a pirticular field use $tbl = $db->table_array_from_sql('table'); $fld = $dbl[rowno]['fldname'];
function table_array_from_sql($sql)
{
$db = $this->query($sql);
$flds = $this->getFieldsForSQL($sql);
while($row = mysql_fetch_array($db,1))
{
$table[] = $row;
}
return $table;
}
function table_row_from_tablename($tablename, $conds="1")
{
$sql = "Select * from $tablename WHERE $conds";
$table = $this->table_array_from_sql($sql);
return $table[0];
}
// USAGE $db->table_array_from_sql('Select * from tablename');
// RETURNS THE aray containing the entire table as an array.
// You can sort it as you like. It is currently keyed array.
// To get a pirticular field use $tbl = $db->table_array_from_sql('table'); $fld = $dbl[rowno]['fldname'];
function table_row_from_sql($sql)
{
$row = $this->table_array_from_sql($sql);
return $row[0];
}
function field_val_from_table($table, $field_name, $crit_field, $crit_val)
{
$sql = "Select $field_name from $table where $crit_field='$crit_val'";
$res = $this->field_val_from_sql($sql);
return $res;
}
function field_val_from_sql($sql)
{
$res = mysql_fetch_array($this->query($sql));
return $res[0];
}
function flat_field_array($table, $field_name, $cond = " WHERE 1")
{
$sql = "SELECT $field_name FROM $table $cond";
$res = $this->query($sql);
while($row = mysql_fetch_array($res))
{
$arr[] = $row[$field_name];
}
return $arr;
}
function field_associative_array_from_table($tablename,$condition = "1",$fld_key,$fld_val,$val_seperator="-")
{
$tbl = $this->table_array_from_sql("SELECT $fld_key, $fld_val FROM $tablename WHERE $condition");
$flds = explode(",",$fld_val);
error_reporting(0);
foreach($tbl as $row)
{
foreach($flds as $fld)
{
$row_val .= ($row_val!="") ? " $val_seperator {$row[$fld]}" : $row[$fld] ;
}
$retval[$row[$fld_key]] = $row_val;
$row_val = "";
}
error_reporting(1);
return $retval;
}
////////////////////////// USAGE /////////////////////////////////////////////
// $db->insert("tablename","field1=value1","field2=value2",...); //
// $db->insert("tablename",array("field1=value1","field2=value2",...));//
// $db->insert("tablename","field1=value1;field2=value2;..."); //
//////////////////////////////////////////////////////////////////////////////
function insert()
{
$args = func_get_args();
$first_arg = $args[0];
if(!(is_array($first_arg)))
$first_arg = split(";", $first_arg);
$table = $first_arg[0];
$opts = $first_arg[1];
$opts = split(":",$opts);
$ret_fld = $opts[1];
$field_list = $this->getFieldsFor($table);
if(is_array($args[1]))
{
$args = $args[1];
}
elseif(count($args) == 2)
{
$args = split(";", $args[1]);
}
else
{
$args = array_splice($args,1);
foreach($args as $arg)
{
$temp_arg = split("=",$arg);
$args2[$temp_arg[0]] = $temp_arg[1];
}
$args = $args2;
}
$flds = "";
$vals = "";
if(array_keys($args))
{
foreach($args as $fld=>$val)
{
if(in_array($fld,$field_list) && (!($_FILES['$fld']['name'])))
{
if(is_array($val))
$val = join(";", $val);
$flds .= $flds ? ","."`".$fld."`" : "`".$fld."`";
$vals .= $vals ? ",'".$val."'" : "'".$val."'";
}
}
}
else
{
foreach($args as $arg)
{
$temp_arg = split("=",$arg);
$fld = "`".$temp_arg[0]."`";
if(in_array($fld,$field_list) && (!($_FILES['$fld']['name'])))
{
$val = str_replace("'","",$temp_arg[1]);
$flds .= $flds ? ",".$fld : $fld;
$vals .= $vals ? ",'".$val."'" : "'".$val."'";
}
}
}
$sql = "INSERT INTO $table ($flds) VALUES ($vals)";
$this->query($sql);
if(mysql_error())
{
return -1;
}
elseif($ret_fld)
{
$ret_sql = "Select " . str_replace("return","",str_replace("_","",$opts[0]))."($ret_fld) from $table";
$ret_val = $this->field_val_from_sql($ret_sql);
return $ret_val;
}
}
////////////////////////// USAGE /////////////////////////////////////////////
// $db->update("tablename","updating condition","field1=value1","field2=value2",...); //
// $db->update("tablename","updating condition",array("field1=value1","field2=value2",...));//
// $db->update("tablename","updating condition","field1=value1;field2=value2;..."); //
//////////////////////////////////////////////////////////////////////////////
function update()
{
$args = func_get_args();
$first_arg = $args[0];
$condition = $args[1];
if(!(is_array($first_arg)))
$first_arg = split(";", $first_arg);
$table = $first_arg[0];
$opts = $first_arg[1];
$opts = split(":",$opts);
$ret_fld = $opts[1];
$field_list = $this->getFieldsFor($table);
if(is_array($args[2]))
{
$args = $args[2];
}
elseif(count($args) == 2)
{
$args = split(";", $args[1]);
}
else
{
$args = array_splice($args,1);
foreach($args as $arg)
{
$temp_arg = split("=",$arg);
$args2[$temp_arg[0]] = $temp_arg[1];
}
$args = $args2;
}
$flds = "";
$vals = "";
if(array_keys($args))
{
foreach($args as $fld=>$val)
{
if(in_array($fld,$field_list) && (!($_FILES['$fld']['name'])))
{
if(is_array($val))
$val = join(";", $val);
$query .= ($query ? "," : "")."`$fld`='$val'";
}
}
}
else
{
foreach($args as $arg)
{
$temp_arg = split("=",$arg);
$fld = $temp_arg[0];
if(in_array($fld,$field_list) && (!($_FILES['$fld']['name'])))
{
$val = str_replace("'","",$temp_arg[1]);
// $flds .= $flds ? ",".$fld : $fld;
$query .= ($query ? "," : "")."`$fld`='".addslashes($val)."'";
// $vals .= $vals ? ",'".$val."'" : "'".$val."'";
}
}
}
$sql = "UPDATE $table SET $query where $condition";
$this->query($sql);
echo mysql_error();
if($ret_fld)
{
//$ret_sql = "Select " . str_replace("return","",str_replace("_","",$opts[0]))."($ret_fld) from $table";
//$ret_val = $this->field_val_from_sql($ret_sql);
//return $ret_val;
}
}
// Function to replace a pirticular string in each row in all fields of the table.
// created by binson asif
function replace_string_in_entire_table($tablename,$fields,$existing_string,$replace_string)
{
$flds= $this->getFieldsForSQL("SELECT $fields from $tablename");
echo mysql_error();
foreach($flds as $fld)
{
$sql="update $tablename set $fld = replace ($fld, '$existing_string', '$replace_string')";
mysql_query($sql);
}
return mysql_error();
}
}
?>
