Lab 3 (PHP Coding Guide)

C:\xampp\htdocs\products



db_connect.php

<?php
/**
* A class file to connect to database
*/
class DB_CONNECT {
var $myconn;
/**
* Function to connect with the database
*/
function connect() {
   define('DB_USER', "root"); // db user
   define('DB_PASSWORD', ""); // db password (mention your db password here)
   define('DB_DATABASE', "mydatabase"); // database name
   define('DB_SERVER', "localhost"); // db server
   // Connecting to mysql database
   $con = mysqli_connect(DB_SERVER, DB_USER, DB_PASSWORD,DB_DATABASE) or die(mysqli_error($con));
   $this->myconn = $con;
   // returning connection cursor
   return $this->myconn;
}
// Function to close db connection

function close($myconn) {
     mysqli_close($myconn);// closing db connection
}
}

?>


Create a testConnectDB.php to test if the connection to DB is successful

testConnectDB.php
<?php
// include db_connect.php file
require_once __DIR__ . '/db_connect.php';
//check if connect button is set and submitted
if(isset($_POST["submit"])) {
// create a new instance of DB_CONNECT class
$db= new DB_CONNECT();
//call the function connect() in DB_CONNECT class
$connection = $db->connect();
if (!$connection)
echo("Connection failed");
else
echo "Connected successfully";
}
?>
<h2> SQL Database Connection </h2>
<form action="testConnectDB.php" method="post">
<input type="submit" name="submit" value="Connect">
</form>

http://localhost/products/testConnectDB.php




Create a PHP page called get_all_products.php with the following codes:

<html><body> 
<?php
 // Following code will list all the products
$htmlDisplay="<h1> Search Results: </h1>";
$htmlDisplay=  $htmlDisplay. "<table border='1'>";
 
 // include db connect class
    require_once __DIR__ . '/db_connect.php';
 // connecting to db
    $db= new DB_CONNECT();
    $db->connect();


    //  $sqlCommand="SELECT * FROM staffdir";       Modify SQL statements
    $sqlCommand="SELECT * FROM products";// get all products from products table
    $result =mysqli_query($db->myconn, "$sqlCommand");

// printing table headers
$htmlDisplay = $htmlDisplay ."<th> Pid </th><th> Name </th> <th> Price </th> <th> Description </th>";

// check for empty result
if (mysqli_num_rows($result) > 0) {
    // looping through all results
     foreach($result as $row)
     {

      $dataHtml = nl2br($row["description"]);

       $htmlDisplay = $htmlDisplay ."<tr> <td>".$row["pid"]. "</td>";    
       $htmlDisplay = $htmlDisplay ."<td>".$row["name"]. "</td>";
       $htmlDisplay = $htmlDisplay ."<td>".$row["price"]. "</td>";  
       $htmlDisplay = $htmlDisplay ."<td>".$dataHtml. "</td></tr>";
                     
     }
 
     $htmlDisplay =$htmlDisplay."</table>";
     echo $htmlDisplay;
   } else {  // no products found
             echo "<h1> Not found </h1>";   }
$db->close($db->myconn);
?>
</body></html>



Run the main.html on web browser. 
Remember to start your Apache and MySQL in XAMPP server.
When you click on the hyperlink “List All Products”, you should see the list of products from the database displayed in a table on your web browser.











In this program flow, we will need to create 2 php files:
• searchProduct.php
• get_product_details.php


Enter these codes for searchProduct.php.
It creates a html form to submit the pid

<!DOCTYPE html> 
<html> 
<body> <h2>Search Product Details</h2> 
<form action="get_product_details.php" method="post"> 

Product ID:<br> <input type="text" name="pid" required> <br> <br>
 <input type="submit" value="Submit"> 
</form> 

<br> <a href="get_all_products.php"> List All Products </a> </body> </html>


Enter these codes for get_product_details.php

