simple php MYSQL program with CRUD operation for beginners .

PHP is a open source server scripting language which is used for writing Web Application . It Gives power to simple HTML page to fetch and update dynamic data . It contains all features of programming language and it is light weight also .It Gives tight competition to the ASP.net. It will be running on server side to fetch data from database to show to screen . It will be executed only one time at the time of page request . Its all functionality and syntax is available here . We are also using MYSQL in our example . MYSQL is really effecient and secure sql server it is open source that's why many startup companies relies on it . Many Startup company adopted this technology stack to develop there software . Many famous applications are writing in this languages which includes . Facebook , WordPress and it is said that almost 25% website live now is on wordpress . which is indirectly contain PHP , MYSQL ,APACHE stack . So go for learning it.

 

In this article we will completing student details system task . To know more about task kindly read this POST .

 

Prerequisites :

  1. Visual Studio Code .
  2. PHP Server
  3. MYSQL Server
  4. Apache for running html
  5. phpMyAdmin
  6. w3.css We are using W3.css in our web page to beautify it

All the above technology stack is available in WAMP server . You can download WAMP 2.5 from here and install it to test our software . After installing wamp kindly open http://localhost to get wamp server index page . If opened successfully than you have successfully installed wamp on your local machine . You can find help how to install visual studio code here

STEP 1:Create Database

 

  1. You can create database by going to phpmyadmin . you can create database by clicking new and then give database name as StudentDB and click create .

STEP 2:Create Table

 

  1. You can create table by going inside database and click New to create new Table . and Give Column name and data type for column
  2. Or you can create table through sql Query . Just Write below query in SQL Tab

