mysql| Package
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 and 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'll be made in 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 to 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 ' raw result retrieved in a associative array
#----A query has to is 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
?>
Example:
<?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 "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 "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 "Die
}
Else
{echo "Die
}
?>