6dc0e9a07bcff97ac9b111f36e12f1f6

What I am trying to do is a little hard to explain, but basically I want to map database fieldnames to an object, corresponding to table name.

So `users` would be used in a class called Users, etc.

I've managed to query the database and stick the fieldnames into an associative array with the code below, but how I'm going to create the object is slightly above me right now.

As of now I have a parent class that im trying to get to handle the querying of field names, and im extending it to create the different classes.

Table

1
2
3
4
5
6
7
8
9
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| user_id  | int(11)     | NO   | PRI | NULL    | auto_increment | 
| username | varchar(20) | YES  |     | NULL    |                | 
| password | varchar(32) | YES  |     | NULL    |                | 
| email    | varchar(50) | YES  |     | NULL    |                | 
+----------+-------------+------+-----+---------+----------------+

Query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php

	$db = mysql_connect('localhost', 'root', 'rootpass');
	mysql_select_db('dname', $db);	
	
	$query = "SHOW COLUMNS FROM users;";
	$result = mysql_query($query, $db);
	while($row = mysql_fetch_assoc($result))
	{
		$fields[] = $row['Field'];
	}
	var_dump($fields);

?>

Result Array

1
2
3
4
5
6
7
8
9
10
11
array(4) {
  [0]=>
  string(7) "user_id"
  [1]=>
  string(8) "username"
  [2]=>
  string(8) "password"
  [3]=>
  string(5) "email"
}

Overall Goal

1
2
3
4
5
6
7
//dynamically made class
class user {
	public $user_id;
	public $username;
	public $password;
	public $email;
}

Refactorings

No refactoring yet !

330555fba743b009765f9d38d7137e6f

Scott Reynen

July 28, 2008, July 28, 2008 20:01, permalink

No rating. Login to rate!

Are you trying to create a class or an object? Your comment says an object, but your "overall goal" suggests a class. If class, why? The class would have no functionality beyond what StdClass already has. If object, see documentation for mysql_fetch_object(). If you're trying to create an object of a previously-defined class with functionality beyond StdClass, see code below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
class generic_class {
	
	function generic_class( $object = NULL ) 
	{
				
		if ( $object !== NULL )
		{

			while( list( $key , $value ) = each( $object ) )
				$this->{ $key } = $value;
			
		} // if
			
		if ( ! isset( $this->table ) )
			$this->table = get_class( $this );
			
	} // generic_class

} // generic_class

class users extends generic_class { 

	function send_email()
	{

		mail( $this -> email , .... );

	} // send_email

} // users

$query = 'SELECT * FROM users';
$result = mysql_query($query, $db);

while ( $user_std_class_obj = mysql_fetch_object( $result ) ) 
{

	$user = new users( $user_std_class_obj );
	$user -> send_email();

} // while
689ba40e7bbd6c630d4616060f7baa37

prengel

July 28, 2008, July 28, 2008 20:03, permalink

No rating. Login to rate!

What you are trying to accomplish is called object relational mapping (ORM).
One of these should help you: http://en.wikipedia.org/wiki/List_of_object-relational_mapping_software#PHP

6dc0e9a07bcff97ac9b111f36e12f1f6

Ishkur

July 28, 2008, July 28, 2008 20:16, permalink

No rating. Login to rate!

Sorry, I mixed up my terms. I am trying to map over to an object.

Below is sort of getting me closer, to where I was aiming at.

And Prengel, I'll look at that. Thank you.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
<?php

class ParentClass
{
	protected $database;
	protected $tables;
	//...
	
	public function __construct($table)
	{
		//get an instance of my own database class
		//and use it in this one.
		$this->database = DB::GetInstance();
		
		//set table name to query
		$this->table = $table;
	}
	
	//...
	//some magic
	//...
}

class User extends ParentClass
{
	public function __construct()
	{
		parent::__construct('users');
	}
}

$bob = new User();
$bob->username = 'bob';
//...
//insert new row with whatever specs
//i defined with $bob->[var] = [val].
$bob->create();

?>
B8d457d2c39911ea4c74ba7d66b9c3f7

Marco Valtas

July 29, 2008, July 29, 2008 13:51, permalink

2 ratings. Login to rate!

