Showing posts with label crud in php and mysqli. Show all posts
Showing posts with label crud in php and mysqli. Show all posts

Friday, December 28, 2018

PERSONNEL RECORD APPLICATION IN PHP AND MYSQL

A simple personnel record application that I wrote in PHP and MySQL in my spare time. 

I am currently accepting programming work, it projects, school 

programming projects, thesis and capstone projects, IT consulting 

work, computer tutorials, and web development work kindly contact me in the following email address for further details.  If you want to advertise on my website kindly contact me also in my email address also. Thank you.
My email address is the following jakerpomperada@gmail.com, jakerpomperada@aol.com, and jakerpomperada@yahoo.com.

My mobile number here in the Philippines is 09173084360.

My telephone number at home here in Bacolod City, Negros Occidental Philippines is  +63 (034) 4335675.

My personal website is http://www.jakerpomperada.com





Sample Program Output



Program Listing

db.php

<?php 

$host = "localhost";
$username = "root";
$password = "";
$dbname = "db";

$con = mysqli_connect($host, $username, $password, $dbname);

if (!$con) {
die("Connection failed" . mysqli_errno($con));
}

 ?>

index.php

<?php 

// including the database connection
include_once 'db.php';


// Inserting data into the database if submit button is clicked
if (isset($_POST['submit'])) {
$first_name = $_POST['fname'];
$last_name = $_POST['lname'];
$email = $_POST['email'];
$date_of_birth = $_POST['dob'];
    
    $first_name = strtoupper($first_name);
    $last_name = strtoupper($last_name);
    $email =strtolower($email);

if (!empty($first_name && $last_name && $email && $date_of_birth)) {
$insert_query = mysqli_query($con, "INSERT INTO users(f_name, l_name, email, dob) VALUES('$first_name', '$last_name', '$email', '$date_of_birth')");
if ($insert_query > 0) {
$msg = "Record Successfully Added to the Database";
}else {
$msg = "Submission Failed";
}
}
}


// update data if update button is clicked
if (isset($_POST['update'])) {
  $first_name = $_POST['fname'];
  $last_name = $_POST['lname'];
  $email = $_POST['email'];
  $date_of_birth = $_POST['dob'];
  $hidden_id = $_POST['hidden_id'];
   
  $first_name = strtoupper($first_name);
  $last_name = strtoupper($last_name);
  $email =strtolower($email);
  
  if (!empty($first_name && $last_name && $email && $date_of_birth)) {
    
    $update_query = mysqli_query($con, "UPDATE users SET f_name ='$first_name', l_name ='$last_name', email ='$email', dob ='$date_of_birth' WHERE id ='$hidden_id'");
   
    if ($update_query > 0) {
     
      $msg = "Database record updated successfully";
    
     }else {
      $msg = "Database record Update Failed";
    }
  }
}

 ?>


<!DOCTYPE html>
<html lang="en">

<head>

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Crud Apllication</title>
    <!-- Bootstrap Core CSS -->
    <link href="css/bootstrap.min.css" rel="stylesheet">

    <!-- Custom CSS -->
    <link href="css/custom.css" rel="stylesheet">

    <script src="js/jquery.js"></script>
    <script src="js/bootstrap.min.js"></script>
    <script src="js/bootbox.min.js"></script>


