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 " ";
}
}
?>
<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" > </th>
<th width="5%" align="right" > </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" > <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.
/ 13 