I did something similar not long ago. I needed one ORM in PHP but Doctrine (http://www.phpdoctrine.org/) was not a option because I had some restriction on my env. So I went and wrote some very light ORM.

The idea is similar with yours, since is kind a big code I will try to show the basic.

I have some classes AbstractTable, AbstractEntry, TableFactory and DBTypeMapper. I think is pretty straight forward their functions. The first catch is that TableFactory scans a subdir looking for a table definition, this because a table definition mention primary keys but not the columns.

TableFactory gives you a object if your table type, since this object is a AbstractTable, mostly operations in a table is centrilized in this class. If I get one query that is too specific for a table I just implement the method inside the table definition file.

I will paste a simple method of AbstractTable, find() which searchs for a entry based on table primary keys. Note that this method returns a object of type entry, so each table has his own class and each entry has too.

One final consideration is type mapping, I wanted to pass a object of type 'entry' to find similar entries and etc. So I wrote a simple type mapper based on Oracle´s types. In AbstractTable code look at method find_by_entry(), it receives one abstract entry and searchs the database for it, but since PHP isn´t strogly typed I had to figure out what type is the column before create the 'where' clause.

The catch with DBTypeMapper is that it fetches the meta information from Oracle when given a table name and use defined handlers for each type.

All works well for a lightweight ORM, I wouldn´t bet this kind of structure for a really big framework, not without some revisions. In the and I will paste what a sample of a code using this structure to be more clear.

Hope this helps.

TableFactory

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php

class TableFactory {

  public static function getTable($tableName) {

    $tableName = ucfirst(strtolower($tableName));

    if(include_once('tables/'.$tableName.'.php')) {
      return new $tableName;
    }
    else {
      throw new Exception('tabela '.$tableName.' não definida no diretório "tables/" ');
    }
  }
}
?>

A table definition

1
2
3
4
5
6
7
8
9
10
11
<?php
// table manip...
class Basic_login extends AbstractTable {

  protected $primary_key = 'LOGIN_ID';

}

class Basic_login_entry extends AbstractEntry { }

?>

AbstractTable

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
<?php

  public function find($id) {
    return $this->find_by($this->primary_key,$id);
  }

  public function find_by($column, $value) {

    $select = 'SELECT * FROM '.strtoupper(get_class($this)).
      ' WHERE '.strtoupper($column).' = '.$value;

    $stmt = $this->ocihelper->parse($select);

    $this->ocihelper->execute($stmt);

    return $this->_map_result($stmt);
  }

 protected function _map_result($stmt) {
    $entries = array();

    while($row = oci_fetch_assoc($stmt)) {

      // class represents a entry in table
      $entryClass = get_class($this); $entryClass .='_entry';
      $entry = new $entryClass;

      // map columns found to fields...
      foreach($row as $field => $value) {
        $property = strtolower($field);
        if(!oci_field_is_null($stmt,$field)) {
          $entry->$property = $value;
        }
      }
      $entries[] = $entry;
    }

    // return all entries found
    return $entries;
  }

  public function find_by_entry(AbstractEntry $entry) {

    $this->is_entry_eq_class($entry);

    $this->dbtypemapper->map($entry);

    $sql = "SELECT * FROM ".strtoupper(get_class($this)). " WHERE ";

    $terms;
    foreach($entry->get_fields() as $field) {
      $terms[] = strtoupper($field).'='.$entry->$field;
    }
    
    $sql .= implode(' AND ',$terms);

    $stmt = $this->ocihelper->parse($sql);

    $this->ocihelper->execute($stmt);

    return $this->_map_result($stmt);
  }
?>

DBTypeMapper

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
<?php

class DBTypeMapper {

  private $conn;

  public function __construct() {
    $this->conn = DBConnection::get()->handle();
  }

  public function map(AbstractEntry $entry) {

    // get the table name from entry object.
    $table_name = strtoupper(str_replace('_entry', '', get_class($entry)));

    if(!IsSet($this->cache[$table_name])) $this->load_table_schema($table_name);

    // calling handlers for each type found
    foreach($this->cache[$table_name] as $column => $type) {
      $property = strtolower($column);
      if(IsSet($this->handlers[$type]) && IsSet($entry->$property)) {
        $entry->$property = $this->handlers[$type]->map($entry->$property);
      }
    }
  }

  private function load_table_schema($table_name) {

    $table_name = strtoupper($table_name); // oracle uses upper case.

    $sql = "
      SELECT COLUMN_NAME, DATA_TYPE 
      FROM USER_TAB_COLUMNS
      WHERE TABLE_NAME='$table_name'";

    $stmt = oci_parse($this->conn,$sql);

    if (!$stmt) {
      $e = oci_error($this->conn);
      throw new Exception($e['message']);
    }

    $r = oci_execute($stmt, OCI_DEFAULT);

    if (!$r) {
      $e = oci_error($stmt);
      throw new Exception($e['message']);
    }

    while($row = oci_fetch_array($stmt)) {
      $this->cache[$table_name][$row['COLUMN_NAME']] = $row['DATA_TYPE'];
      $datatypes[$row['DATA_TYPE']] = true;
    }

    // load handlers for types found
    foreach(array_keys($datatypes) as $type) {
      $class_name = 'Map_'.$type;
      if(!IsSet($this->handlers[$type])) {
        if(class_exists($class_name)) {
          $this->handlers[$type] = new $class_name();
        }
        else {
          $this->handlers[$type] = new Map_GENERIC();
        }
      }
    }
  }

}

// Type mappers. Estas classes formatam os dados
// corretamente para o banco de dados conforme o esquema
// exportado pela tabela em user_tab_columns no ORACLE.
// Ex. uma data do tipo 10/02/2008 será formatada pelo 
// mapper Map_DATE para to_date('10/02/2008' 'dd/mm/yyyy')
interface TypeMapper {
  static function map($field);
  //static function unmap($field);
}

class Map_GENERIC implements TypeMapper {
  public static function map($field) {
    return $field;
  }
}

class Map_DATE extends Map_GENERIC implements TypeMapper {
  public static function map($field) {
    if($field == '') return;

    if(preg_match('/^(\d{2}\/\d{2}\/\d{4} \d{2}:\d{2}:\d{2})$/', $field, $m)) {
      return "TO_DATE('$m[1]','dd/mm/yyyy hh24:mi:ss')";
    }
    elseif(preg_match('/^(\d{2}\/\d{2}\/\d{4} \d{2}:\d{2})$/', $field, $m)) {
      return "TO_DATE('$m[1]','dd/mm/yyyy hh24:mi')";
    }
    elseif(preg_match('/^(\d{2}\/\d{2}\/\d{4} \d{2})$/', $field, $m)) {
      return "TO_DATE('$m[1]','dd/mm/yyyy hh24')";
    }
    elseif(preg_match('/^(\d{2}\/\d{2}\/\d{4})$/', $field, $m)) {
      return "TO_DATE('$m[1]','dd/mm/yyyy')";
    }
    elseif(preg_match('/^(\d{2}\/\d{2}\/\d{2})$/', $field, $m)) {
      return "TO_DATE('$m[1]','dd/mm/yy')";
    }
    elseif(preg_match('/^(\d{10})$/', $field, $m)) { // epoch time
      return "(TO_DATE('19700101','YYYYMMDD')+ $field/86400)";
    }
    else {
      throw new Exception("Don´t know how to convert ($field) to date for ORACLE.");
    }
  }
}

class Map_VARCHAR2 extends Map_GENERIC implements TypeMapper {
  public static function map($field) {
    $field = preg_replace("/'/","\'",$field); // escape '
    return "'$field'";
  }
}

?>

Sample of a insert

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?php

// $config_date, $serial_number and etc were previous given...

$t_conf   = TableFactory::getTable('single_conf');

$new_config_id = $t_conf->next_seq_value();

$new_entry = $t_conf->new_entry();

$new_entry->config_id                 = $new_config_id;
$new_entry->date                      = $config_date;
$new_entry->enable                    = 1;
$new_entry->serialnumber              = $serial_number;
$new_entry->username                  = $username;
$new_entry->source                    = $source;

$t_conf->insert($new_entry);


?>
6dc0e9a07bcff97ac9b111f36e12f1f6

Ishkur

July 31, 2008, July 31, 2008 06:45, permalink

No rating. Login to rate!

Going with that the previous guy mentioned, I look around at ORM solutions that would fit the bill. Just about everything I could find was entirely oversized and just added so much overhead it was terrifying. So i looked for a more lightweight solution and found some rather good software (http://coughphp.com/). CoughPHP seems to do what I want decently, so I'm going to see where that gets me.

E0e8a4042ff16abe01c8286eee70beb3

mynameisgoor

August 15, 2008, August 15, 2008 08:20, permalink

No rating. Login to rate!

You are not right.

Your refactoring





Format Copy from initial code

or Cancel