StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
11119961
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
1
CommunityOwnedDate
CreationDate
2012-06-20T12:49:54.890
FavoriteCount
0
LastActivityDate
2012-06-20T12:49:54.890
LastEditDate
LastEditorUserId
0
OwnerUserId
1112551
ParentId
11119430
PostTypeId
2
Score
-1
ViewCount
0
LastEditorDisplayName
text
Body
This is a personal account of how I approach designing my own framework classes when working with a database. Typically, when designing an MVC framework, I lay out two core concepts for developing the classes: <ul> <li>a single database class that manages various aspects such as query building, row count return, insert ID, etc.</li> <li>an interface data class to ensure that each class remains consistent (for example, each class must have all four <a href="http://en.wikipedia.org/wiki/Create,_read,_update_and_delete" rel="nofollow">CRUD</a> operations)</li> </ul> Database class My database class usually looks something like the following. You will notice towards the bottom that there are mutator functions like you have specified. These are specifically designed only to set and get data from the class itself. There is minor validation (<code>if(is_resource(...))</code> in get_connection), though I have heard that this sort of code should be in the Model level. <pre><code> public $error; private $location = ''; private $username = ''; private $password = ''; private $database = ''; public function __construct() { $connection = mysql_connect($this->location,$this->username,$this->password); $this->set_connection($connection); $db_select = mysql_select_db($this->database,$this->get_connection()); } public function query() { $query = mysql_query($this->query,$this->connection); $this->set_insert_id(mysql_insert_id($this->connection)); switch(substr(strtolower($this->query),0,6)) { case 'select': $this->set_row_count(mysql_num_rows($query)); $row_count = $this->get_row_count(); if($row_count == 0) { $this->set_results(0); } else { while($result = mysql_fetch_assoc($query)) { $results = $this->get_results(); $results[] = $result; $this->set_results($results); } } break; case 'insert': case 'update': case 'delete': case 'replace': default: $this->set_results(($query === true)); break; } } public function get_query() { return $this->query; } public function set_query($query) { $this->query = $query; } public function get_insert_id() { return $this->insert_id; } public function set_insert_id($insert_id) { if(is_numeric($insert_id)) { $this->insert_id = $insert_id; } } public function get_results() { return $this->results; } public function set_results($results) { $this->results = $results; } public function get_row_count() { return $this->row_count; } public function set_row_count($row_count) { if(is_numeric($row_count)) { $this->row_count = $row_count; } } public function get_connection() { return $this->connection; } public function set_connection($connection) { if(is_resource($connection)) { $this->connection = $connection; } else { $this->connection = false; } } } ?> </code></pre> Data class The interface data class is simply a guideline for other classes, so its very straightforward. When doing this approach, it's easy to manipulate with your data. <pre><code><?php interface Data { public function create(); public function read(); public function update(); public function delete(); } ?> </code></pre> An example class Here is an example class that I would typically use with the previous stated classes that manipulates the Database and Data classes. When using a typical CRUD operation, I do a call to the Database class, set the query, query the database, then return the results. That's as simple as it can get, and any other further requirements can be appended into each function. <pre><code><?php require_once('classes/class.Data.php'); require_once('classes/class.Database.php'); class Settings implements Data { private $profile_id; private $settings = array(); public function create() { $settings_instance = new Database; $sql = 'INSERT INTO settings (' . implode(',',array_keys($this->settings)) . ') VALUES ("' . implode('","',array_values($this->settings)) . '");'; $settings_instance->set_query($sql); $settings_instance->query(); return $settings_instance->get_results(); } public function read() { $settings_instance = new Database; $sql = 'SELECT * FROM settings WHERE profile_id = "' . $this->profile_id . '"'; $settings_instance->set_query($sql); $settings_instance->query(); $settings_results = $settings_instance->get_results(); if($settings_results) { $this->set_settings($settings_results[0]); } } public function update() { $keys = array_keys($this->settings); $values = array_values($this->settings); $settings_instance = new Database; $sql = 'UPDATE settings SET '; foreach($keys as $index => $key) { if($keys[$index] == "profile_id") continue; $sql .= $keys[$index] . ' = "' . $values[$index] . '", '; } $sql = rtrim($sql,', '); $sql .= ' WHERE profile_id = "' . $this->profile_id . '"'; $settings_instance->set_query($sql); $settings_instance->query(); return $settings_instance->get_results(); } public function delete() { $settings_instance = new Database; $sql = 'DELETE FROM settings WHERE profile_id = "' . $this->profile_id . '"'; $settings_instance->set_query($sql); $settings_instance->query(); return $settings_instance->get_results(); } public function get_profile_id() { return $this->profile_id; } public function set_profile_id($profile_id) { if(is_numeric($profile_id)) { $this->profile_id = $profile_id; } } public function get_setting($index) { if(isset($this->settings[$index])) { return $this->settings[$index]; } } public function set_setting($index,$new_value) { if(isset($this->settings[$index])) { $this->settings[$index] = $new_value; } } public function get_settings() { return $this->settings; } public function set_settings($settings) { if(is_array($settings)) { $this->settings = $settings; } } public function get_setting_types() { return $this->setting_types; } public function set_setting_types($setting_types) { if(is_array($setting_types)) { $this->setting_types = $setting_types; } } } ?> </code></pre> Some example code Here is some example Model code to go along with this approach. I find this to be quite readable. It will create a new settings row in the database with the profile_id = 1. <pre><code>require_once('class.Settings.php'); $Settings = new Settings; $Settings->set_profile_id(1); $Settings->create(); </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POAm i doing SQL Statements in my models classes correctly?
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USZack Zatkin-Gold
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POAm i doing SQL Statements in my models classes correctly?
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTDownMod
CommentsPostId
1. COthank you ..that helps. I assume that your data class is similar to the sql class that i mentioned right?
 singulars
 PostPostId
 PO
 UserUserId
 USSlay

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.