PHP Database connectivity
Snippet Menu
17
Mon. Oct. 9th, 2006 2:16 PM
Database Class with quick connectivity and useful database functio
//ClsDatabase_DB
// $db=new ClsDatabase_db();$db->ClsDatabase_conn();
class ClsDatabase__db{
function ClsDatabase_conn($dbname="##database_name##"){
mysql_connect ("localhost", "##username##", "##password##") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ($dbname);
}
function ClsDatabase_query($query){
$query = @mysql_query($query) or die(mysql_error());
return $query;
}
function ClsDatabase_rs($query) {
$query = @mysql_fetch_array($query);
return $query;
}
function auto_id($tbl,$field,$strCondition="") {
//GET THE MAXIMUM auto id of a table
$strCondition=$strCondition=="" ? "" : "where $strCondition";
$sql="Select MAX($field) as maxseq from $tbl $strCondition";
$rowsMAX = mysql_fetch_array(mysql_query($sql));
return $rowsMAX['maxseq'] + 1;
}
function insert($table,$arrFieldValues,$debug=0)
{
/*
INSERT record into database $table
Example:
$arrFieldValues=array("id" => 1, // leaving blank if this an auto increment field
"user_fname" => "John", //(or $_POST["user_fname"])
"user_lname" => "Doe");
insert("users",$arrFieldValues);
If debug is set to 1, sql statement will be display
*/
$arrFields=array_keys($arrFieldValues);
$arrValues=array_values($arrFieldValues);
$escVals=array();
foreach ($arrValues as $val)
{
if(!is_numeric($val)) $val="'".mysql_escape_string($val)."'";
$escVals[]=$val;
}
$sql="INSERT INTO $table (".join(', ',$arrFields).") VALUES(".join(', ',$escVals).")";
if($debug) echo $sql;
mysql_query($sql);
return mysql_insert_id();
}
function update($table,$arrFieldValues,$arrConditions,$debug=0)
{
/*
UPDATE record into database $table
Example:
$arrFieldValues=array("id" => 1, // leaving blank if this an auto increment field
"user_fname" => "John", //(or $_POST["user_fname"])
"user_lname" => "Doe");
$arrConditions=array("user_lname" => "Doe");
update("users",$arrFieldValues,$arrConditions);
*/
$arrUpdates=array();
foreach ($arrFieldValues as $field => $val)
{
if(!is_numeric($val)) $val="'".mysql_escape_string($val)."'";
$arrUpdates[]= "$field = $val";
}
$arrWhere=array();
foreach ($arrConditions as $field => $val){
if(!is_numeric($val)) $val="'".mysql_escape_string($val)."'";
$arrWhere[]= "$field = $val";
}
$sql="UPDATE $table SET ".join(', ',$arrUpdates)." WHERE ".join(' AND ',$arrWhere);
if($debug) echo $sql;
return mysql_query($sql);
}
function delete($table,$arrConditions)
{
$arrWhere=array();
foreach ($arrConditions as $field => $val)
{
if(!is_numeric($val)) $val="'".mysql_escape_string($val)."'";
$arrWhere[]= "$field = $val";
}
$sql="DELETE FROM $table WHERE ".join(' AND ',$arrWhere);
$hRes=mysql_query($sql);
return mysql_affected_rows($hRes);
}
function boolDuplicate($table,$arrConditions)
{
//CHECK for duplicate record with the same conditions
$arrWhere=array();
foreach ($arrConditions as $field => $val)
{
if(!is_numeric($val)) $val="'".mysql_escape_string($val)."'";
$arrWhere[]= "$field = $val";
}
$sql="SELECT * FROM $table WHERE ".join(' AND ',$arrWhere);
$hRes=mysql_query($sql);
if(mysql_num_rows(mysql_query($sql))>0)
return 1;
else
return 0;
}
function getOne($tbl,$field,$strCondition="",$debug=0)
{
// GET data for a single record
// EXAMPLE: getOne("users","user_fname","id=1")
$strCondition=$strCondition=="" ? "" : "where $strCondition";
$sql="Select $field as fieldReturn from $tbl $strCondition";
if($debug) echo $sql;
$rowsReturn = mysql_fetch_array(mysql_query($sql));
return $rowsReturn["fieldReturn"];
}
function getAllRows($sql,$debug=0)
{
//GET ALL RECORDS based on the $sql statement, return an array
if($debug) echo $sql;
$result=mysql_query($sql);
while($rows = mysql_fetch_array($result))
$rowsReturn[]=$rows;
return $rowsReturn;
}
function getRow($sql,$debug=0)
{
//GET 1 Record base on the $sql statment
if($debug) echo $sql;
return mysql_fetch_array(mysql_query($sql));
}
function getArray($tbl,$field,$strCondition="",$delimeter="#",$debug=0)
{
//GET a concatenate string with the delimeter
// EXAMPLE: getArray("users","user_fname","",",") => John,Mary
$strCondition=$strCondition=="" ? "" : "where $strCondition";
$sql="Select $field as fieldReturn from $tbl $strCondition ORDER BY $field";
if($debug) echo $sql;
$result=mysql_query($sql);
while($rowsReturn = mysql_fetch_array($result)) $lstReturn.=$rowsReturn["fieldReturn"].$delimeter;
return "#".$lstReturn;
}
function ClsDatabase_die(){ mysql_close(); }
}
Votes Up
Votes Down
Thank you for posting this code.
Below are couple of example
/*
I will use table "users" to illustrate
user_id
user_fname
user_lname
user_address
user_city
*/
$db= new ClsDatabase_db();
$db->ClsDatabase_conn();
/******************** Example 1:
Purpose: Get all data from table users where user_id is 1, and assign them to variable */
$rowUser=$db->getRow("SELECT * FROM users WHERE user_id=1");
foreach($rowUser as $key => $value)
if(!is_numeric($key)) eval("\$$key=\"$value\";"); //dirty trick to assign variable
/* End Example 1
The "eval" function evaluate all the field and assign $user_id,$user_fname,$user_lname, etc...with the correct value. The advantage for this method is if you have to add or delete fields in your table, you don't have to go back and change this code
******************/
/*************** Example 2: quickly check if a record exit */
if($db->getOne("users","user_fname","NAMEDOESNTEXIT")=="")
echo "Doesn't exit";
/*********************************************/
/*************** Add Update record *****************/
if($_POST["pageaction"]!=""){
$arrFieldValues=array("user_id" => $_POST["user_id"], // it's ok if this variable is blank, indicate a new record
"user_fname" => $_POST["user_fname"],
"user_lname" => $_POST["user_lname"],
"user_address" => $_POST["user_address"],
"user_city" => $_POST["user_city"]);
if($_POST["pageaction"]=="update")
$db->update("users",$arrFieldValues,array("user_id" => $_POST["user_id"])); //update record, handling and shipping are taken care in the function
else //insert data instead
$id=$db->insert("users",$arrFieldValues); //$id is the new insert_id
}
//DIRTY TRICK:
/* if you use a consistent prefix for your form such as "user_", then you can use this trick to gather data faster, and best of all dynamically:
foreach($_POST as $key => $value)
if(strstr($key,"user_")!="") $arrFieldValues[$key]=$value;
**********************/
/**************** GET all record ******************/
$arrUsers=$db->getAllRows("SELECT user_id,user_fname FROM users");
foreach($arrUser as $u){ //loop through the record
$lstOption.="<option value={$u["user_id"]}>{$u["user_fname"]}</option>";
}
/****************************************************/
/* DEBUG
getOne(),getRow(),getAllRows(),insert(),update() all have default debug option at the end set to 0, you can set it to 1 to view the sql statement it generated.
getOne($sql,1); getAllRows($sql,1);insert($table,$arrFieldValues,1); etc...
You can also use print_r to view the array generated by getRow() and getAllRows()
***********/
$ClsDatabase_die();