class_mysql.php
<?php
//######################################################################
//##### title :: class mysql
//##### file :: class_mysql.php
//##### project :: webvision
//##### related document :: none
//##### description ::
//##### to provide access utility for mysql access
//##### rundb is used to run sql query with the result
//##### grouped into array.
//##### author :: mark quah
//##### revision ::
//######################################################################
class mysql
{
var $no_rows=0, $row=array();
var $no_fields=0, $field=array();
//#-----------------------------------------------------------------
//#---- function :: mysql($p_host, $p_user, $p_passwd, $p_db="mysql")
//#---- description ::
//#---- initialize class with information to access server
//#---- no connection will be made at this point.
//#---- input ::
//#---- p_host : server hostname|ip address
//#---- p_user : user name to log into server
//#---- p_passwd : passwd for the user
//#---- p_db : database to be used
//#---- output ::
//#---- none
//#-----------------------------------------------------------------
function mysql($p_host, $p_user, $p_passwd, $p_db="mysql")
{
$this->sql_host = $p_host;
$this->sql_user= $p_user;
$this->sql_passwd = $p_passwd;
$this->sql_db = $p_db;
} // end mysql
//#-----------------------------------------------------------------
//#---- function :: rundb($statement, $exp_result = "")
//#---- description ::
//#---- execute a mysql statement in a non-persistant mode
//#---- input ::
//#---- p_statement : statement to be executed
//#---- exp_result : is result expected?
//#---- value 1 (default): result stored in row array
//#---- value 0: result not stored in row array
//#---- output ::
//#---- return "ok" : succesful
//#---- return err_message from mysql_connect
//#---- exp_result==1: additional result stored into array row
//#---- no_row contains no. of record retrieved
//#---- row[recno][ "field" ] contains value of recno record
//#---- field["fieldname"] contains the field list
//#-----------------------------------------------------------------
function rundb($p_statement, $exp_result = 1)
{
//--- connect to the database
$link=mysql_connect($this->sql_host, $this->sql_user, $this->sql_passwd);
if (!$link)
return sprintf("error connecting to host %s, by user %s",
$this->sql_host, $this->sql_user) ;
//--- select the database
if (!mysql_select_db($this->sql_db, $link))
{ $err_msg=sprintf("error in selecting %s database",
$this->sql_db);
$err_msg .= sprintf("error:%d %s", mysql_errno($link),
mysql_error($link));
return $err_msg;
}
//--- execute the statement
if (!($this->result=mysql_query($p_statement, $link)))
{ $err_msg=sprintf("error in selecting %s database/n",
$this->sqldb);
$err_msg .= sprintf("/terror:%d/t/nerror message %s",
mysql_errno($link), mysql_error($link));
return $err_msg;
}
//--- organize the result
if ($exp_result == 1)
{ $this->no_rows = mysql_num_rows($this->result);
$this->groupresult();
}
//--- success return
return ok;
} // end function rundb
//#-----------------------------------------------------------------
//#---- function :: groupresult( )
//#---- description ::
//#---- to group the raw result retrieved in an associative array
//#---- a query has to be made using rundb prior to this execution
//#---- the handle is storedin $result
//#---- input :: none
//#---- output :
//#---- return none
//#---- additional result stored into array
//#---- no_row, row[recno]["field"] = value
//#---- no_field, field["fieldname"]
//#-----------------------------------------------------------------
function groupresult()
{
//--- get result
$is_header = false;
for ( $recno = 0; $recno < $this->no_rows; $recno ++ )
{ $row = mysql_fetch_object($this->result);
//--- get field list
if ( ! $is_header )
{ $no_fields = 0;
$t_row = $row;
while ( $item = each($t_row) )
{ $this->field[$no_fields] = $item["key"];
$no_fields ++;
}
$this->no_fields = $no_fields;
$is_header = true;
}
//---- get data
while ( $item = each($row))
$this->row[$recno][$item["key"]] = $item["value"];
}
//--- end connection
mysql_free_result($this->result);
} // groupresult
//#-----------------------------------------------------------------
//#---- function :: showhtml($p_table="", $p_header = "", $p_cell = "")
//#---- description ::
//#---- to return the result in html table format
//#---- input ::
//#---- p_table : html <table> format
//#---- p_header : first row format
//#---- p_cell : individual cell format
//#---- output ::
//#---- "ok" : succesful
//#---- err_message from mysql_connect
//#-----------------------------------------------------------------
function showhtml($p_table="", $p_header="", $p_cell="" )
{
//--- default option
$p_table=($p_table=="")?"bgcolor=#bb9999 border=1": $p_table;
$p_header=($p_header=="")? "bgcolor=#9999bb" : $p_header;
$p_cell=($p_cell=="")?"bgcolor=#99bb99":$p_cell;
//--- display table
echo "<table ".$p_table.">";
//--- display header line
echo "<tr ".$p_header.">";
echo "<td>recno";
for ($i = 0; $i < $this->no_fields; $i ++)
printf("<td>%s", $this->field[$i]);
//--- display data
for ( $i = 0; $i < $this->no_rows; $i ++)
{ echo "<tr $p_cell>";
printf("<td>%-3s", $i);
for ($f = 0; $f < $this->no_fields; $f ++)
{ $f_name = $this->field[$f];
$f_value = $this->row[$i][$f_name];
if ( $f_value=="" )
$f_value=" ";
printf("<td>%s", $f_value);
}
}
//--- the end
echo "</table>";
} // showhtml
} // end class mysql
?>
例子:
<?php
include("class_mysql.php");
//===== set up sql connection
$mysql=new mysql("server", "mysql userid", "mysql passwd", "mysql db");
//==== extract result
$status = $mysql->rundb("select * from user;");
if ($status != "ok")
{ echo "<hr>db error: $status.<hr>";
die;
}
for ($i = 0; $i < $mysql->no_rows; $i ++)
{
echo "record no: " . ($i + 1) ."<hr>";
for ($j = 0; $j < $mysql->no_fields; $j ++)
{
$field_name = $mysql->field[$j];
echo "field: ".$field_name." ----- value: ".
$mysql->row[$i][$field_name]."<br>";
}
}
//==== use the built-in showhtml format
$status = $mysql->rundb("select * from user;");
if ($status != "ok")
{ echo "<hr>db error: $status.<hr>";
die;
}
$mysql->showhtml("","","center");
//==== run some query not expecting results
$stmt = ("fill in your staement eg. insert into");
$status = $myql->rundb($stmt, 0);
if ($status
if ($status != "ok")
{ echo "<hr>db fail: $status.<hr>";
die;
}
else
{ echo "<hr>success: $status.<hr>";
die;
}
?>
新闻热点
疑难解答
