CRUD EXAMPLE for PHP Beginner
Test your application by just typing to browser:
http://127.0.0.1/crud_sample/index.php
Requirements
Create folder to your web server located at htdocs for my case Im using XAMPP this will be my directory
C:\xampp\htdocs\crud_sample
Put all files named index.php,add.php, and view.php inside your folder you created.
Create database named crud_sample
This will be the example
- CREATE DATABASE `crud_sample` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
USE `crud_sample`;
Create table from your database crud_sample name tbl_personnel
This will be the example
- CREATE TABLE IF NOT EXISTS `tbl_personnel` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(30) COLLATE utf8_bin NOT NULL,
`middlename` varchar(30) COLLATE utf8_bin NOT NULL,
`lastname` varchar(30) COLLATE utf8_bin NOT NULL,
`gender` varchar(8) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=10 ;
1. Create an index.php File.
This will be the inputs.
<table align="center" border="1" style="padding:5px;" width="100%">
<tr>
<td style="padding:10px; text-align:center;"><a href="view.php" target="view_main">View Personnel List</a></td>
<td style="padding:10px; text-align:center;"><a href="add.php" target="view_main">Add Personnel</a></td>
</tr>
<tr>
<td colspan="2" align="center" height="600px"><iframe name = "view_main" width="100%" height="100%"></iframe></td>
</tr>
</table>
2. Create add.php file
This will be the inputs
<?php
$con = mysql_connect("localhost","root",""); //Database Initialization and Connection properties
mysql_select_db("crud_sample",$con); //Database Initialization and Connection properties
// If Button Save was click this is the code will executed
if(isset($_POST['save'])){
// check fields for wheither not empty or empty
if(!empty($_POST['firstname']) && !empty($_POST['middlename']) && !empty($_POST['lastname'])){
// initialize the variable to be the value of the textbox
$fname = $_POST['firstname'];
$mname = $_POST['middlename'];
$lname=$_POST['lastname'];
$gender = $_POST['gender'];
//Sql syntax for INSERT Statement
$query = "INSERT INTO tbl_personnel (firstname,middlename,lastname,gender) values ('".$fname."','".$mname."','".$lname."','".$gender."')";
$result = mysql_query($query);
if($result){
$msg = " Successfully Save New Personnel ($fname $mname $lname)";
}
}else{
$msg= " All fields are required!";
}
}
?>
//Basic HTML Form
<form method="POST">
<h5><?php if(isset($msg)){ echo "<font color = 'RED' >$msg</font>"; }else { }?></h5>
<table>
<tr>
<td>First Name</td>
<td> : <input type="text" name="firstname"></td>
</tr>
<tr>
<td>Middle Name</td>
<td> : <input type="text" name="middlename"></td>
</tr>
<tr>
<td>Last Name</td>
<td> : <input type="text" name="lastname"></td>
</tr>
<tr>
<td>Gender</td>
<td> : <select name="gender">
<option>Male</option>
<option>Female</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit" name="save" value="Save"></td>
</tr>
</table>
</form>
3. Create a view.php file
This will be the inputs
<?php
$con = mysql_connect("localhost","root","");
mysql_select_db("crud_sample",$con);
// if the link Edit will click this is the code will executed
if(isset($_GET['id_edit'])){
$id = $_GET['id_edit'];
// syntax for SQL SELECT statement
$query_edit="SELECT * FROM tbl_personnel WHERE id = '$id'";
$result_edit = mysql_query($query_edit);
//fetching all data from database tables to put in respective variables.
while($row_edit = mysql_fetch_array($result_edit)){
$fname = $row_edit[1];
$mname = $row_edit[2];
$lname = $row_edit[3];
$gender = $row_edit[4];
}
// if the button update will click this is the code will executed
if(isset($_POST['update'])){
if(!empty($_POST['firstname']) && !empty($_POST['middlename']) && !empty($_POST['lastname'])){
$fname1 = $_POST['firstname'];
$mname1 = $_POST['middlename'];
$lname1=$_POST['lastname'];
$gender1 = $_POST['gender'];
// syntax for SQL UPDATE statement
$query = "UPDATE tbl_personnel SET firstname='$fname1', middlename='$mname1', lastname='$lname1', gender='$gender1' WHERE id = {$id}";
$result = mysql_query($query);
if($result){
$msg = " Successfully Update the record number $id";
$fname = "";
$mname = "";
$lname = "";
$gender = "";
}
}else{
$msg= " All fields are required!";
}
}
?>
<form method="POST">
<h5><?php if(isset($msg)){ echo "<font color = 'RED' >$msg</font>"; }else { }?></h5>
<table>
<tr>
<td>First Name</td>
<td> : <input type="text" name="firstname" value="<?php echo $fname;?>"></td>
</tr>
<tr>
<td>Middle Name</td>
<td> : <input type="text" name="middlename" value="<?php echo $mname;?>"></td>
</tr>
<tr>
<td>Last Name</td>
<td> : <input type="text" name="lastname" value="<?php echo $lname;?>"></td>
</tr>
<tr>
<td>Gender</td>
<td> : <select name="gender">
<option>Male</option>
<option <?php if($gender =="Female") echo "selected";?>>Female</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit" name="update" value="Update"></td>
</tr>
</table>
</form>
<?php
}
// if the link Delete will click this is the code will executed
if(isset($_GET['id_delete'])){
$id = $_GET['id_delete'];
$query_delete = "DELETE FROM tbl_personnel WHERE id = {$id}";
$result_delete = mysql_query($query_delete);
if($result_delete){
echo "ID number : <font color='RED'><b>$id</b></font> Successfully Deleted From DATABASE";
}
}
$table = "<table border=1px; style='padding:2px;'>
<tr>
<td style='padding:5px;font-weight:bold;'>ID</td>
<td style='padding:5px;font-weight:bold;'>First Name</td>
<td style='padding:5px;font-weight:bold;'>Middle Name</td>
<td style='padding:5px;font-weight:bold;'>Last Name</td>
<td style='padding:5px;font-weight:bold;'>Gender</td>
<td style='padding:5px;font-weight:bold;'>Action</td>
</tr>";
$query = "SELECT * FROM tbl_personnel";
$result = mysql_query($query);
$number_rows = mysql_num_rows($result);
// check if the number of rows is greater than 1 or equal. If so, the value from database will fecth and displayed
if($number_rows >= 1){
// fetch all data from database
while($row = mysql_fetch_array($result)){
// Display all data to the table
$table.="<tr>
<td style='padding:5px;'>".$row[0]."</td>
<td style='padding:5px;'>".$row[1]."</td>
<td style='padding:5px;'>".$row[2]."</td>
<td style='padding:5px;'>".$row[3]."</td>
<td style='padding:5px;'>".$row[4]."</td>
<td style='padding:5px;'><a href='?id_edit=".$row[0]."'>Edit</a> <a href='?id_delete=".$row[0]."'>Delete</a></td>
</tr>";
}
$table.="</table>";
}else{
$table.="<tr><td colspan='6' align='center'>No Data</td></tr></table>";
}
echo $table;
?>
Feel Free to send comment....
Happy Coding
How to bind jtable from your database table using netbeans
1. Create a JFrame that holds your Jtable...
2. From Pallete click and drag Jtable to the JFrame.
. Click Jtable to be selected and right click then choose bind -> elements then click.
4. Window for binding jtable will popup. click import data from button. Select new connection for connection string is not set in the selection and click. Select the driver name according to your chosen database.
5. Fill-up neccessary data according to machine configuration. Then click ok. window popup that display your connection and selection of table to be bind in jtable. Then click ok and wait for data to import.
6.From Left portion of the window are those fields that are available and from the right are fields that are selected to view in the jtable. Then click ok and compile and run your program...
Happy coding....