</head>

 <body>

    <!-- Navigation -->
    <nav class="navbar navbar-inverse navbar-fixed-top" role="navigation">
        <div class="container">
            <!-- Brand and toggle get grouped for better mobile display -->
            <div class="navbar-header">
                <button type="button" class="navbar-toggle" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1">
                    <span class="sr-only">Toggle navigation</span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                </button>
             <p class="" style="margin-left: 360px; color: white; padding-top: 6px; font-size: 20px;"><?php if (isset($msg)) echo $msg; ?></p>
            </div>
            
        </div>
        <!-- /.container -->
    </nav>

    <!-- Page Content -->
    <div class="container">

          <div class="row"> 
            <div class="col-md-12">
                <h1 class="page-header text-center">
                    PERSONNEL RECORD APPLICATION IN PHP AND MYSQL
                    <br>
                    <small>Create, Read, Update & Delete Database Records</small>
            </div>

           </div>
        

            <form class="form-horizontal" action="" method="POST" >    
                <div class="form-group">
                    <div class="col-md-4 col-md-offset-2">
                        <input type="text" name="fname" class="form-control " placeholder="First Name" value="<?php if (isset($first_name)) echo $first_name;?>" auto="off">
                    </div>

                    <div class="col-md-4">
                        <input type="text" name="lname" class="form-control" placeholder="Last Name" value="<?php if (isset($last_name)) echo $last_name;?>">
                    </div>
                </div>

                <div class="form-group">
                    <div class="col-md-4 col-md-offset-2">
                        <input type="email" name="email" class="form-control " placeholder="Email Address" value="<?php if (isset($email)) echo $email;?>">
                    </div>

                    <div class="col-md-4">
                        <input type="date" name="dob" class="form-control" placeholder="Date of Birth" value="<?php if (isset($dob)) echo $dob;?>">
                    </div>
                </div>
                <div class="form-group">
               <div class="col-md-8 col-lg-offset-2">
                <button class="btn btn-block btn-success btn-lg" name="submit" type="submit">Add Record<span class="glyphicon glyphicon-chevron-right"></span></button>
                </div>
               </div>
            </form>
           
            <hr>
       
        <div class="col-md-12">
            <table class="table table-striped table-bordered table-responsive">
                <thead>
                    <tr class="success">
                        <th>Personnel ID Number</th>
                        <th>Full Name</th>
                        <th>Email</th>
                        <th>Date of Birth</th>
                        <th>Actions</th>
                    </tr>
                </thead>
                <tbody>

                <?php
                  $i = 0; 
                      $read_query = mysqli_query($con, "SELECT * FROM users");
                        while ($rows = mysqli_fetch_assoc($read_query)) { 
                        $i++; ?>
               
                      <tr>
                      <td><?php echo $i; ?></td>
                      <td><?php echo $rows['f_name']."&nbsp;".$rows['l_name']; ?></td>
                        <td><?php echo $rows['email']; ?></td>
                        <td><?php echo $rows['dob']; ?></td>
                        <td>
                            <a href="update.php?update=<?php echo $rows['id'];?>" class="btn btn-primary">Update</a>
                            &nbsp&nbsp
            <a class="delete text-danger" data-id="<?php echo $rows['id']; ?>" href="javascript:void(0)">
                <i class="glyphicon glyphicon-trash"></i>
                </a>
                        </td>
                       </tr>


                    <?php }    ?>

                 
                </tbody>
            </table>
        </div>

    </div>

</body>

<script src="js/myscript.js"></script>

</html>


update.php

<?php 

// including the database connection
include_once 'db.php';


// show users details if update button is clicked
if (isset($_GET['update'])) {
  $update_id = $_GET['update'];
  $user_data = mysqli_fetch_assoc(mysqli_query($con, "SELECT * FROM users WHERE id='$update_id'"));
  $first_name = $user_data['f_name'];
  $last_name = $user_data['l_name'];
  $email = $user_data['email'];
  $dob = $user_data['dob'];
}


 ?>


<!DOCTYPE html>
<html lang="en">

<head>

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Crud Application</title>
    <!-- Bootstrap Core CSS -->
    <link href="css/bootstrap.min.css" rel="stylesheet">

    <!-- Custom CSS -->
    <link href="css/custom.css" rel="stylesheet">
    
    <script src="js/jquery.js"></script>
    <script src="js/bootstrap.min.js"></script>
