note 95241 deleted from language.oop5.serialization by danbrown
| From: | danbrown@php.net | Date: | Mon, 21 Dec 2009 17:00:23 +0000 |
| Subject: | note 95241 deleted from language.oop5.serialization by danbrown | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-164187@lists.php.net to get a copy of this message | ||
Note Submitter: wbcarts at juno dot com
----
Using PHP Objects to maintain DATABASE TABLE SIZE
PHP Objects can keep the number of records in a database down to its smallest size. The interesting
thing about this is that it never submits a single DELETE query. Curious... isn't it? Without
further ado:
1. Put in a 'flags' column in the TABLE(S) you want to manage:
CREATE TABLE user (
user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_name VARCHAR(24) NOT NULL,
user_flags INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (user_id),
INDEX (user_name),
INDEX (user_flags)
);
2. Then create a PHP Class definition that will interface to the TABLE.
<?php
include('db_connect.php');
class User
{
protected $user_id;
protected $user_name;
protected $user_flags; // This is the property that we're interested in.
public function __construct($id, $name, $flags)
{
$this->user_id = $id;
$this->user_name = $name;
$this->user_flags = $flags;
}
# Retrieve and Deserialize a User by ID.
public static function db_user_by_id($dbc, $id)
{
$query = "SELECT * FROM user WHERE user_id=$id LIMIT 1";
$result = mysqli_query($dbc, $query);
if(mysqli_num_rows($result) == 1)
{
$row = mysqli_fetch_array($result, MYSQLI_NUM);
return new User($row[0], $row[1], $row[2]);
}
}
# Retrieve a FLAGGED row and/or create a new User object with it,
public static function db_available($dbc)
{
$id = 0;
$query = "SELECT user_id FROM user WHERE user_flags=1 LIMIT 1";
$result = mysqli_query($dbc, $query);
if(mysqli_num_rows($result) == 1)
{
$row = mysqli_fetch_array($result, MYSQLI_NUM);
$id = $row[0];
}
return new User($id, '', 0);
}
public function setDelete($delete) // TRUE or FALSE.
{
$this->user_flags = ($delete ? 1 : 0); // This doesn't really delete, it just flags.
}
public function setName($name)
{
$this->user_name = $name;
}
# This function determines whether to INSERT or UPDATE, but never to DELETE.
public function save($dbc)
{
$this->user_id == 0 ? $this->insert($dbc) : $this->update($dbc);
}
protected function insert($dbc)
{
$query = "INSERT INTO user VALUES (NULL, '$this->user_name',
$this->user_flags)";
$result = mysqli_query($dbc, $query);
}
protected function update($dbc)
{
$query = "UPDATE user SET user_name='$this->user_name',
user_flags=$this->user_flags WHERE user_id=$this->user_id LIMIT 1";
$result = mysqli_query($dbc, $query);
}
}
?>
3. Use the PHP Object to handle the database for you, like so:
<?php
include('User.php');
# INSERT a new User.
$user = User::db_available($dbc);
$user->setName('John Doe');
$user->save($dbc);
# UPDATE User 22.
$user = User::db_user_by_id($dbc, 22);
$user->setName('Jane Doe');
$user->save($dbc);
# DELETE User 22.
$user = User::db_user_by_id($dbc, 22);
$user->setDelete(true); // FLAGS ROW 22 for reuse - does not really DELETE.
$user->save($dbc);
# INSERT a new User.
$user = User::db_available($dbc); // REUSES ROW 22 because it has been previously FLAGGED.
$user->setName('Green Goblin');
$user->save($dbc);
?>
Adding a 'flags' column into your tables can ensure that FLAGGED rows are REUSED if your
PHP code looks for it, like ours does... see User::db_available().
In addition, your table will soon come to its 'natural maximum size' and no more. For
example, I have a 'reminders' TABLE that I've used for over a year now and it only
has 12 rows in it today. This is because they just get FLAGGED when I'm done, and REUSED when I
need another! So far, I've never used more than 12 reminders at any given time, so that's
as big as it ever got. All without sending a single DELETE query to the db.