📄 sdo.das.rel.examples.two-table.html
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html> <head> <title>Two-table examples</title> <meta http-equiv="content-type" content="text/html; charset=UTF-8"> </head> <body><div style="text-align: center;"> <div class="prev" style="text-align: left; float: left;"><a href="sdo.das.rel.examples.one-table.html">One-table examples</a></div> <div class="next" style="text-align: right; float: right;"><a href="sdo.das.rel.examples.three-table.html">Three-table example</a></div> <div class="up"><a href="sdo.das.rel.examples.html">Examples</a></div> <div class="home"><a href="index.html">PHP Manual</a></div></div><hr /><div id="sdo.das.rel.examples.two-table" class="section"> <h2 class="title">Two-table examples</h2> <p class="para"> The following set of examples all use two tables from the company database: the company and department tables. These examples exercise more of the function of the Relational DAS. </p> <p class="para"> In this series of examples a company and department are created, retrieved, updated, and finally deleted. This illustrates the lifecycle for a data graph containing more than one object. Note that this example clears out the company and department tables at the start so that the exact results of the queries can be known. </p> <p class="para"> You can find these examples combined into one script called <var class="filename">1cd-CRUD</var> in the <var class="filename">Scenarios</var> directory in the Relational DAS package. </p> <p class="para"> <div class="example"> <p><b>Example #1 One company, one department - Create</b></p> <div class="example-contents"><p> As in the earlier example of creating just one company data object, the first action after constructing the Relational DAS is to call <b>createRootDataObject()</b> to obtain the special root object of the otherwise empty data graph. The company object is then created as a child of the root object, and the department object as a child of the company object. </p></div> <div class="example-contents"><p> When it comes to applying the changes, the Relational DAS has to perform special processing to maintain the foreign keys that support the containment relationships, especially if auto-generated primary keys are involved. In this example, the relationship between the auto-generated primary key <var class="varname">id</var> in the company table and the <var class="varname">co_id</var> column in the department table must be maintained. When inserting a company and department for the first time the Relational DAS has to first insert the company row, then call PDO's <b>getLastInsertId()</b> method to obtain the auto-generated primary key, then add that as the value of the <var class="varname">co_id</var> column when inserting the department row. </p></div> <div class="example-contents"><div class="phpcode"><code><span style="color: #000000"><span style="color: #0000BB"><?php<br /></span><span style="color: #007700">require_once </span><span style="color: #DD0000">'SDO/DAS/Relational.php'</span><span style="color: #007700">;<br />require_once </span><span style="color: #DD0000">'company_metadata.inc.php'</span><span style="color: #007700">;<br /><br /></span><span style="color: #FF8000">/*************************************************************************************<br />* Empty out the two tables<br />*************************************************************************************/<br /></span><span style="color: #0000BB">$dbh </span><span style="color: #007700">= new </span><span style="color: #0000BB">PDO</span><span style="color: #007700">(</span><span style="color: #0000BB">PDO_DSN</span><span style="color: #007700">,</span><span style="color: #0000BB">DATABASE_USER</span><span style="color: #007700">,</span><span style="color: #0000BB">DATABASE_PASSWORD</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$pdo_stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$dbh</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">'DELETE FROM COMPANY;'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$rows_affected </span><span style="color: #007700">= </span><span style="color: #0000BB">$pdo_stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">$pdo_stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$dbh</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">'DELETE FROM DEPARTMENT;'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$rows_affected </span><span style="color: #007700">= </span><span style="color: #0000BB">$pdo_stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br /><br /></span><span style="color: #FF8000">/**************************************************************<br />* Create a company with name Acme and one department, the Shoe department<br />***************************************************************/<br /></span><span style="color: #0000BB">$dbh </span><span style="color: #007700">= new </span><span style="color: #0000BB">PDO</span><span style="color: #007700">(</span><span style="color: #0000BB">PDO_DSN</span><span style="color: #007700">,</span><span style="color: #0000BB">DATABASE_USER</span><span style="color: #007700">,</span><span style="color: #0000BB">DATABASE_PASSWORD</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$das </span><span style="color: #007700">= new </span><span style="color: #0000BB">SDO_DAS_Relational </span><span style="color: #007700">(</span><span style="color: #0000BB">$database_metadata</span><span style="color: #007700">,</span><span style="color: #DD0000">'company'</span><span style="color: #007700">,</span><span style="color: #0000BB">$SDO_containment_metadata</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$root </span><span style="color: #007700">= </span><span style="color: #0000BB">$das </span><span style="color: #007700">-> </span><span style="color: #0000BB">createRootDataObject</span><span style="color: #007700">();<br /><br /></span><span style="color: #0000BB">$acme </span><span style="color: #007700">= </span><span style="color: #0000BB">$root </span><span style="color: #007700">-> </span><span style="color: #0000BB">createDataObject</span><span style="color: #007700">(</span><span style="color: #DD0000">'company'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$acme </span><span style="color: #007700">-> </span><span style="color: #0000BB">name </span><span style="color: #007700">= </span><span style="color: #DD0000">"Acme"</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">$shoe </span><span style="color: #007700">= </span><span style="color: #0000BB">$acme</span><span style="color: #007700">-></span><span style="color: #0000BB">createDataObject</span><span style="color: #007700">(</span><span style="color: #DD0000">'department'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$shoe</span><span style="color: #007700">-></span><span style="color: #0000BB">name </span><span style="color: #007700">= </span><span style="color: #DD0000">'Shoe'</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">$das </span><span style="color: #007700">-> </span><span style="color: #0000BB">applyChanges</span><span style="color: #007700">(</span><span style="color: #0000BB">$dbh</span><span style="color: #007700">, </span><span style="color: #0000BB">$root</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span></span></code></div> </div> </div> </p> <p class="para"> <div class="example"> <p><b>Example #2 One company, one department - Retrieve and Update</b></p> <div class="example-contents"><p> In this case the SQL query passed to <b>executeQuery()</b> performs an inner join to join the data from the company and department tables. Primary keys for both the company and department tables must be included in the query. The result set is re-normalised to form a normalised data graph. Note that a column specifier is passed as the third argument to the <b>executeQuery()</b> call enabling the Relational DAS to know which column is which in the result set. </p></div> <div class="example-contents"><p> Note that the <var class="varname">co_id</var>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -