PHP and COM - All For One, And One For All

(Page 7 of 9 )

What I need now is a script that lets me view records, add records, edit records and delete records. For convenience, I'm going to put all these functions into a single script, with a series of "if" tests to determine which function to activate every time the script is executed.

Here's the broad outline of what the script will look like:

<?php
// start storing the content in a buffer
ob_start();
// page header
// set some defaults
if(!isset($nextopid) && !isset($previousopid)) 
{
$nextopid = "list";
$previousopid = "";
}
// make a choice as to what function is needed
if($nextopid == "list") 
{
// code to list all records
} 
else if($nextopid == "add" || ($nextopid == "edit" && isset($id))) 
{
// code to display HTML form to add or modify records
} 
else if($nextopid == "add_process") 
{
// code to add new record
} 
else if($nextopid == "edit_process") 
{
// code to update selected record
} 
else if($nextopid == "delete") 
{
// code to delete selected record 
}
// page footer
// display buffer contents
ob_end_flush();
?>

As you can see from the code above, there are just two variables used to control the entire script (they're initialized with default values at the top of the script). The more important of the two is the $nextopid variable, which tells the script which operation to perform; it can contain any one of the following values:

"list" - list all records in the table;

"add" - display a form for adding new records;

"edit" - display a form for editing an existing record (must be passed the ID of the record as well);

"add_process" - process the form data and INSERT the record into the table;

"edit_process" - process the form data and UPDATE the record in the table;

"delete" - DELETE the selected record from the table.

Let's look at the code for each of these in detail.

<?php
// code to list all records
if($nextopid == "list") 
{
// check to see what the previous operation was
// if null, do nothing
// if value exists, display an appropriate message 
// to indicate the results of that operation
if(isset($previousopid) && $previousopid != "") 
{
switch ($previousopid) 
{
case "add_process":
echo "<h3>Record successfully
added</h3>";
break;
case "edit_process":
echo "<h3>Record successfully
updated</h3>";
break;
case "delete":
echo "<h3>Record successfully
deleted</h3>";
break;
default:
echo "&nbsp;";
}
}
?>
<h4 align="left">Coin Listing</h4>
<table border="2" cellpadding="5" cellspacing="1" width="90%">
<tr>
<th width="5%" align="right">#</th>
<th width="20%" align="left">Name</th>
<th width="20%" align="left">Country</th>
<th width="15%" align="right">Weight <br>(in gms.)</th>
<th width="5%" align="right" >Year</th>
<th width="20%" align="left">Remarks</th>
<th width="5%" align="right" >&nbsp;</th>
<th width="5%" align="right" >&nbsp;</th>
</tr>
<?php
// open up a connection to the database
$DB_Conn = new COM("ADODB.Connection") or die("Cannot start ADO");
$DB_Conn->Open("phpcom");
// execute a query
$RS_Record = $DB_Conn->Execute("SELECT * FROM coins"); 
// iterate through the recordset
while (!$RS_Record->EOF) 
{
// get the field data into variables
$id = $RS_Record->Fields('id');
$name = $RS_Record->Fields('name');
$country = $RS_Record->Fields('country');
$weight = $RS_Record->Fields('weight');
$year = $RS_Record->Fields('year');
$remarks = $RS_Record->Fields('remarks');
?>
<tr>
<td width="5%" align="right"><?php echo $id->value;
?></td>
<td width="20%" align="left"><?php echo $name->value;
?></td>
<td width="20%" align="left"><?php echo $country->value;
?></td>
<td width="5%" align="right"><?php echo $weight->value;
?></td>
<td width="10%" align="right"><?php echo $year->value;
?></td>
<td width="35%" align="left"><?php echo $remarks->value;
?></td>
<td width="5%" align="right" ><a href="<?php echo
$PHP_SELF."?nextopid=edit&previousopid=list&id=".$id->value;?>">Edit</a>
</td>
<td width="5%" align="right" ><a href="<?php echo
$PHP_SELF."?nextopid=delete&previousopid=list&id=".$id->value;?>">Delete
</a>
</td>
</tr>
<?php
// go to the next record
$RS_Record->MoveNext();
}
?>
<tr>
<td colspan="8" align="right"><a href="<?php echo
$PHP_SELF."?nextopid=add&previousopid=list";?>">Add New</a></td>
</tr>
</table>
<?php
// clean up
$RS_Record->Close();
$DB_Conn->Close();
$RS_Record = null;
$DB_Conn = null;
// end of "list" block
}
?>

Over here, I've used COM to create an instance of the ADODB.Connection class - this allows me to open a connection to the DSN created on the previous page. Next, I've executed a query to SELECT all the records present in the table and store them in a record set. Then I've iterated through the record set and displayed all the fields on a Web page. Note my usage of the Fields() method to do this.

Here's what the output looks like:



Next up, adding and editing coins. Since the form displayed for both these operations is similar, I can combine the two operations into a single block. If the operation is an "add" operation, the form will be displayed with empty fields; if it is an "edit" operation, the record ID passed to the script will be used to query the table for the record and pre-fill the form fields with data.

<?php
if($nextopid == "add" || ($nextopid == "edit" && isset($id))) 
{
// code to display HTML form to add or modify records
?>
<h4 align="left"><?php echo ucfirst($nextopid)." Coin"; ?></h4> <?php
if ($nextopid == "edit" && isset($id)){
// if this is an edit operation
// use the ID to retrieve the record from the table
$DB_Conn = new COM("ADODB.Connection") or die("Cannot
start ADO");
$DB_Conn->Open("phpcom");
// run query
$query = "SELECT name, country, weight, year, remarks
FROM coins where id = $id";
$RS_Record = $DB_Conn->Execute($query);    
// iterate through recordset
while (!$RS_Record->EOF) 
{
$name = $RS_Record->Fields('name');
$name = $name->value;
$country = $RS_Record->Fields('country');
$country = $country->value;
$weight = $RS_Record->Fields('weight');
$weight = $weight->value;
$year = $RS_Record->Fields('year');
$year = $year->value;
$remarks = $RS_Record->Fields('remarks');
$remarks = $remarks->value;
$RS_Record->MoveNext();
}
// clean up
$RS_Record->Close();
$DB_Conn->Close();
$RS_Record = null;
$DB_Conn = null;
}
?>
<form action="<?php echo $PHP_SELF;?>" method="post" >
<?php if(isset($id)) {?><input type="hidden" name="id" value="<?php echo
$id;?>"><?php } ?>
<input type="hidden" name="previousopid" value="<?php echo
$nextopid;?>">
<input type="hidden" name="nextopid" value="<?php echo
$nextopid;?>_process">
<table border="2" cellpadding="10" cellspacing="1" width="90%">
<tr>
<th align="left">Coin characteristics</th>
<th align="left"><?php echo ucfirst($nextopid); ?>
Value</th>
</tr>
<tr>
<td align="right">Name</td>
<td align="left"><input type="text" name="name"
size="30" maxlength="30" value="<?php if(isset($name)) echo
trim($name);?>"></td>
</tr>
<tr>
<td align="right">Country</td>
<td align="left"><input type="text" name="country"
size="30" maxlength="30" value="<?php if(isset($country)) echo
trim($country);?>"></td>
</tr>
<tr>
<td align="right">Weight (in gms)</td>
<td align="left"><input type="text" name="weight"
size="10" maxlength="10" value="<?php if(isset($weight)) echo
trim($weight);?>"></td>
</tr>
<tr>
<td align="right">Year</td>
<td align="left"><input type="text" name="year"
size="10" maxlength="10" value="<?php if(isset($year)) echo
trim($year);?>"></td>
</tr>
<tr>
<td align="right">Remarks</td>
<td align="left"><textarea name="remarks" cols="40"
rows="4"><?php
if(isset($remarks)) echo trim($remarks);?></textarea></td>
</tr>
<tr>
<td colspan="2" align="right"><input type="submit"
value="<?php echo ucfirst($nextopid);?> Coin" >&nbsp;&nbsp;<input
type="reset" value="Clear above form"></td>
</tr>
<tr>
<td colspan="2" align="right"><a href="<?php echo
$PHP_SELF."?nextopid=list"; ?>">Back to listing</a></td>
</tr>
</table>
</form>
<?
// end of "add" or "edit" condition
} 
?>

Once I know that the user is editing a record, it's trivial to create another ADODB connection to the database to fetch the details of the entry and display a pre-filled form. If, on the other hand, the user is adding a record, I can skip the entire process of fetching the record from the database and just display a blank form.

Here's what the two forms look like:





Note the values of $nextopid in both cases above - for an "add" operation, $nextopid is set to "add_process", and for an "edit" operation, it's set to "edit_process". Let's take a look at those next.

Next: New Coins For Old >>
 

More PHP Articles
More By Harish Kamath, (c) Melonfire

Comments On: PHP and COM

>>> Be the FIRST to comment on this article!