Transcript JavaScript #4 - UWEC Computer Science Department

Creating PHPs to
Insert, Update, and
Delete Data
CS 320
Approach For Inserting Data

Create first page (HTML or PHP) to collect user
inputs using an HTML form
 Passes

inputs as parameters to the second page
Create second page (PHP) to execute the
INSERT query and display appropriate response
(success or failure) depending on the query
outcome
 Tip:
write the insert query with its values hard coded
before trying to write it using the values the user
submits
Inserting New Data
Page 1 ProductInput.html
Page 2 ProductInsert.php
insert
succeeds
insert fails
*Complete HTML and PHP pages are available
in D2L
Review: Web <form> Tag
Attributes


Name: identifies the form internally within the Web page
Action: specifies the action the Web server performs
with the form parameter list


Usually specifies a script or program that runs on the Web server
Method: how the Web server processes the parameters


GET: passes the parameters as a list on the URL
POST: sends the parameters directly to the receiving program
<form name="NewProduct"action="ProductInsert.php"
method="get">
GET and POST Parameter Passing

GET: passes the parameters as a list in the URL
<form method="get" action=“InsertProduct.php">
Action

Parameters
POST: sends the parameters directly to the receiving
program without displaying them in the URL
<form method="post" action=“InsertProduct.php">

Strategy:


Use GET while you are developing PHP pages
Switch to POST when you are ready to deploy the pages
Review: Web form input name
attributes

Specifies how a parameter value passed
by a Web form is referenced in the server
program
<input name="prod_desc" type="text" size="30"
maxlength="30">
 Good
idea: give the inputs the same names
as the corresponding database fields
Referencing Form Parameters in
PHPs
 General syntax:
$_REQUEST["parameter_name"]
 Specific example:
<?php $prod_desc = $_REQUEST["prod_desc"]; ?>

The name you give to a form’s input in the previous
page is what you use for form_parameter_name in
$_REQUEST
Referencing Form Parameters in
PHPs
 Remember that the parameter is coming from
the page that calls the current page:
 Code in InsertProduct.php:
<?php $prod_desc = $_REQUEST["prod_desc"]; ?>
Steps for Inserting Data Using a
PHP
1.
2.
3.
Receive input parameters and assign
them to variables
Create the query
Execute the query
 If
it fails, display the failure message
 Otherwise, display the success message
(good idea to redisplay the table records also)
PHP With INSERT Command
<?php
//Put code here to connect to MySQL and specify the database
//assign the parameter values to variables
$prod_desc = $_REQUEST['prod_desc'];
$prod_cost = $_REQUEST['prod_cost'];
$prod_price = $_REQUEST['prod_price'];
Concatenation operator
to show INSERT
command
on multiple lines
Character data type must
be in single quotes
//Specify the query
$query = "INSERT INTO candy_product (prod_desc, prod_cost, prod_price) " .
"VALUES ('$prod_desc',$prod_cost,$prod_price)";
//Execute the query
$result = mysql_query($query)
//Show alternate failures message when you are done with development
or die("SQL Error: <b>" . mysql_error() . "</b><br />");
//Display success message
echo $msg = "Record successfully inserted";
//Add code here to retrieve and display CANDY_PRODUCT table contents
//so user can see result of insert
?>
From InsertProduct.php
Updating Data
Page 1: ProductList.php
Select record to update
Page 2: ViewProductRecord.php
Display single record layout
for selected record
Passes
selected prod_id
as parameter
Passes prod_id and updated values
Page 3: UpdateProduct.php
Confirmation (show message, updated
product information or error message)
Displaying retrieved data
values in selection list:
<?php
$query = "SELECT prod_id, prod_desc FROM candy_product";
$result = mysql_query($query)
or die("SQL Error: <b>" . mysql_error() . "</b><br />");
?>
<p>Select a product for updating:</p>
<form name="Products" method="get" action="ViewProductRecord.php">
<select name="prod_id" size="1" >
<!-- populate selection list with retrieved values -->
<?php while ($row = mysql_fetch_array($result)) { ?>
<option value="<?php echo $row["prod_id"] ?>">
<?php echo $row["prod_desc"] ?></option>
<?php } ?>
</select>
</form>
From ProductList.php
Displaying retrieved
values in form inputs:




Receive the prod_id value as a parameter and
store it as a variable
Create a new form and specify prod_id as a
hidden input
Retrieve the current values for the selected
prod_id's other values
Create text inputs whose values are the current
prod_id record values
See: ViewProductRecord.php
Save prod_id parameter as a variable and retrieve
current product values:
<?php
//save passed parameter as a variable
$prod_id = $_REQUEST['prod_id'];
//Specify the query
$query = "SELECT prod_desc, prod_cost, prod_price FROM candy_product " .
"WHERE prod_id = $prod_id";
//Store the result
$result = mysql_query($query)
or die("SQL Error: <b>" . mysql_error() . "</b><br />");
?>
From ViewProductRecord.php
Create a form, add prod_id as a hidden form input,
display other values as form text input values:
<form action="UpdateProduct.php" method="get" name="UpdateProduct" >
<h1>Update Product Information</h1>
<table>
<!– place the prod_id in a hidden input to pass to next PHP -->
<input name="prod_id" type="hidden" value="<?php echo $prod_id ?>" />
<!– Display form items with current data values -->
<?php while ($row = mysql_fetch_array($result)) { ?>
<tr><td>Description:</td>
<td><input name="prod_desc" value="<?php echo $row["prod_desc"] ?>"
type="text" size="30" maxlength="30" /></td></tr>
<tr><td>Cost:</td>
<td><input name="prod_cost" value="<?php echo $row["prod_cost"] ?>"
</td></tr>
<tr><td>Price:</td>
<td><input name="prod_price" value="<?php echo $row["prod_price"] ?>" />
</td></tr>
<tr><td><input name="cmdSubmit" type="submit" value="Update"></td>
<td><input name="cmdReset" type="reset" value="Cancel"></td></tr>
<?php } ?></table></form>
From ViewProductRecord.php
Update the
CANDY_PRODUCT table and
display success message
//save passed parameters as variables
$prod_id = $_REQUEST['prod_id'];
$prod_desc = $_REQUEST['prod_desc'];
$prod_cost = $_REQUEST['prod_cost'];
$prod_price = $_REQUEST['prod_price'];
Place character data
type value in single quotes
//Create UPDATE query
$query = "UPDATE candy_product " .
"SET prod_desc = '$prod_desc', " .
"prod_cost = $prod_cost, " .
"prod_price = $prod_price " .
"WHERE prod_id = $prod_id";
//Execute the query
$result = mysql_query($query)
or die("SQL Error: <b>" . mysql_error() . "</b><br />");
//Display page content
echo $msg = "Record successfully updated";
//Add code to retrieve and display CANDY_PRODUCT table contents
//here so user can see result of update
From UpdateProduct.php
Selecting Data Using a Hyperlink Table
Page 1: Product_Hyperlink_Table.php
Page 2: ViewProductRecord.php
Display single record layout
for selected record
Page 3: UpdateProduct.php
Confirmation (show message, updated
product information or error message)
Deleting Data

From a business standpoint, be very
cautious about allowing users to delete
data
 Better

to archive
You can't delete parent records that are
referenced as foreign keys
Deleting Data
Page 1 ProductListDelete.html
Page 2 ProductDelete.php
Delete
succeeds
Delete fails
PHP With DELETE Command
<?php
//Put code here to connect to MySQL and specify the database
//save passed parameter as variable
$prod_id = $_REQUEST['prod_id'];
//Create DELETE query
$query = "DELETE FROM candy_product " .
"WHERE prod_id = $prod_id";
//Execute the query
$result = mysql_query($query)
or die("SQL Error: <b>" . mysql_error() . "</b><br />");
//Display page content
echo $msg = "Record successfully deleted";
//Add code to retrieve and display CANDY_PRODUCT table contents
//here so user can see result of delete
From ProductDelete.php
PHP/Database Debugging
Strategies

Develop incrementally!





Use method="GET" to view parameter names and
values
View the page source code


Add one line of code
Test
Repeat
Make sure you are testing the most current version of your
PHP page
Display SQL queries directly on the Web page

Then run the query in MySQL Query Workbench
To debug a query, comment out all other code and
display it on the Web page:
<?php
//Put code here to connect to MySQL and specify the database
//assign the parameter values to variables
$prod_desc = $_REQUEST['prod_desc'];
$prod_cost = $_REQUEST['prod_cost'];
$prod_price = $_REQUEST['prod_price'];
//Specify the query
$query = "INSERT INTO candy_product (prod_desc, prod_cost, prod_price) " .
"VALUES ('$prod_desc',$prod_cost,$prod_price)";
//Store the result
//$result = mysql_query($query)
//Show alternate failures message when you are done with development
//or die("SQL Error: <b>" . mysql_error() . "</b><br />");
//Display success message
//echo $msg = "Record successfully inserted";
echo $query
?>
Additional PHP Resources

Tutorials
 Basic
PHP - http://www.w3schools.com/PHP/php_intro.asp
 MySQL - http://www.freewebmasterhelp.com/tutorials/phpmysql/1

Search Google for specific topics…