How to use PHP and MySql in Web Development

How to use PHP and MySql in Web Development

How to use PHP and MySql in Web Development
Step 1: If we need to interact with database the first step is to connect with database. Here is object oriented style to connect with database.
$conn = new mysqli("localhost", "my_user", "my_password", "db_name");
Step 2: After sucessfly established connection with database we can perform CURD operation on database. Here are the function that are commonly
used to perform CURD :
Performs a query on the database
$result = $conn->query($sql_query)
Determine number of rows result set
$result->num_rows
Fetch a result row as an associative array
$row = $result->fetch_assoc()
Fetch the data from row

// Filename : DbConfig.php
class DbConfig {

   const SERVERNAME = 'localhost';
   const USERNAME = 'root';
   const PASSWORD  = 'root'; 
   const DBNAME = 'mydb';
   public $defaultConn;

   function __construct($servername = self::SERVERNAME,$username = self::USERNAME,$password= self::PASSWORD,$dbname = self::DBNAME) {
    $this->defaultConn = new mysqli($servername,$username,$password,$dbname);
     if ($this->defaultConn->connect_error) {
        die("Connection failed: " . $this->defaultConn->connect_error);
    } 
   
   }
   public function getConnection() {
       return $this->defaultConn;
   }

  }
//DbClassEmployee.php
require_once('DbConfig.php');
/*
Table that used in Class

CREATE TABLE `employee` (
  `name` varchar(20) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) 


*/
Class DbClassEmployee
{
 
 private $conn;
 public $error;
 public $message;
 function __construct ()
 {
        
 }
 public function getConnection()
 {
    $objConn = new DbConfig();
    $this->conn = $objConn->getConnection();
 }
public function connClose()
{
    $this->conn->close();
   
}
public function newEmployee($name,$phone,$address='',$sex='')
{
    $this->message = '';
    $this->error = 0;
    if(empty($name)) {
        $this->message .= 'Please enter name ';
        $this->error = 1;
    }
    if(empty($phone)) {
        $this->message .= ' Please enter phone ';
        $this->error = 1;
    }

    
    $sql = "INSERT INTO employee (name, phone, address,sex)
    VALUES ('".$name."','".$phone."','". $address."','".$sex."')";
    if(!$this->error) {
     if ($this->conn->query($sql) === TRUE) {
        $this->message .=  "New record created successfully";
     } else {
        $this->message .= "Error: " . $sql . "<br>" . $this->conn->error;
        $this->error=1;
     }
    }
   
} // new e

public function updateEmployee($id,$phone='',$address='') {
    $this->message = '';
    $this->error = 0;
    if(empty($id)) {
        $this->message .= 'Please enter id ';
        $this->error = 1;
    }
    if(empty($phone) && empty($address)) {
        $this->message .= ' Please enter phone or address';
        $this->error = 1;
    }
    
    $sql = "update employee ";
    if(!empty($phone) && !empty($address)) {
        $sql .= " set phone = '".$phone."' , address ='".$address."'";
    } else if (!empty($phone)) {
        $sql .= " set phone = '".$phone."'";
    } else if (!empty($address)) {
        $sql .= " set address ='".$address."'";
    } else {}
    $sql .= " where id = ".$id;
    
      
    if(!$this->error) {
        if ($this->conn->query($sql) === TRUE) {
           $this->message .=  "record updated successfully";
        } else {
           $this->message .= "Error: " . $sql . "<br>" . $this->conn->error;
           $this->error=1;
        }
       }
       
}

public function getEmployees()
{
    $sql = "SELECT * FROM employee ";
    $result = $this->conn->query($sql);
    if ($result->num_rows > 0) {
        $records ="<table border =1 width ='600px'>";
        $records .="<tr><td>Id</td><td>Name</td><td>Phone</td><td>Address</td><td>Gender</td></tr>";
        while($row = $result->fetch_assoc()) {
            $records .='<tr><td>'.$row["id"].'</td><td>'.$row["name"].'</td><td>'.$row["phone"].'</td><td>'.$row["address"].'</td><td>'.$row["sex"].'</td></tr>';
       
        }
        $records .="</table>";

        echo $records;
    }
}

} // class




$objEmp = new DbClassEmployee();
$objEmp->getConnection();

// new record 
/*
$objEmp->newEmployee('Sahilpree singh','45454545');
if($objEmp->error)
{
    echo $objEmp->message; 
} else {
    echo $objEmp->message; 
}
*/

// update 
/*
$objEmp->updateEmployee(6,'567891234','Chd');
if($objEmp->error)
{
    echo $objEmp->message; 
} else {
    echo $objEmp->message; 
}
*/
// all employee

$objEmp->getEmployees();

$objEmp->connClose();
Tags