CREATE TABLE IF NOT EXISTS `student_table` ( `student_id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(100) DEFAULT NULL, `Address` varchar(100) DEFAULT NULL, `Telephone` varchar(100) DEFAULT NULL, `Standard` varchar(100) DEFAULT NULL, `Roll_Number` varchar(100) DEFAULT NULL, PRIMARY KEY (`student_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

 

STEP 3:CREATE PROJECT

 

  1. Create New Folder name as Student . In C:/wamp/www/
  2. Now Create new file name as index.php in that folder .In C:/wamp/www/Student/
  3. Open this folder in Visual Studio Code

 

DESIGN HTML

We will first design HTML by following is code for designing HTML with w3.css beautification . following is the code

 

 

<html> <head> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> </head> <body > <div style="width:100%;text-align:center"> <h1> Student Detail System</h1> </div> <div style="width:100%; align:center;"> <form action="index.php" method="post" class="form" enctype="multipart/form-data"> <div style="margin:10px;display: inline-block;"> Name :<input name="Name" type="text" value="<?php echo (isset($Name))?$Name:'';?>"></div> <div style="margin:10px;display: inline-block;"> Address :<input name="Address" type="text" value="<?php echo (isset($Add))?$Add:'';?>"></div> <div style="margin:10px;display: inline-block;"> Telephone :<input name="Telephone" type="text" value="<?php echo (isset($Tel))?$Tel:'';?>"></div> <div style="margin:10px;display: inline-block;"> Standard :<input name="Standard" type="text" value="<?php echo (isset($Std))?$Std:'';?>"></div> <div style="margin:10px;display: inline-block;" > Roll Number :<input name="RollNumber" type="text" value="<?php echo (isset($RNo))?$RNo:'';?>"></div> <div style="margin:10px;display: inline-block;" ><input name="student_id" style="display:none;" type="text" value="<?php echo (isset($studenId))?$studenId:'';?>"></div> </div> <div class="w3-cell-row w3-center w3-padding"> <input name="submit" class="w3-btn w3-blue w3-border w3-margin" type="submit" value="Insert"/> <input name="Update" class="w3-btn w3-blue w3-border w3-margin" type="submit" value="Update"/> <input name="Delete" class="w3-btn w3-blue w3-border w3-margin" type="submit" value="Delete"/> </form> </div> <?php $dbc=mysqli_connect("localhost","root","","studentdb"); $query = "select * from student_table"; $res = $dbc->query($query); ?> <table class="w3-table-all w3-small "> <tr > <td>Student_ID</td> <td>Name</td> <td>Address</td> <td>Telephone</td> <td>Standard</td> <td>Roll Number</td> <td>Update / Delete</td> </tr> <?php while ($row = $res->fetch_assoc()) { echo "<tr class='w3-hover-blue' style='cursor:pointer;'>"; echo "<td>".$row['student_id']."</td>"; echo "<td>".$row['Name']."</td>"; echo "<td>".$row['Address']."</td>"; echo "<td>".$row['Telephone']."</td>"; echo "<td>".$row['Standard']."</td>"; echo "<td>".$row['Roll_Number']."</td>"; echo "<td><a href='index.php?student_id=".$row['student_id']."'>Select</a></td>"; echo "</tr>"; } ?> </table> </body> </html>

  1. In Above code first we have declare w3.css link to uses all its classes in our php we application .
  2. Then we have designed 5 inputs field with their name as Name,Address,Telephone,Standard , RollNumber So that we can identify them because of their names in PHP Script .
  3. We Are using form to submit all input values
  4. We have put action as index.php . So that again this page is called with input values
  5. We have used php script in input value . So that if $name value is there then only have to show . Otherwise it will show empty .
  6. We have given three button to INSERT , UPDATE AND DELETE operation
  7. Lastly we are calling student_table data in our table through PHP scripting .
  • By first Connecting to Database with root user and password in my case which is empty
  • Then Executing query
  • Then fetch query by fetch_assoc() function

Insert Operation

For Performing insert Operation you have to feel every field as Name, Address etc and then click Insert. As you click insert a form is submitted to index.php as we have mention in action attribute of form . We will handle that submit in php as given below

<?php $dbc=mysqli_connect("localhost","root","","studentdb"); if(isset($_POST['submit'])) { $Name = $_POST['Name']; $Address = $_POST['Address']; $Telephone = $_POST['Telephone']; $Standard = $_POST['Standard']; $RollNumber = $_POST['RollNumber']; $query = "Insert into student_table(Name,Address,Telephone,Standard,Roll_Number) Values('$Name','$Address','$Telephone','$Standard','$RollNumber')"; $res = $dbc->query($query); header("Refresh:0"); } ?>

  1. firstly we have connected to database name as studentdb
  2. Then we have checked that is it form submit action by using isset() function
  3. then we have taken all the values from POST by its name which we have provided earlier
  4. Then Executed Insert Query to insert data to student_table
  5. Then we have refreshed the page by using header("Refresh:0") to update table data . As i told you that PHP is executed only once at the time of request at the server

Select Operation

For Selecting you need to click on select hyperlink besides to record in table as you click on select hyperlink it will redirect to index.php with student_id as GET parameter to send it . and it is handle by following code

if(isset($_GET['student_id'])) { $query = "select * from student_table where student_id='".$_GET['student_id']."'"; $res = $dbc->query($query); $row = $res->fetch_assoc(); $Name=$row['Name']; $Add=$row['Address']; $Tel=$row['Telephone']; $Std=$row['Standard']; $RNo=$row['Roll_Number']; $studenId=$row['student_id']; }

Update Operation

firstly you need to select record from table as you select from table it will appears in input fields and then just update it by clicking update button . As you click update button Form is submitted at index.php and handled by following code

if(isset($_POST['Update'])) { $Name = $_POST['Name']; $Address = $_POST['Address']; $Telephone = $_POST['Telephone']; $Standard = $_POST['Standard']; $RollNumber = $_POST['RollNumber']; $SID=$_POST['student_id']; $query = "UPDATE student_table SET Name='$Name',Address='$Address',Telephone='$Telephone',Standard='$Standard',Roll_Number='$RollNumber' WHERE student_id=$SID"; // echo $query; $res = $dbc->query($query); // header("Refresh:0;url=index.php"); }

Delete Operation

firstly you need to select record from table as you select from table it will appears in input fields and then just update it by clicking DELETE button . As you click update button Form is submitted at index.php and handled by following code

if(isset($_POST['Delete'])) { $SID=$_POST['student_id']; $query = "DELETE FROM student_table WHERE student_id=$SID"; $res = $dbc->query($query); //header("Refresh:0;url=index.php"); }

 

Full code

<?php $dbc=mysqli_connect("localhost","root","","studentdb"); if(isset($_POST['submit'])) { $Name = $_POST['Name']; $Address = $_POST['Address']; $Telephone = $_POST['Telephone']; $Standard = $_POST['Standard']; $RollNumber = $_POST['RollNumber']; $query = "Insert into student_table(Name,Address,Telephone,Standard,Roll_Number) Values('$Name','$Address','$Telephone','$Standard','$RollNumber')"; $res = $dbc->query($query); header("Refresh:0"); }else if(isset($_GET['student_id'])) { $query = "select * from student_table where student_id='".$_GET['student_id']."'"; $res = $dbc->query($query); $row = $res->fetch_assoc(); $Name=$row['Name']; $Add=$row['Address']; $Tel=$row['Telephone']; $Std=$row['Standard']; $RNo=$row['Roll_Number']; $studenId=$row['student_id']; }else if(isset($_POST['Update'])) { $Name = $_POST['Name']; $Address = $_POST['Address']; $Telephone = $_POST['Telephone']; $Standard = $_POST['Standard']; $RollNumber = $_POST['RollNumber']; $SID=$_POST['student_id']; $query = "UPDATE student_table SET Name='$Name',Address='$Address',Telephone='$Telephone',Standard='$Standard',Roll_Number='$RollNumber' WHERE student_id=$SID"; // echo $query; $res = $dbc->query($query); // header("Refresh:0;url=index.php"); } else if(isset($_POST['Delete'])) { $SID=$_POST['student_id']; $query = "DELETE FROM student_table WHERE student_id=$SID"; $res = $dbc->query($query); //header("Refresh:0;url=index.php"); } ?> <html> <head> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> </head> <body > <div style="width:100%;text-align:center"> <h1> Student Detail System</h1> </div> <div style="width:100%; align:center;"> <form action="index.php" method="post" class="form" enctype="multipart/form-data"> <div style="margin:10px;display: inline-block;"> Name :<input name="Name" type="text" value="<?php echo (isset($Name))?$Name:'';?>"></div> <div style="margin:10px;display: inline-block;"> Address :<input name="Address" type="text" value="<?php echo (isset($Add))?$Add:'';?>"></div> <div style="margin:10px;display: inline-block;"> Telephone :<input name="Telephone" type="text" value="<?php echo (isset($Tel))?$Tel:'';?>"></div> <div style="margin:10px;display: inline-block;"> Standard :<input name="Standard" type="text" value="<?php echo (isset($Std))?$Std:'';?>"></div> <div style="margin:10px;display: inline-block;" > Roll Number :<input name="RollNumber" type="text" value="<?php echo (isset($RNo))?$RNo:'';?>"></div> <div style="margin:10px;display: inline-block;" ><input name="student_id" style="display:none;" type="text" value="<?php echo (isset($studenId))?$studenId:'';?>"></div> </div> <div class="w3-cell-row w3-center w3-padding"> <input name="submit" class="w3-btn w3-blue w3-border w3-margin" type="submit" value="Insert"/> <input name="Update" class="w3-btn w3-blue w3-border w3-margin" type="submit" value="Update"/> <input name="Delete" class="w3-btn w3-blue w3-border w3-margin" type="submit" value="Delete"/> </form> </div> <?php $query = "select * from student_table"; $res = $dbc->query($query); ?> <table class="w3-table-all w3-small "> <tr > <td>Student_ID</td> <td>Name</td> <td>Address</td> <td>Telephone</td> <td>Standard</td> <td>Roll Number</td> <td>Update / Delete</td> </tr> <?php while ($row = $res->fetch_assoc()) { echo "<tr class='w3-hover-blue' style='cursor:pointer;'>"; echo "<td>".$row['student_id']."</td>"; echo "<td>".$row['Name']."</td>"; echo "<td>".$row['Address']."</td>"; echo "<td>".$row['Telephone']."</td>"; echo "<td>".$row['Standard']."</td>"; echo "<td>".$row['Roll_Number']."</td>"; echo "<td><a href='index.php?student_id=".$row['student_id']."'>Select</a></td>"; echo "</tr>"; } ?> </table> </body> </html>







Taher Ali Badnawarwala

Taher Ali, drives to create something special, He loves swimming ,family and AI from depth of his heart . He loves to write and make videos about AI and its usage


Leave a Comment


No Comments Yet

Leave a Reply

Your email address will not be published. Required fields are marked *