</head>

 <body>

  <style type="text/css">
        body{
              padding-top: 30px;
        }
  </style>

    <!-- Navigation -->
    <nav class="navbar navbar-inverse navbar-fixed-top" role="navigation">
        <div class="container">
            <!-- Brand and toggle get grouped for better mobile display -->
            <div class="navbar-header">
                <button type="button" class="navbar-toggle" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1">
                    <span class="sr-only">Toggle navigation</span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                </button>
             <p class="" style="margin-left: 350px; color: white; padding-top: 6px;"><?php if (isset($msg)) echo $msg; ?></p>
            </div>
            
        </div>
        <!-- /.container -->
    </nav>

    <!-- Page Content -->
    <div class="container">

          <div class="row">  <!-- Blog Entries Column -->
            <div class="col-md-12">
                <h1 class="page-header text-center">
                    PERSONNEL RECORD APPLICATION IN PHP AND MYSQL
                    <br>
                    <small>Update Database Records</small>
            </div>

           </div>
        

            <form class="form-horizontal" action="index.php" method="POST" >    
                <div class="form-group">
                    <div class="col-md-4 col-md-offset-2">
                        <input type="text" name="fname" class="form-control " placeholder="First Name" value="<?php if (isset($first_name)) echo $first_name;?>">
                    </div>

                    <div class="col-md-4">
                        <input type="text" name="lname" class="form-control" placeholder="Last Name" value="<?php if (isset($last_name)) echo $last_name;?>">
                    </div>
                </div>

                <div class="form-group">
                    <div class="col-md-4 col-md-offset-2">
                        <input type="email" name="email" class="form-control " placeholder="Email Address" value="<?php if (isset($email)) echo $email;?>">
                    </div>

                    <div class="col-md-4">
                        <input type="date" name="dob" class="form-control" placeholder="Date of Birth" value="<?php if (isset($dob)) echo $dob;?>">
                    </div>

                    <input type="hidden" name="hidden_id" value="<?php if (isset($_GET['update'])) echo $_GET['update']; ?>">

                </div>
                <div class="form-group">
               <div class="col-md-8 col-lg-offset-2">
                <button class="btn btn-block btn-info btn-lg" name="update" type="submit">Update Record<span class="glyphicon glyphicon-chevron-right"></span></button>
                <hr class="text-danger">
                </div>
               </div>
            </form>
           
            <hr>
       
    </div>

</body>

</html>

delete.php

<?php

include_once 'db.php';
if ($_REQUEST['delete']) {
$d_id = $_REQUEST['delete'];
$delete_query = mysqli_query($con, "DELETE FROM users WHERE id='$d_id'");
        
        if ($delete_query > 0) {
echo "Record Deleted Successfully From the Database...";
}
}


db.sql


-- phpMyAdmin SQL Dump
-- version 4.8.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Dec 28, 2018 at 07:23 AM
-- Server version: 10.1.37-MariaDB
-- PHP Version: 7.3.0

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `db`
--

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `f_name` varchar(200) NOT NULL,
  `l_name` varchar(200) NOT NULL,
  `email` varchar(200) NOT NULL,
  `dob` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `f_name`, `l_name`, `email`, `dob`) VALUES
(4, 'JAKE RODRIGUEZ', 'POMPERADA', 'jakerpomperada@gmail.com', '1978-06-08'),
(5, 'JACOB SAMUEL', 'POMPERADA', 'jacobsamuel@yahoo.com', '2013-04-19'),
(6, 'JULIANNA RAE', 'POMPERADA', 'iyapomperada@aol.com', '2014-07-04'),
(7, 'MA. JUNALLIE ', 'POMPERADA', 'alliefpomperada@gmail.com', '1971-08-25');

--
-- Indexes for dumped tables
--

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;








Monday, March 5, 2018

User Record System in PHP and MySQLI

A simple crud application that I wrote using PHP and MySQLI which is already compliant with the new version of PHP.  

My email address are the following jakerpomperada@gmail.com and jakerpomperada@yahoo.com.

My mobile number here in the Philippines is 09173084360.

My telephone number at home here in Bacolod City, Negros Occidental Philippines is  +63 (034) 4335675.
 