<html><body>
 <?php
    $htmlDisplay="<h1> Search Results: </h1>";
    $htmlDisplay=  $htmlDisplay. "<table border='1'>";
 
    if (isset($_POST["pid"])) {
          $pid = $_POST['pid'];
 
           require_once __DIR__ . '/db_connect.php'; // include db connect class
           $db= new DB_CONNECT();// connecting to db
           $db->connect();

           $sqlCommand="SELECT * FROM products WHERE pid = $pid";// get the search product from products table
           $result =mysqli_query($db->myconn, "$sqlCommand");
          $htmlDisplay = $htmlDisplay ."<th> Pid </th><th> Name </th> <th> Price </th> <th> Description </th>";// printing table headers

    if (mysqli_num_rows($result) > 0) {   // check for empty result
    // looping through all results

      foreach($result as $row)
      {   
          $htmlDisplay = $htmlDisplay ."<tr> <td>".$row["pid"]. "</td>";  
          $htmlDisplay = $htmlDisplay ."<td>".$row["name"]. "</td>";
          $htmlDisplay = $htmlDisplay ."<td>".$row["price"]. "</td>";  
          $htmlDisplay = $htmlDisplay ."<td>".$row["description"]. "</td></tr>";
      }
  
      $htmlDisplay =$htmlDisplay."</table>";
      echo $htmlDisplay;
  
    } else {// no products found
             echo "<h1> Not found </h1>";  
    }
        $db->close($db->myconn);
   }
?>



</body>

</html>



Run the main.html on web browser.
When you click on the hyperlink “Search Product”, you should be able to use the product id to search for product.
Study the above php codes thoroughly to understand them.



Part 3: To Edit Existing Product in your Products Table














Codes for editProduct.php

<!DOCTYPE html> 
<h2>Select Product for Editing</h2> 
<html> 
    <body> 
        <form action="edit_product_details.php" method="post"> 
            Product ID:<br> 
            <input type="text" name="pid" required> 
            <br> <br> 
            <input type="submit" value="Submit"> 
        </form> 
    <br> 
    <a href="get_all_products.php"> List All Products </a> 
    </body> 
</html>

Codes for edit_product_details.php

Create another php file in Visual Studio and enter the following codes. 
Save it as edit_product_details.php.
 This file will have a combination of html codes and php codes.
Below are the php codes that are used to retrieve the product details based on the posted pid from the html form.


<html> 
<body> 
<?php 
    //check that value for pid is set in the html form 
    if (isset($_POST["pid"])) { 
        $pid = $_POST['pid']; 
        // include db connect class 
        require_once __DIR__ . '/db_connect.php'; 
        // connecting to db 
        $db= new DB_CONNECT(); 
        $db->connect(); 

        // get the selected product based on pid 
        $sqlCommand="SELECT * FROM products WHERE pid = $pid"; 
        $result =mysqli_query($db->myconn, "$sqlCommand"); 
    if (mysqli_num_rows($result) > 0) { 
        foreach($result as $row) 
        { //retrieve the values of the product details 
            $name = $row["name"]; 
            $price = $row["price"]; 
            $description=$row["description"]; 
        } 
    } else { 
        // no products found 
        echo "<h1> Not found </h1>"; 
        echo "<br> <a href='main.html'> Home </a>"; 
        return; 
        } 
        $db->close($db->myconn); 
} 
?> 

<!----------------html Codes to be added   ----------------->


</body> 
</html>

We will then populate the html form with the details of the product extracted from the database. Add following html codes just before the </body> tag.

<h2>Edit Selected Product Here</h2> 
<form action="update_product_details.php" method="post"> 
    Product Name: <input type="text" name="name" size=50 value="<?php echo $name ?>" > <br> <br> 
    Product Price: <input type="text" name="price" size =20 value="<?php echo $price ?>" > <br> <br> 
    Product Description: <input type="text" name="description" size=50 value="<?php echo $description ?>" > <br> <br> 
    <input hidden type='text' name='pid' value="<?php echo $pid ?>" > 
    <input type="submit" name = "Update" value="Update"> 
    <input type="submit" name = "Remove" value="Remove"> 
</form>


Note that the html form has a hidden input for pid. That is because we do not want the user to change the value of pid, as we will need this value of pid to update the correct product details in the database, or to remove it from the database later on in this lab.


Codes for update_product_details.php

