Register Register Member Login Member Login Member Login Forgot Password ??
PHP , ASP , ASP.NET, VB.NET, C#, Java , jQuery , Android , iOS , Windows Phone
 

Registered : 109,036

HOME > PHP Manual > Three-table example





ลองใช้ค้นหาข้อมูล


   
Bookmark.

Three-table example

The following examples use all three tables from the company database: the company, department, and employee tables. These introduce the final piece of function not exercised by the examples above: the non-containment reference employee_of_the_month.

Like the examples above for company and department, this set of examples is intended to illustrate the full lifecycle of such a data graph.

Example #1 One company, one department, one employee - Create

In this example a company is created containing one department and just one employee. Note that this example clears out all three tables at the start so that the exact results of the queries can be known.

Note how once the company, department and employee have been created, the employee_of_the_month property of the company can be made to point at the new employee. As this is a non-containment reference, this cannot be done until the employee object has been created within the graph. Non-containment references need to be managed carefully. For example if the employee were now deleted from under the department, it would not be correct to try to save the graph without first clearing or re-assigning the employee_of_the_month property. The closure rule for SDO data graphs requires that any object pointed at by a non-containment reference must also be reachable by containment relationships.

When it comes to inserting the graph into the database, the procedure is similar to the example of inserting the company and department, but employee_of_the_month introduces an extra complexity. The Relational DAS needs to insert the objects working down the tree formed by containment relationships, so company, then department, then employee. This is necessary so that it always has the auto-generated primary key of a parent on hand to include in a child row. But when the company row is inserted the employee who is employee of the month has not yet been inserted and the primary key is not known. The procedure is that after the employee record is inserted and its primary key known, a final step is performed in which the the company record is updated with the employee's primary key.

<?php
require_once 'SDO/DAS/Relational.php';
require_once 
'company_metadata.inc.php';

/*************************************************************************************
* Empty out the three tables
*************************************************************************************/
$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);
$pdo_stmt $dbh->prepare('DELETE FROM COMPANY;');
$rows_affected $pdo_stmt->execute();
$pdo_stmt $dbh->prepare('DELETE FROM DEPARTMENT;');
$rows_affected $pdo_stmt->execute();
$pdo_stmt $dbh->prepare('DELETE FROM EMPLOYEE;');
$rows_affected $pdo_stmt->execute();

/*************************************************************************************
* Create a tiny but complete company.
* The company name is Acme.
* There is one department, Shoe.
* There is one employee, Sue.
* The employee of the month is Sue.
*************************************************************************************/
$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);

$root             $das  -> createRootDataObject();
$acme             $root -> createDataObject('company');
$acme -> name     "Acme";
$shoe             $acme -> createDataObject('department');
$shoe -> name     'Shoe';
$shoe -> location 'A-block';
$sue              $shoe -> createDataObject('employee');
$sue -> name      'Sue';
$acme -> employee_of_the_month $sue;

$das -> applyChanges($dbh$root);

echo 
"Wrote back Acme with one department and one employee\n";
?>

Example #2 One company, one department, one employee - Retrieve and update

The SQL statement passed to the Relational DAS is this time an inner join that retrieves data from all three tables. Otherwise this example introduces nothing that has not appeared in a previous example.

The graph is updated by the addition of a new department and employee and some alterations to the name properties of the existing objects in the graph. The combined changes are then written back. The Relational DAS will process and apply an arbitrary mixture of additions, modifications and deletions to and from the data graph.

<?php
require_once 'SDO/DAS/Relational.php';
require_once 
'company_metadata.inc.php';

/*************************************************************************************
* Find the company again and change various aspects.
* Change the name of the company, department and employee.
* Add a second department and a new employee.
* Change the employee of the month.
*************************************************************************************/
$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);

$root $das->executeQuery($dbh,
  
"select c.id, c.name, c.employee_of_the_month, d.id, d.name, e.id, e.name " .
  
"from company c, department d, employee e " .
  
"where e.dept_id = d.id and d.co_id = c.id and c.name='Acme'",
   array(
'company.id','company.name','company.employee_of_the_month',
   
'department.id','department.name','employee.id','employee.name'));
$acme         $root['company'][0];

$shoe         $acme->department[0];
$sue          $shoe -> employee[0];