Sample Program Output


Program Listing

add.php

<html>
<head>
<link rel="stylesheet" type="text/css" href="mystyle.css">
    <title>User Record System Using PHP and MySQLI</title>


</head>
<body>

<?php
 require_once 'connections.php';

if($_POST) {
    $name = $_POST['name'];
    $age = $_POST['age'];

    $sql = "INSERT INTO person (name,age) VALUES ('$name', '$age')";
    if($connect->query($sql) === TRUE) {
echo "<p>New Record Successfully Created</p>";
        echo "<a href='create.php'><button type='button'>Back</button></a>";
        echo "<a href='index.php'><button type='button'>Home</button></a>";
    } else {
        echo "Error " . $sql . ' ' . $connect->connect_error;
    }

    $connect->close();
}

  
?>
</body>
</html>


delete.php

<?php 

require_once 'connections.php';

if($_GET['id']) {
$id = $_GET['id'];

$sql = "SELECT * FROM person WHERE id = {$id}";
$result = $connect->query($sql);
$data = $result->fetch_assoc();

$connect->close();
?>

<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" type="text/css" href="mystyle.css">
<title>Delete User Record</title>
</head>
<body>

<h3>Do you really want to delete the record of
<?php echo strtoupper($data['name'].'?');  ?>
</h3>
<form action="remove.php" method="post">

<input type="hidden" name="id" value="<?php echo $data['id'] ?>" />
<button type="submit">Ok</button>
<a href="index.php"><button type="button">Back</button></a>
</form>

</body>
</html>

<?php
}
?>

edit.php

<?php 

require_once 'connections.php';

if($_GET['id']) {
$id = $_GET['id'];

$sql = "SELECT * FROM person WHERE id = {$id}";
$result = $connect->query($sql);

$data = $result->fetch_assoc();

$connect->close();

?>

<!DOCTYPE html>
<html>
<head>
<title>Edit Users</title>
<link rel="stylesheet" type="text/css" href="mystyle.css">
</head>
<body>

<fieldset>
<legend>Edit Users</legend>

<form action="update.php" method="post">
<table cellspacing="0" cellpadding="0">
<tr>
<th>Name</th>
<td><input type="text" name="name" placeholder="Name" value="<?php echo $data['name'] ?>" /></td>
</tr>
<tr>
<th>Age</th>
<td><input type="text" name="age" placeholder="Age" value="<?php echo $data['age'] ?>" /></td>
</tr>
<tr>
<input type="hidden" name="id" value="<?php echo $data['id']?>" />
<td ><button id="someTable" type="submit">Save Changes</button>&nbsp;&nbsp;
<a href="index.php"><button type="button">Back</button></a></td>
</tr>
</table>
</form>

</fieldset>

</body>
</html>

<?php
}
?>


index.php

<html>
<?php require_once 'connections.php'; ?>
<!DOCTYPE html>
<<br>
 <h3 align="center"> User Record System Using PHP and MySQLI </h3>html>
<head>
<link rel="stylesheet" type="text/css" href="mystyle.css">
    <title>User Record System Using PHP and MySQLI</title>


</head>
<body>
 <br>
<div class="manageUser">
    <a href="create.php"><button type="button">Add User</button></a>
    <table border="1" cellspacing="0" cellpadding="0">
        <thead>
            <tr>
                <th>Name</th>
                <th>Age</th>
<th>Option</th>
                
            </tr>
        </thead>
        <tbody>
             <?php
            $sql = "SELECT * FROM person";
            $result = $connect->query($sql);

            if($result->num_rows > 0) {
                while($row = $result->fetch_assoc()) {
                    echo "<tr>
                        <td>".$row['name']."</td>
                        <td>".$row['age']."</td>
                         <td>
                            <a href='edit.php?id=".$row['id']."'><button type='button'>Edit</button></a>
                            <a href='delete.php?id=".$row['id']."'><button type='button'>Remove</button></a>
                        </td>
                    </tr>";
                }
            } else {
                echo "<tr><td colspan='5'><center>No Data Avaliable</center></td></tr>";
            }
            ?>
        </tbody>
    </table>