Create another php file in Visual Studio and enter the following codes. Save it as update_product_details.php.

<html> 
<body> 
<h2>Update Product</h2> 

<?php 
/* 
* Following code will edit or remove the selected product 
* All product details are read from HTTP Post Request 
*/ 
// check for required fields from posted html form 
if (isset($_POST['name']) && isset($_POST['price']) && isset($_POST['description']) && isset($_POST['pid']) ) { 
    //retrieve the values from html form 
    $pid = $_POST['pid']; 
    $name = $_POST['name']; 
    $price = $_POST['price']; 
    $description = $_POST['description']; 
    // include db connect class 
    require_once __DIR__ . '/db_connect.php'; 
    // connecting to db 
    $myConnection= new DB_CONNECT(); 
    $myConnection->connect(); 
    
    //if update button is clicked in form submission 
    if (isset($_POST['Update'])) 
        $sqlCommand="UPDATE products SET name='$name', price='$price', description='$description' WHERE pid='$pid'"; 
    //if remove button is clicekd in form submission 
    else if (isset($_POST['Remove'])) 
        $sqlCommand = "DELETE FROM products WHERE pid ='$pid'"; 
    //execute the sql command 
        $result =mysqli_query($myConnection->myconn, "$sqlCommand"); 
 


   // check the result 
    if ($result) {  //if result is successful
        // successfully updated into database 
        if (isset($_POST['Update'])) 
            echo "Product successfully updated."; 
        //successfully removed from database 
        else if (isset($_POST['Remove'])) ////
            echo "Product successfully removed"; 
    } else { 
    // failed to update database 
    echo "Oops! An error occurred." ; 
    } 

    $myConnection->close($myConnection->myconn); 
    echo "<br><br><a href='main.html'> Home</a>"; 
    } else { 
    echo "Error occurs"; 
    } 
?> 

</body> 
</html>


With these 3 php files created, you should be able to update or remove the selected product from the database.



Part 4: To Add New Product to your Products Table
When you click on the Add Product link, you should be able to add new product to your products table in your database.















Expected Output
























On your own, proceed to write the necessary codes to add new product to the database.
Hint: Refer to this url link at https://www.w3schools.com/php/php_mysql_insert.asp or the sample SQL statements in Lab 2 for the php codes on inserting new records into database.

Comments

Post a Comment

Popular posts from this blog

Simple Login Example (PHP Server + Android Client)

Image
  Simple Login Example I have created this simple Login Example and intentionally leave it as the kind of login that is subject to hacking using SQL injection technique. I will discuss on SQL injection later. XAMP server side (PHP) Database create table users(     userid varchar (20) primary key ,    password varchar (80) not null ); insert the data userid as 'test' and password as '1234' for testing INSERT INTO `users`(`userid`, `password`) VALUES ('test','1234') PHP Step 0: Create    db_connect.php (  Refer to Lab2) Step1 :Create a  loginTest.php <<< Server Side>>>  loginTest.php          <?php // check for post data if ( isset ( $_POST [ "id" ])) { $id = $_POST [ 'id' ]; $pw = $_POST [ 'pw' ]; // include db connect class require_once __DIR__ . '/db_connect.php' ; // connecting to db $db = new DB_CONNECT(); $db -> connect (); ...
Read more

Lab 6: Using split

  Using split https://www.w3schools.com/java/tryjava.asp?filename=demo_compiler import java.util.* ; public class Main { public static void main ( String [] args ) { // ArrayList to store product info in Hashmap for ListView ArrayList < HashMap < String , String >> productsList = new ArrayList < HashMap < String , String >>(); String result = "1;Long-grain rice, not parboiled, WKB;88.00:7;Pineapple King;19.00:11;Oat Bran;1.00:12;Apple;20.00:14;Soya Bean;2.00:" ; System . out . println ( result + "\n\n" ); String [] products = result . split ( " : " ); for ( int i = 0 ; i < products . length ; i ++) { //System.out.println(products[i]); String [] details = products [ i ]. split ( " ; " ); String id = details [ 0 ]; String name = details [ 1 ]; String price = details [ 2 ]; ...
Read more