ติดต่อกับ Mysql
ส่วนนี้คงไม่ต้อง อธิบายมากมายนะครับ แค่เป็นส่วนที่ต่อยอดจากการใช้งานฟังก์ชั่น mysql ใน php เฉยๆ เขียนให้มันทำงานได้กระชับมากขึ้นเท่านั้นเอง
MySql Class
Code
MySql.php
<?php
/**
* Mysql class database server for PHP5+
* @package Databases
*/
final class MySql {
/**
* @access Public
* @var string
*/
public $strServerName;
/**
* @access Public
* @public string
*/
public $strUserName;
/**
* @access Public
* @public string
*/
public $strUserPassword;
/**
* @access Public
* @public string
*/
public $strDatabaseName;
/**
* @access Private
* @public resource
*/
public $resConn;
/**
* @access Public
* @public string
*/
public $strSql;
/**
* @access Public
* @public resource
*/
public $resResult;
/**
* @access Private
* @public string
*/
public $rs;
/**
* @access Public
* @public integer
*/
public $intNumRows;
public $intNumColumn;
/**
* @access Public
* @public array
*/
public $fetch;
/**
* @access Public
* @public integer
*/
public $intAffretedRows;
/**
* @access Public
* @public integer
*/
public $intState;
/**
* @
* @return MySql
*/
function __construct() {
$this->strServerName = "";
$this->strUserName = "";
$this->strUserPassword = "";
$this->strDatabaseName = "";
$this->resConn = null;
$this->strSql = "";
$this->resResult = null;
$this->rs = "";
$this->intNumRows = 0;
$this->fetch = Array ();
$this->intAffretedRows = 0;
$this->intState = 0;
}
/**
* @access Public
* @return integer
*/
function openConnection() {
if ($this->intState == 0) {
$this->resConn = @mysql_connect ( $this->strServerName, $this->strUserName, $this->strUserPassword );
if ($this->resConn) {
$this->intState = 1;
}
return $this->intState;
} else {
$this->intState = 0;
return $this->intState;
}
}
/**
* @access Public
* @return boolean
*/
public function selectDatabase() {
if ($this->intState == 1) {
if (mysql_select_db ( $this->strDatabaseName, $this->resConn ) == true) {
return true;
} else {
return false;
}
} else {
return false;
}
}
/**
* @access Public
* @return integer
*/
public function closeDB() {
if ($this->intState == 1) {
mysql_close ( $this->resConn );
$this->intState = 0;
return $this->intState;
} else {
$this->intState = 1;
return $this->intState;
}
}
/**
* @access Public
* @return integer
*/
public function numFields() {
$i_num_fields = mysql_num_fields ( $this->resResult );
return $i_num_fields;
}
/**
* @access Public
* @param string $strSql
* @return resource
*/
public function executeQuery($s_sql, $b_use_charset = false, $s_charset = 'UTF-8') {
if ($b_use_charset === true)
@mysql_query ( "SET NAMES $s_charset" );
$this->resResult = @mysql_query ( $s_sql, $this->resConn );
if ($this->resResult) {
if (self::countRecord () > 0) {
self::fetchArray ();
}
} else {
print "SQL TEXT : " . $s_sql;
}
}
public function executeUpdate($s_sql) {
$i_affected = null;
$this->resResult = @mysql_query ( $s_sql, $this->resConn );
if ($this->resResult == true) {
$i_affected = self::rowAffreated ();
} else {
print "SQL TEXT : " . $s_sql;
}
return $i_affected;
}
/**
* @access Private
* @return integer
*/
public function countRecord() {
@$i_num_rows = mysql_num_rows ( $this->resResult );
return $i_num_rows;
}
public function countField() {
@$i_num_column = mysql_num_fields ( $this->resResult );
return $i_num_column;
}
/**
* @access Public
* @param integer $indexRow
* @param integer $indexField
* @return string
*/
public function indexGrid($indexRow, $indexField) {
$s_field_value = mysql_result ( $this->resResult, $indexRow, $indexField );
return $s_field_value;
}
/**
* @access Public
* @return array
*/
public function fetchArray() {
$this->fetch = mysql_fetch_array ( $this->resResult );
return $this->fetch;
}
/**
* @access Public
* @param integer $intIndexColumn
* @return string
*/
public function fieldName($intIndexColumn) {
$s_field_name = mysql_field_name ( $this->resResult, $intIndexColumn );
return $s_field_name;
}
/**
* return effect of sql on commond insert update and delete
*
* @access public
* @return integer
*/
public function rowAffreated() {
$i_affreted_rows = mysql_affected_rows ( $this->resConn );
return $i_affreted_rows;
}
}
?>
ตัวอย่างการใช้งาน คลาส MySql
require_once 'MySql.php';
header ( 'Content-Type: text/xml; charset=utf-8' );
# create new object.
$objMySql = new MySql ( );
# set server name.
$objMySql->strServerName = STR_NAME_SERVER;
# set username.
$objMySql->strUserName = STR_USER_NAME;
# set password.
$objMySql->strUserPassword = STR_PASSWORD;
# set database name.
$objMySql->strDatabaseName = STR_DATABASE_NAME;
# open connection.
$objMySql->openConnection ();
# open database.
$objMySql->selectDatabase ();
# define sql commnad.
$s_sql = "select * from tbl_userlogin;";
# on case select use $objMySql->executeQuery();
# on case insert, delete ,update use $objMySql->executeUpdate();
$objMySql->executeQuery ( $s_sql );
# count record from data of query
$intCountJob = $objMySql->countRecord ();
# show data of query by $objMySql->indexGrid(index row,index column or string name of column);
# count column of query by $objMySql->countField ();
# show field name of column by $objMySql->fieldName ( index Column)
print "<?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n";
print "<tbl_userlogin totalItems =\"$intCountJob\" itemsFound=\"$intCountJob\">\r\n";
# loop record by data from job.
for($intRecordLoop = 0; $intCountJob > $intRecordLoop; $intRecordLoop ++) {
print "<record user_id=\"" . trim ( $objMySql->indexGrid ( $intRecordLoop, 'user_id' ) ) . "\">\r\n";
$intCountColumns = $objMySql->countField ();
#loop column.
for($intColumnLoop = 0; $intCountColumns > $intColumnLoop; $intColumnLoop ++) {
print "<" . $objMySql->fieldName ( $intColumnLoop ) . ">" . trim ( $objMySql->indexGrid ( $intRecordLoop, $objMySql->fieldName ( $intColumnLoop ) ) ) . "</" . $objMySql->fieldName ( $intColumnLoop ) . ">\r\n";
}
print "</record>\r\n";
}
print "</tbl_userlogin>\r\n";
# close database connection.
$objMySql->closeDB ();
ผลลัพธ์จากตัวอย่าง การใช้งาน Mysql Class ข้างต้น
Load balance : Server 02