</div>

</body>
</html>

update.php

<html>
<head>
<link rel="stylesheet" type="text/css" href="mystyle.css">
    <title>User Record System Using PHP and MySQLI</title>


</head>
<body>

<?php 

require_once 'connections.php';

if($_POST) {
$name = $_POST['name'];
$age = $_POST['age'];
$id = $_POST['id'];

$sql  = "UPDATE person SET name = '$name',age = '$age' WHERE id = {$id}";
if($connect->query($sql) === TRUE) {
echo "<p>Succcessfully Updated</p>";
echo "<a href='edit.php?id=".$id."'><button type='button'>Back</button></a>";
echo "<a href='index.php'><button type='button'>Home</button></a>";
} else {
echo "Erorr while updating record : ". $connect->error;
}

$connect->close();

}

?>

</body>
</html>


connections.php

<?php  
$localhost = "localhost"; 
$username = "root"; 
$password = ""; 
$dbname = "user"; 

// create connection 
$connect = new mysqli($localhost, $username, $password, $dbname); 

// check connection 
if($connect->connect_error) {
    die("connection failed : " . $connect->connect_error);
} else {
    // echo "Successfully Connected";
}
  
?>


create.php

<!DOCTYPE html>
<html>
<head>
    <title>Add Member</title>

    <link rel="stylesheet" type="text/css" href="mystyle.css">
</head>
<body>

<fieldset>
    <legend>Add User</legend>

    <form action="add.php" method="post">
        <table cellspacing="0" cellpadding="0">
            <tr>
                <th>Name</th>
                <td><input type="text" name="name" placeholder="Name" /></td>
            </tr>     
            <tr>
                <th>Age</th>
                <td><input type="text" name="age" placeholder="Age" /></td>
            </tr>
            
            <tr>
                <td><button type="submit">Save Changes</button></td>
                <td><a href="index.php"><button type="button">Back</button></a></td>
            </tr>
        </table>
    </form>

</fieldset>

</body>
</html>

remove.php

<html>
<head>
<link rel="stylesheet" type="text/css" href="mystyle.css">
    <title>User Record System Using PHP and MySQLI</title>


</head>
<body>
<?php 

require_once 'connections.php';

if($_POST) {
$id = $_POST['id'];

$sql = "DELETE FROM person WHERE  id = {$id}";
if($connect->query($sql) === TRUE) {
echo "<link rel='stylesheet' type='text/css href='mystyle.css'>";
echo "<p>Record Sucessfully Remove from the Database !!!</p>";
echo "<a href='index.php'><button type='button'>Back</button></a>";
} else {
echo "Error removing record : " . $connect->error;
}

$connect->close();
}

?>

</body>
<html>

mystyle.css


            
        .manageUser {
            width: 50%;
            margin: auto;
        }

        table {
font-family:arial;
font-weight:bold;
size:18px;
            width: 100%;
            margin-top: 20px;
        }

        body {
background-color:lightgreen;
font-family:arial;
font-weight:bold;
size:18px;
}
fieldset {
margin: auto;
margin-top: 100px;
width: 50%;
}

table tr th {
padding-top: 10px;
}
user.sql

-- phpMyAdmin SQL Dump -- version 3.1.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Jan 04, 2014 at 06:55 AM -- Server version: 5.1.30 -- PHP Version: 5.2.8 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `user` -- -- -------------------------------------------------------- -- -- Table structure for table `person` -- CREATE TABLE IF NOT EXISTS `person` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `age` int(3) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; -- -- Dumping data for table `person` -- INSERT INTO `person` (`id`, `name`, `age`) VALUES (1, 'Manuel Uy', 36), (2, 'Jorge Vargas', 12), (3, 'Maria Buenavista', 22);