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

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

GET and POST Parameter Passing

 GET: passes the parameters as a list in the URL

method="get"

action=“InsertProduct.php">  Action Parameters POST: sends the parameters directly to the receiving program without displaying them in the URL

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  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.

Receive input parameters and assign them to variables Create the query 3.

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 Concatenation operator to show INSERT command on multiple lines //assign the parameter values to variables $prod_desc = $_REQUEST['prod_desc']; $prod_cost = $_REQUEST['prod_cost']; $prod_price = $_REQUEST['prod_price']; 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: " . mysql_error() . "
"); //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

Passes selected prod_id as parameter

Page 2: ViewProductRecord.php

Display single record layout for selected record

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: " . mysql_error() . "
");

?

>

Select a product for updating:

<

?php

while ($row = mysql_fetch_array($result)) {

?

>

?

>"> <

?php

<

?php

}

?

> echo $row["prod_desc"]

?

>

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: " . mysql_error() . "
");

?

> From ViewProductRecord.php

Create a form, add prod_id as a hidden form input, display other values as form text input values:

Update Product Information

<

?php

while ($row = mysql_fetch_array($result)) {

?

>

<

?php

}

?

>

Description:	? >" type="text" size="30" maxlength="30" />
Cost:	? >"
Price:	? >" />

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: " . mysql_error() . "
"); //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: " . mysql_error() . "
"); //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!

  Add one line of code Test  Repeat Use method="GET" to view parameter names and values View the page source code  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:

"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: " . mysql_error() . "
"); //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…