after I read gorillapoop's post,
http://p2p.wrox.com/topic.asp?TOPIC_ID=44613 , I tried the code, but it is not working for me. I do some modification for gorillapoop's code and make it works for me. The code I tested in WAMP Environment with MYSQL and assume that host = localhost, user = root, with no password, database_name = test
codes as following:
//--------------------------------------------------------------
// sql.php
<?php
class sql {
private $result_rows; # Result rows hash
private $query_handle; # db: the query handle
private $link_ident; # db: the link identifier
public function __construct() {
$hostname = 'localhost';
$username = 'root';
$password = '';
$database = 'test';
if (!$this->link_ident){
$this->link_ident=mysql_connect($hostname, $username, $password);
}
mysql_select_db($database);
}
public function query($sql, $code_return_mode = 0) {
$result=mysql_query($sql,$this->link_ident);
if (!$result){
error_log("QUERY FAILED: $sql\n");
echo ("QUERY FAILED: $sql\n");
return(0);
}else{
$num=mysql_numrows($result);
for ($i=0; $i<$num; $i++){
$return_array[$i] = mysql_fetch_array($result);
}
$this->result_rows = $return_array;
}
return(1);
}
public function get_result($row_num, $column_name) {
return ($this->result_rows[$row_num][$column_name]);
}
public function get_row_hash($row_num) {
return ($this->result_rows[$row_num]);
}
public function get_table_hash() {
return $this->result_rows;
}
public function done($close_connection = 0) {
if ($close_connection) {
mysql_close ($this->link_ident);
}
}
};
?>
//--------------------------------------------------------------
// GenericObject.php
<?php
require_once 'sql.php';
$debug = 1;
class GenericObject {
# Member Variables
private $id;
private $table_name;
private $database_fields;
private $loaded;
private $modified_fields;
# Methods
public function Reload() {
$sql = new sql(0);
$id = $this->id;
$table_name = $this->table_name;
$sql->query("SELECT * FROM ".$table_name ." WHERE id=".$id."");
//for debug only --------------------------------------------
if($debug){
print "SELECT * FROM ".$table_name ." WHERE id=".$id."";
}
//-----------------------------------------------------------
$result_fields = $sql->get_row_hash(0);
$this->database_fields = $result_fields;
$this->loaded = 1;
if (sizeof($this->modified_fields) > 0) {
foreach ($this->modified_fields as $key => $value) {
$this->modified_fields[$key] = false;
};
};
}
private function Load() {
$this->Reload();
$this->loaded = 1;
}
public function ForceLoaded() {
$this->loaded = 1;
}
public function GetField($field) {
if ($this->loaded == 0) {
$this->Load();
};
return $this->database_fields[$field];
}
public function GetAllFields() {
if ($this->loaded == 0) {
$this->Load();
};
return($this->database_fields);
}
public function GetID() {
return $this->id;
}
public function Initialize($table_name, $tuple_id = "") {
$this->table_name = $table_name;
$this->id = $tuple_id;
}
public function SetField($field, $value) {
if ($this->loaded == 0) {
if ($this->id) {
$this->Load();
};
};
$this->database_fields[$field] = $value;
$this->modified = 1;
$this->modified_fields[$field] = true;
}
public function Destroy() {
$id = $this->id;
$table_name = $this->table_name;
if ($id) {
$sql = new sql(0);
$stmt = "DELETE FROM " . $table_name . " WHERE id=\'" . $id . "\'";
$sql->query($stmt);
};
}
public function Save() {
$id = $this->id;
$table_name = $this->table_name;
$sql = new sql(0);
if (!$id) {
$this->loaded = 0;
};
if ($this->loaded == 0) {
# assume this is a new entity
$stmt = "INSERT INTO " . $table_name ." (";
foreach ($this->database_fields as $key => $value) {
if (!is_numeric($key)) {
$key = str_replace("'", "\'", $key);
if ($value != "") {
$stmt .= $key.",";
};
};
};
# Chop last comma
$stmt = substr($stmt,0,strlen($stmt)-1);
$stmt .= ") VALUES (";
foreach ($this->database_fields as $key => $value) {
if (!is_numeric($key)) {
if ($value != "") {
$value = str_replace("'", "\'", $value);
$stmt .= "'$value',";
};
};
};
# Chop last comma
$stmt = substr($stmt,0,strlen($stmt)-1);
$stmt .= ")";
} else {
$stmt = "UPDATE " . $table_name ." SET ";
foreach ($this->database_fields as $key => $value) {
if (!is_numeric($key)) {
if ($this->modified_fields[$key] == true) {
$value = str_replace("'", "\'", $value);
if ($value == "") {
$stmt .= $key." = NULL, ";
} else {
$stmt .= $key." = '$value', ";
};
};
};
};
# Chop last comma and space
$stmt = substr($stmt,0,strlen($stmt)-2);
$stmt .= " WHERE id='$id'";
};
$return_code = $sql->query($stmt, 1);
if ($this->loaded == 0) {
# Try to get the ID of the new tuple.
$stmt = "SELECT MAX(id) AS id FROM $table_name WHERE ";
foreach ($this->database_fields as $key => $value) {
if (!is_numeric($key)) {
if ($value) {
if ($this->modified_fields[$key] == true) {
$value = str_replace("'", "\'", $value);
$stmt .= "\"$key\" = '$value' AND ";
};
};
};
};
# Chop last " AND " (superfluous)
$stmt = substr($stmt,0,strlen($stmt)-5);
$sql->query($stmt);
$result_rows = $sql->get_table_hash();
$proposed_id = $result_rows[0]['id'];
if ($proposed_id > 0) {
$this->loaded = 1;
$this->id = $proposed_id;
return true;
} else {
return false;
};
};
return($return_code);
}
};
//--------------------------------------------------------------
// test_sql.php
//this is for test the sql class
<?php
require_once 'sql.php';
$sql = new sql();
$sql->query("SELECT * FROM user");
$result_rows = $sql->get_table_hash();
for($i = 0;$i < sizeof($result_rows)-1; $i++){
print ("Data Type : " . $result_rows[$i]."\n<br>");
print ("ID : " . $result_rows[$i]['id'] . "\n<br>");
print ("UserName : " . $result_rows[$i]['username'] . "\n<br>");
print ("FirstName : " . $result_rows[$i]['first_name'] . "\n<br>");
print ("LastName : " . $result_rows[$i]['last_name'] .
"\n<br>==========================<br><br>");
}
$sql->done(1);
?>
//--------------------------------------------------------------
// user.php
<?php
require_once 'GenericObject.php';
class User extends GenericObject {
public function __construct($id) {
$this->initialize("user", $id);
}
}
?>
//--------------------------------------------------------------
// userteset.php
// this is for user test
<?php
#require_once("GenericObject.php");
require_once("user.php");
$objUser = new User(1);
$strUsername = $objUser->GetField("last_name");
print $strUsername;
?>
//--------------------------------------------------------------
// user.sql
// this is MYSQL Statement for user table,
// I use this in phpAdmin
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(32) collate utf8_unicode_ci NOT NULL,
`first_name` varchar(64) collate utf8_unicode_ci NOT NULL,
`last_name` varchar(64) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;
INSERT INTO `user` (`id`, `username`, `first_name`, `last_name`) VALUES
(1, 'ed', 'Ed', 'Lecky-Thompson'),
(2, 'steve', 'Steve', 'Nowicki'),
(3, 'alec', 'Alec', 'Cove'),
(4, 'heow', 'Heow', 'Eide-Goodman'),
(5, 'john', 'John', 'Doe'),
(6, 'jane', 'Jane', 'Doe');
//------------------------------------------------------
I hope this can help.
Thanks.
[email protected]
Chapter 7 for MySQL