$it           $acme->createDataObject('department');
$it->name     'IT';
$it->location 'G-block';
$billy        $it->createDataObject('employee');
$billy->name  'Billy';

$acme->name   'MegaCorp';
$shoe->name   'Footwear';
$sue->name    'Susan';

$acme->employee_of_the_month $billy;
$das -> applyChanges($dbh$root);
echo 
"Wrote back company with extra department and employee and all the names changed (Megacorp/Footwear/Susan)\n";

?>

Example #3 One company, two departments, two employees - Retrieve and delete

The company is retrieved as a complete data graph containing five data objects - the company, two departments and two employees. They are all deleted by deleting the company object. Deleting an object from the graph deletes all the object beneath it in the graph. Five SQL DELETE statements will be generated and executed. As always they will be qualified with a WHERE clause that contains all of the fields that were retrieved, so that any updates to the data in the database in the meantime by another process will be detected.

<?php
require_once 'SDO/DAS/Relational.php';
require_once 
'company_metadata.inc.php';

/*************************************************************************************
* Now read it one more time and delete it.
* You can delete part, apply the changes, then carry on working with the same graph but
* care is needed to keep closure - you cannot delete the employee who is eotm without
* reassigning. For safety here we delete the company all in one go. 
*************************************************************************************/
$das = new SDO_DAS_Relational ($database_metadata,'company',$SDO_containment_metadata);
$dbh = new PDO(PDO_DSN,DATABASE_USER,DATABASE_PASSWORD);

$root $das->executeQuery($dbh,
  
"select c.id, c.name, c.employee_of_the_month, d.id, d.name, e.id, e.name " .
  
"from company c, department d, employee e " .
  
"where e.dept_id = d.id and d.co_id = c.id and c.name='MegaCorp';",
   array(
'company.id','company.name','company.employee_of_the_month',
   
'department.id','department.name','employee.id','employee.name'));
$megacorp $root['company'][0];

unset(
$root['company']);
$das -> applyChanges($dbh$root);

echo 
"Deleted the company, departments and employees all in one go.\n";

?>



ThaiCreate.Com Forum


Comunity Forum Free Web Script
Jobs Freelance Free Uploads
Free Web Hosting Free Tools

สอน PHP ผ่าน Youtube ฟรี
สอน Android การเขียนโปรแกรม Android
สอน Windows Phone การเขียนโปรแกรม Windows Phone 7 และ 8
สอน iOS การเขียนโปรแกรม iPhone, iPad
สอน Java การเขียนโปรแกรม ภาษา Java
สอน Java GUI การเขียนโปรแกรม ภาษา Java GUI
สอน JSP การเขียนโปรแกรม ภาษา Java
สอน jQuery การเขียนโปรแกรม ภาษา jQuery
สอน .Net การเขียนโปรแกรม ภาษา .Net
Free Tutorial
สอน Google Maps Api
สอน Windows Service
สอน Entity Framework
สอน Android
สอน Java เขียน Java
Java GUI Swing
สอน JSP (Web App)
iOS (iPhone,iPad)
Windows Phone
Windows Azure
Windows Store
Laravel Framework
Yii PHP Framework
สอน jQuery
สอน jQuery กับ Ajax
สอน PHP OOP (Vdo)
Ajax Tutorials
SQL Tutorials
สอน SQL (Part 2)
JavaScript Tutorial
Javascript Tips
VBScript Tutorial
VBScript Validation
Microsoft Access
MySQL Tutorials
-- Stored Procedure
MariaDB Database
SQL Server Tutorial
SQL Server 2005
SQL Server 2008
SQL Server 2012
-- Stored Procedure
Oracle Database
-- Stored Procedure
SVN (Subversion)
แนวทางการทำ SEO
ปรับแต่งเว็บให้โหลดเร็ว


Hit Link
   







Load balance : Server 05
ThaiCreate.Com Logo
© www.ThaiCreate.Com. 2003-2024 All Rights Reserved.
ไทยครีเอทบริการ จัดทำดูแลแก้ไข Web Application ทุกรูปแบบ (PHP, .Net Application, VB.Net, C#)
[Conditions Privacy Statement] ติดต่อโฆษณา 081-987-6107 อัตราราคา คลิกที่นี่