【PHP】CRUD PhpMyAdmin Database
>
>
【PHP】CRUD PhpMyAdmin Database

PHP - CRUD to PhpMyAdmin Database

Example - CRUD to PhpMyAdmin Database

In this pose shows how to create database into PhpMyAdmin and use php programming language to create, Read, Update, Delete to PhpMyAdmin Database by using NetBeans, XAMPP and MVC Method.

Example - Included

1 - Create Database Table (PhpMyAdmin).

First create a database given a name of testdb, after type in the database name click create button.

Create- Table Column.

After- Created.

2 - Create Table.

After a testdb database has been created, you will see create table option below enter you table name account and number of columns increase to 5 and click go.
  1. id Type INT And tick A.I(Auto Increment)
  2. Username Type Varchar 50
  3. password Type Varchar 50
  4. name Varchar 100
  5. address Varchar 100
  6. To add new Column
  7. After everything done just click Save

Example - Sql.

CREATE TABLE `account` (
  `id` int(11) NOT NULL,
  `Username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `address` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Indexes for table `account`
ALTER TABLE `account`
  ADD PRIMARY KEY (`id`);

-- AUTO_INCREMENT for table `account`
ALTER TABLE `account`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;

3.1

3.2

3 - Create New Project (Netbeans).

3.1

After database has been created go to your Netbeans File>New Project, Category : PHP Projects: PHP Application, after select click Next.

3.2

Enter Project name : test Source Folder : put inside your XAMPP folder > htdocs Folder, after project name and source folder click finish.

4 - Create Table.

Create 4 folder Config to store database connection setting, Control to store Sql Query, Model to Account variable, and View to store Frontend code under the test project Source Files.

5 - Create DBConnection Class.

Right click on the Config folder and New>PHP Files to create a class name DBConnection.php under Config Folder to store database connection string

Copy Or Paste in- Below PHP Code.(Remove all default code fist)

<?php

class DBConnection {

    private static $instance = null;
    private $conn;
    private $host = 'localhost';		//your host you can put either localhost or your local Ip Address
    private $dbName = 'testdb';			//your database name
    private $dbuser = 'root';			//database username
    private $dbpassword = '';			//database password

    private function __construct() {
        $this->conn = new PDO("mysql:host={$this->host};
         dbname={$this->dbName}", $this->dbuser, $this->dbpassword, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
    }

    public static function getInstance() {
        if (!self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    public function getConnection() {
        return $this->conn;
    }

}

?>

6 - Create AccountControl Class.

Right click on the Control folder and New>PHP Files to create a class name AccountControl.php under Control Folder to store all the sql query that use to create, update, read, and delete record.

Code for - AccountControl Class

<?php

//If your want to use other class function or method you must require the class into this class.
//../ means that back to previous file location(Example : Souce File/Control, added ../ will be Souce File/)
require("../Config/DBConnection.php");
require("../Model/Account.php");

//Reset All SESSION
function resetSess() {
	unset($_SESSION['AccountId']);
	unset($_SESSION['userName']);
	unset($_SESSION['userPass']);
	unset($_SESSION['Name']);
	unset($_SESSION['Address']);
	session_destroy();
}

//Create User
if (isset($_POST['AddUser'])) {	//POST is to get the from the form in post method with the button name of AddUser
	session_start();			//If you are using SESSION to past data you must include this session_start
	extract($_POST);
	$instance = DBConnection::getInstance();
	$conn = $instance->getConnection();


	$userName = $_POST['userName'];		// the userName,userPass,Name, and Address are from the form input field name and put the value inside the variable
	$userPass = $_POST['userPass'];
	$Name = $_POST['Name'];
	$Address = $_POST['Address'];


	$sql = "INSERT INTO account (Username, password, name ,address) VALUES (?, ?, ?, ?)";		//Sql Insert Statement
	$stmt = $conn->prepare($sql);

	$stmt->bindParam(1, $userName);
	$stmt->bindParam(2, $userPass);
	$stmt->bindParam(3, $Name);
	$stmt->bindParam(4, $Address);

	$stmt->execute();
	if ($stmt) {
		$_SESSION['Success'] = "Account Created Successfully";
	}
	header("refresh:0; url=../View/index.php");			//refresh and return to the index page
}

//Read All Account 
function getAllAccount() {				//function that retrieve all account and return
	extract($_POST);
	$instance = DBConnection::getInstance();
	$conn = $instance->getConnection();
	$arrAccount = array();
	$count = 0;

	$sql = $conn->prepare("SELECT * FROM account");

	if ($sql->execute(array())) {

		while ($row = $sql->fetch(PDO::FETCH_ASSOC)) {
			$id = $row["id"];
			$UserName = $row["Username"];
			$UserPass = $row["password"];
			$Name = $row["name"];
			$Address = $row["address"];

			$Account = new Account($id, $UserName, $UserPass, $Name, $Address);		//I am creating the Account class to store all the account data into it
			$arrAccount[$count] = $Account;
			$count ++;
		}
		return $arrAccount;
	}
}

//Search
if (isset($_POST['SearchUsr'])) {
	session_start();
	extract($_POST);
	$instance = DBConnection::getInstance();
	$conn = $instance->getConnection();

	$UserName = $_POST['userName'];

	$sql = "SELECT * FROM account WHERE Username = ?";
	$stmt = $conn->prepare($sql);
	$stmt->bindParam(1, $UserName);
	$stmt->execute();
	$rowCount = $stmt->fetchObject();
	$count = $stmt->rowCount();

	if ($count > 0) {
		$_SESSION['AccountId'] = $rowCount->id;
		$_SESSION['userName'] = $rowCount->Username;
		$_SESSION['userPass'] = $rowCount->password;
		$_SESSION['Name'] = $rowCount->name;
		$_SESSION['Address'] = $rowCount->address;
	} else {
		$_SESSION['AccountId'] = '';
		$_SESSION['userName'] = '';
		$_SESSION['userPass'] = '';
		$_SESSION['Name'] = '';
		$_SESSION['Address'] = '';
	}


	header("refresh:0; url=../View/index.php");
}

//Update Account 
if (isset($_POST['UpdateUsr'])) {
	session_start();
	extract($_POST);
	$instance = DBConnection::getInstance();
	$conn = $instance->getConnection();
	
	if ($_POST['accID'] === ""){
		$_SESSION['Failed'] = "cannot be empty";
        echo "<script>window.location = '../View/index.php'</script>";
	}
	else{
		$id =$_POST['accID'];
		$UserPass =$_POST['password'];
		$Name = $_POST['name'];
		$Address = $_POST['address'];
		
		$sql = "UPDATE account SET password = ?, name = ?, address = ? WHERE id = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(1, $UserPass);
        $stmt->bindParam(2, $Name);
        $stmt->bindParam(3, $Address);
		$stmt->bindParam(4, $id);

        $stmt->execute();
		$_SESSION['Success'] = "Account Updated Successfully";
		header("refresh:0; url=../View/index.php");
	}
}

//Delete Account	
if (isset($_POST['DeleteUser'])) {
	session_start();
	extract($_POST);
	$instance = DBConnection::getInstance();
	$conn = $instance->getConnection();
	
	$ID = $_POST['AccID'];

	$sql = "SELECT * FROM account WHERE id = ?";
	$stmt = $conn->prepare($sql);
	$stmt->bindParam(1, $ID);
	$stmt->execute();
	$rowCount = $stmt->fetchObject();
	$count = $stmt->rowCount();
	
	if ($count > 0) {
		$sql = "DELETE FROM account WHERE id = ?";
		$stmt = $conn->prepare($sql);
		$stmt = $conn->prepare($sql);
		$stmt->execute(array($ID));
		$_SESSION['Success'] = "Account Deleted Successfully";
		header("refresh:0; url=../View/index.php");
	}
	else{
		$_SESSION['Failed'] = "Account Not Found";
		header("refresh:0; url=../View/index.php");
	}
}
?>

7 - Create Account Class.

Right click on the Model folder and New>PHP Files to create a class name Account.php under Model Folder to store account variable, constructor, setter and getter.

Code for - Account Class

<?php

Class Account{	
	private $id, $UserName, $UserPass, $Name, $Address;
	
	function __construct($id, $UserName, $UserPass, $Name, $Address) {
		$this->id = $id;
		$this->UserName = $UserName;
		$this->UserPass = $UserPass;
		$this->Name = $Name;
		$this->Address = $Address;
	}
	function getId() {
		return $this->id;
	}

	function getUserName() {
		return $this->UserName;
	}

	function getUserPass() {
		return $this->UserPass;
	}

	function getName() {
		return $this->Name;
	}

	function getAddress() {
		return $this->Address;
	}

	function setId($id) {
		$this->id = $id;
	}

	function setUserName($UserName) {
		$this->UserName = $UserName;
	}

	function setUserPass($UserPass) {
		$this->UserPass = $UserPass;
	}

	function setName($Name) {
		$this->Name = $Name;
	}

	function setAddress($Address) {
		$this->Address = $Address;
	}
}

?>

Code for - ReadAll Class

Code for - index Class

8 - Create View Class.

Right click on the View folder and New>PHP Files to create a class name ReadAll.php under View Folder to display all record from the database account table(Read Method) and index.php for create, update, search and delete record from the database account table.

Code for - ReadAll Class

<?php
session_start();

if (isset($_SESSION['Success'])) {
    unset($_SESSION['Success']);
    ?>
    <script>
        alert("Success");
    </script>
<?php } ?>

<?php
if (isset($_SESSION['Failed'])) {
    unset($_SESSION['Failed']);
    ?>
    <script>
        alert("Failed.");
    </script>
<?php } ?>

	<!--Create Account-->

	
	<!--Read All Account-->
	<head>
		<style>
			table {
				font-family: arial, sans-serif;
				border-collapse: collapse;
				width: 100%;
			  }

			  td, th {
				border: 1px solid #dddddd;
				text-align: left;
				padding: 8px;
			  }

			  tr:nth-child(even) {
				background-color: #dddddd;
			  }
		</style>
			
	</head>
		<fieldset>
			<legend><b>Read All Account :</b></legend>
			<table class="data-table" id="myTable"">
                <thead>
                    <tr>
                        <th>No.</th>
                        <th>ID</th>
                        <th style="width: 200px">Username</th>
                        <th>Password</th>
                        <th>Name</th>                                  
						<th>Address</th>
                    </tr>
                </thead>
                <tbody>  

                    <?php
                    include '../Control/AccountControl.php';				//First you need to include the class you want to use
                    $arrAccount = getAllAccount();							//After you have include the class you can directly call the function/method inside that class, I call getAllAccount to retrieve all Account
					$id = $UserName = $UserPass = $Name = $Address = "";
					$no = 1;
                    foreach ($arrAccount as $value) {
						$id  = $value->getId();
						$UserName = $value->getUserName();
						$UserPass = $value->getUserPass();
						$Name = $value->getName();
						$Address = $value->getAddress();


                        echo '<tr>
                            <td>' . $no . '</td>
							<td>' . $id . '</td>
                            <td>' . $UserName . '</td>
                            <td>' . $UserPass . '</td>
                            <td>' . $Name . '</td>
                            <td>' . $Address . '</td></tr>';												                                     
                   $no++;
                }
                ?>

                </tbody>              
                <tfoot>
                <td></td>
                <td></td>
                <td></td>
                <td></td>
                <td></td>
                <td></td>
                </tfoot>
            </table>
					
		</fieldset>

Code for - index Class

<?php
session_start();

if (isset($_SESSION['Success'])) {		// to receive the response from AccountControl class
	$msg = $_SESSION['Success'];		// get message received from AccountControl Class and put into variable
	unset($_SESSION['Success']);		//To unset this session value
    
	echo '<script type="text/javascript">alert("'.$msg.'");</script>';		// display the message from AccountControl Class
}
?>

<?php
if (isset($_SESSION['Failed'])) {		// to receive the response from AccountControl class
	$msgf = $_SESSION['Failed'];		// get message received from AccountControl Class and put into variable
    unset($_SESSION['Failed']);			//To unset this session value
    echo '<script type="text/javascript">alert("'.$msgf.'");</script>';		// display the message from AccountControl Class
	
} ?>

	<!--Create Account Start-->
<form method="post" action="../Control/AccountControl.php" class="border">		<!--action put the class name where your store all your sql code-->
				
	<div>
		<fieldset>
			<legend><b>Create Function:</b></legend>
		<span class="mainWord" style="text-align: left">Username</span>
		<input type="text" name="userName" id="userName" placeholder="" autocomplete="off">
		
		<br/>
		<span class="mainWord">Password</span>
		<input type="text" name="userPass" id="userName" placeholder="" autocomplete="off">
		
		<br/>
		<span class="mainWord">Name</span>
		<input type="text" name="Name" id="userName" placeholder="" autocomplete="off">
		
		<br/>
		<span class="mainWord">Address</span>
		<input type="text" name="Address" id="userName" placeholder="" autocomplete="off">
		
		<br/><br/>
		 <button type="submit" class="btn" name="AddUser">Create</button>
		 </fieldset>
	</div>
 </form>
<!--Create Account End-->
	

<!--Read All Account Start-->
		<fieldset>
			<legend><b>Read All Account :</b></legend>			
			<form action="ReadAll.php" method="get" target="_blank">			<!--Open in new page ReadAll.php-->
				<button type="submit" class="btn" name="ReadAcc">Read All</button>
			</form>
		</fieldset>
<!--Read All Account End-->
	

<!--Search And Update Start-->
	
		<fieldset>
			<legend><b>Search and Update Account :</b></legend>
			<!--Search Start-->
				<form method="post" action="../Control/AccountControl.php" class="border">
					<span class="mainWord" style="text-align: left">Username</span>
					<input type="text" name="userName" id="userName" placeholder="" autocomplete="off">
					<button type="submit" class="btn" name="SearchUsr">Search</button>
				</form>
			<!--Search End-->
			<br/><br/>
			
			<!--Update Start-->
			<form method="post" action="../Control/AccountControl.php" class="border">
				<span class="mainWord" style="text-align: left">Account ID</span>
				<input type="text" name="accID"  placeholder="" autocomplete="off" readonly style="background-color: rgba(214, 214, 214)" value="<?php echo  $_SESSION['AccountId']; ?>"><br/>
				
				<span class="mainWord" style="text-align: left">Password</span>
				<input type="text" name="password"  placeholder="" autocomplete="off" value="<?php echo  $_SESSION['userPass']; ?>"><br/>

				<span class="mainWord" style="text-align: left">Name</span>
				<input type="text" name="name"  placeholder="" autocomplete="off" value="<?php echo  $_SESSION['Name']; ?>"><br/>

				<span class="mainWord" style="text-align: left">Address</span>
				<input type="text" name="address" placeholder="" autocomplete="off" value="<?php echo  $_SESSION['Address']; ?>"><br/>
				<button type="submit" class="btn" name="UpdateUsr">Update</button>
			</form>
			<!--Update End-->
		</fieldset>
<!--Search And Update End-->

	
<!--Delete Account Start-->
	<fieldset>
		<legend><b>Delete Account :</b></legend>
			<form method="post" action="../Control/AccountControl.php" class="border">
				<span class="mainWord" style="text-align: left">Account ID</span>
				<input type="text" name="AccID" id="userName" placeholder="" autocomplete="off">
				<button type="submit" class="btn" name="DeleteUser">Delete</button>
			</form>
	</fieldset>
<!--Delete Account End-->

9 - Open Page.

After the above steps, just go to your internet browser and type in the URL of : http://localhost/test/view/index.php , inside the page consist of create, read, search and update, delete function.
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0 0 votes
Article Rating

Start typing and press Enter to search

Shopping Cart
0
Would love your thoughts, please comment.x
()
x