Export mysqli resultset as excel with nested table in PhP

 <?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);
$con = mysqli_connect("localhost", "root", "", "tablename");
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$output = '';
$stmt3 = $con->prepare("SELECT id,registrationid,name,email,mobile,nationality,state,district,gender from adv33");
$stmt3->execute();
$stmt3->bind_result($id, $registrationid, $name, $email, $mobile, $nationality, $state, $district, $gender);
$stmt3->store_result();
if ($stmt3->num_rows > 0) {
    $output .= '
    <table border="1">
                    <tr>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Mobile</th>                      
                        <th>Nationality</th>
                        <th>State</th>
                        <th>District</th>
                        <th>Gender</th>                                            
                    </tr>
  ';
    while ($stmt3->fetch()) {
        $output .= '
                    <tr>                         
                         <td>' . $name . '</td>
                         <td>' . $email . '</td>
                         <td>' . $mobile . '</td>                         
                         <td>' . $nationality . '</td>
                         <td>' . $state . '</td>
                         <td>' . $district . '</td>
                         <td>' . $gender . '</td>                         
                    </tr>';
        $output .= '                  
         <table border="1">
         <tr>
         <td><b>Post</b></td>
         <td><b>Company</b></td>         
           <td><b>From Date</b></td>
            <td><b>To Date</b></td>
            <td><b>Scale of Pay</b></td>          
   ';
        $stmt4 = $con->prepare("SELECT slno,email,post,company,fromdate,todate,scale FROM adv33exp  where email ='$registrationid'");
        $stmt4->execute();
        /* bind result variables */
        $stmt4->bind_result($slno, $email, $post, $company, $fromdate, $todate, $scale);
        $stmt4->store_result();
        if ($stmt4->num_rows > 0) {
            /* fetch value */
            while ($stmt4->fetch()) {
                $output .= '
                     <tr>
                         <td>' . $post . '</td>
                         <td>' . $company . '</td>                        
                         <td>' . $fromdate . '</td>
                         <td>' . $todate . '</td>
                         <td>' . $scale . '</td>                         
                    </tr>
                ';
            }
        }
    }
    $output .= '</table>';
    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=Applicants.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
    echo $output;
}
?>

How to get hidden field value in jquery?

JS Code 

<script>

      $(document).ready(function(){   

        var userid = $('#userid').val();

    }

</script>

HTML Code

<input type="hidden" name="userid" id="userid" value="8"/>   

Software Development: Iterative and Evolutionary Development

Iterative and Evolutionary Development

• Involves early programming and testing of a partial system, in repeating cycles.
• Relies on short quick development steps (or iterations), feedback and adaptation to clarify the requirements and design so successively enlarging and refining a system. 
• Normally assumes that the development starts before all requirements are defined in detail, feedback is used to clarify and improve the evolving specifications.
• Each iteration will include requirements, analysis, design, implementation, and test. 
• Iterative feedback and evolution leads towards the desired system. The requirements and design instability lowers over time.

Current research demonstrates that iterative methods are associated with higher success and productivity rates, and lower defect levels.

Timeboxing

A key idea is that iterations are timeboxed, or fixed in length.

• Most iterative methods recommend in iteration length between 2 – 6 weeks.
• If it seems that it will be difficult to meet the deadline, the recommended response is to de-scope

De-scoping: removing tasks or requirements from the iteration, and including them in a future iteration, rather than slipping the completion date.

Iterative and Evolutionary Development (also known as iterative and inceremental development; spiral development and evolutionary development)

Build-Feedback-Adapt Cycles

In complex changing systems, feedback and adaptation are key ingredients for success:

• Feedback from early development, programmers trying to read specifications, and client demos: to refine the requirements.
• Feedback from tests and developers : to refine the design and models.
• Feedback from the progress of the team tackling early features : to refine the schedule and estimates.

Benefits of Iterative development 

• Less project failure, better productivity, and lower defect rates 
• Early rather than late mitigation of high risks 
• Early visible progress 
• Early feedback, user engagement, and adaptation 
• Managed complexity: the team is not overwhelmed by “analysis paralysis” or very long and complex steps 
• The learning within an iteration can be methodically used to improve the development process itself, iteration by iteration. 

The Unified Process is a popular iterative software development process. 

Why a new methodology?

Process-oriented methods:

• Requirements of a project are completely frozen before the design and development process commences.
• Not always feasible
• Need for flexible, adaptable and agile methods, which allow the developers to make late changes in specifications.

Waterfall (Sequential) Lifecycle

• Promotes big up-front “speculative” requirements and design steps before programming.
• Historically promoted due to belief or hearsay rather than statistically significant evidence.
• Success/failure studies show that the waterfall has high failure rates.

Why the waterfall lifecycle fails?

The key false assumption:

• The specifications are predictable and stable and can be correctly defined at the start, with low change rates.
• But change is a constant on software projects.
• A typical software project experienced a 25% change.

  

Add dynamic rows in html table with Add, Edit, Delete feature in PHP and MySQLi

dynamic-table.php

<!DOCTYPE html>

<html lang="en-US">

<head>

<title> Dynamic Table in PhP</title>

<meta charset="utf-8">

<meta http-equiv="X-UA-Compatible" content="IE=edge">

<link href="https://fonts.googleapis.com/css?family=Lato:400,700,300|Open+Sans:400,300,600,700" rel="stylesheet" />

<link rel="stylesheet" href="//code.jquery.com/ui/1.13.1/themes/base/jquery-ui.css">

<link href="../css/bootstrap.min.css?ver=3.3.5" rel="stylesheet" />

<link href="../css/font-awesome.min.css?ver=4.6.3" rel="stylesheet" />

<link rel="stylesheet" href="css/dataTables.bootstrap.min.css" />

</head>

<body>

<script src="../js/jquery-3.5.1.min.js"></script>

<script src="../js/jquery.dataTables.min.js"></script>

<script src="../js/bootstrap.min.js?ver=3.3.5"></script>

<script src="../js/jquery-ui.js"></script>

<script>

      $(document).ready(function(){

       $("#fromdate").datepicker({

                changeMonth: true,

                changeYear: true,

                dateFormat: 'dd/mm/yy'                          

             

            });

        $("#todate").datepicker({

                changeMonth: true,

                changeYear: true,

                dateFormat: 'dd/mm/yy'                          

             

            });

  var employeeData = $('#employeeList').DataTable({

"processing":true,

"serverSide":true,

"bFilter":false,

"bSort" : false ,

"bPaginate": false,

"bInfo" : false,

"order":[],

"ajax":{

url:"action.php",

type:"POST",

data:{action:'listEmployee'},

dataType:"json"

}

});

$('#addEmployee').click(function(){

$('#employeeModal').modal('show');

$('#employeeForm')[0].reset();

$('.modal-title').html("<i class='fa fa-plus'></i> Add Employment Details");

$('#action').val('addEmployee');

$('#save').val('Add');

});

$("#employeeList").on('click', '.update', function(){

var empId = $(this).attr("id");

var action = 'getEmployee';

$.ajax({

url:'action.php',

method:"POST",

data:{empId:empId, action:action},

dataType:"json",

success:function(data){

$('#employeeModal').modal('show');

$('#empId').val(data.id);

$('#post').val(data.post);

$('#company').val(data.company);

$('#type').val(data.type);

$('#fromdate').val(data.fromdate);

$('#todate').val(data.todate);

$('#scale').val(data.scale);

$('#gross').val(data.gross);

$('.modal-title').html("<i class='fa fa-plus'></i> Edit Employee");

$('#action').val('updateEmployee');

$('#save').val('Save');

}

})

});

$("#employeeModal").on('submit','#employeeForm', function(event){

event.preventDefault();

$('#save').attr('disabled','disabled');

var formData = $(this).serialize();

$.ajax({

url:"action.php",

method:"POST",

data:formData,

success:function(data){

$('#employeeForm')[0].reset();

$('#employeeModal').modal('hide');

$('#save').attr('disabled', false);

employeeData.ajax.reload();

}

})

});

$("#employeeList").on('click', '.delete', function(){

var empId = $(this).attr("id");

var action = "empDelete";

if(confirm("Are you sure you want to delete this employee?")) {

$.ajax({

url:"action.php",

method:"POST",

data:{empId:empId, action:action},

success:function(data) {

employeeData.ajax.reload();

}

})

} else {

return false;

}

});

});

</script>

<div id="content" class="site-content">

<div class="container">

<div class="row">

<div class="col-md-12 padding-bottom-20">

<div class="row row-margin">

<div class="col-md-12">

<div class="container">

<div id="payment" style="display: block;">

<div style="overflow-x: auto;">

<div class="col-lg-10 col-md-10 col-sm-9 col-xs-12">   

<div class="panel-heading">

<div class="row">

<div class="col-md-10">

<h3 class="panel-title"></h3>

</div>

<div class="col-md-2" align="right">

<button type="button" name="add" id="addEmployee" class="btn btn-success btn-xs" style="font-size: 15px;padding: 10%;">Add New Row</button>

</div>

</div>

</div>

<table id="employeeList" class="table table-bordered table-striped" style="width:100%;">

        <thead>

<tr>

<th>Post held</th>

<th>Department/ Institute/ Company</th>

<th>Permanent/ Temporary/ Contract</th>

<th>From Date</th>

<th>To Date</th>

<th>Scale of pay</th>

<th>Gross Amount</th>

<th></th>

<th></th>

</tr>

</table>

</div>

<div id="employeeModal" class="modal fade">

    <div class="modal-dialog">

    <form method="post" id="employeeForm">

    <div class="modal-content">

    <div class="modal-header">

    <button type="button" class="close" data-dismiss="modal">&times;</button>

<h4 class="modal-title"><i class="fa fa-plus"></i> Edit Employment Details</h4>

    </div>

    <div class="modal-body">

<div class="form-group"

<label for="post" class="control-label">Post held</label>

<input type="text" class="form-control" id="post" name="post" placeholder="Post held" required>

</div>

<div class="form-group">

<label for="company" class="control-label">Department/ Institute/ Company</label>

<input type="text" class="form-control" id="company" name="company" placeholder="Department/ Institute/ Company">

</div>    

<div class="form-group">

<label for="type" class="control-label">Permanent/ Temporary/ Contract</label>

<input type="text" class="form-control"  id="type" name="type" placeholder="Permanent/ Temporary/ Contract" required="required">

</div>  

<div class="form-group">

<label for="fromdate" class="control-label">From Date [dd/mm/yyyy]</label>

<input type="text" class="form-control"  id="fromdate" name="fromdate" placeholder="From Date" autocomplete="off" readonly="readonly" required="required" ></textarea>

</div>

<div class="form-group">

<label for="todate" class="control-label">To Date [dd/mm/yyyy]</label>

<input type="text" class="form-control" id="todate" name="todate" placeholder="To Date" autocomplete="off" readonly="readonly" required="required" >

</div>

<div class="form-group">

<label for="scale" class="control-label">Scale of pay</label>

<input type="text" class="form-control" id="scale" name="scale" placeholder="Scale of pay" required="required" >

</div>

<div class="form-group">

<label for="gross" class="control-label">Gross Amount</label>

<input type="text" class="form-control" id="gross" name="gross" placeholder="Gross Amount" required="required" >

</div>

    </div>

    <div class="modal-footer">

    <input type="hidden" name="empId" id="empId" />

    <input type="hidden" name="userid" id="userid" value="12"/>   

    <input type="hidden" name="action" id="action" value="" />

    <input type="submit" name="save" id="save" class="btn btn-info" value="Save" />

    <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>

    </div>

    </div>

    </form>

    </div>

    </div>

</div>

</div>

</div>

</div>

</div>

</div>

</div>

<!-- .col-md-9 --></div>

<!-- .row --></div>

<!-- .container --></div>

action.php

<?php

include('Employee.php');

$emp = new Employee();

if(!empty($_POST['action']) && $_POST['action'] == 'listEmployee') {

$emp->employeeList();

}

if(!empty($_POST['action']) && $_POST['action'] == 'addEmployee') {

$emp->addEmployee();

}

if(!empty($_POST['action']) && $_POST['action'] == 'getEmployee') {

$emp->getEmployee();

}

if(!empty($_POST['action']) && $_POST['action'] == 'updateEmployee') {

$emp->updateEmployee();

}

if(!empty($_POST['action']) && $_POST['action'] == 'empDelete') {

$emp->deleteEmployee();

}

?>

Config.php

<?php
class dbConfig {
    protected $serverName;
    protected $userName;
    protected $password;
    protected $dbName;
    function dbConfig() {
        $this -> serverName = 'localhost';
        $this -> userName = 'root';
        $this -> password = "";
        $this -> dbName = "employee";
    }
}
?>

Employee.php

<?php require('config.php'); class Employee extends Dbconfig { protected $hostName; protected $userName; protected $password; protected $dbName; private $empTable = 'employment'; private $dbConnect = false; public function __construct(){ if(!$this->dbConnect){ $database = new dbConfig(); $this -> hostName = $database -> serverName; $this -> userName = $database -> userName; $this -> password = $database ->password; $this -> dbName = $database -> dbName; $conn = new mysqli($this->hostName, $this->userName, $this->password, $this->dbName); if($conn->connect_error){ die("Error failed to connect to MySQL: " . $conn->connect_error); } else{ $this->dbConnect = $conn; } } } public function employeeList(){ $sqlQuery = "SELECT * FROM ".$this->empTable." "; $result = mysqli_query($this->dbConnect, $sqlQuery); $employeeData = array(); while( $employee = mysqli_fetch_assoc($result) ) { $empRows = array(); $empRows[] = $employee['post']; $empRows[] = $employee['company']; $empRows[] = $employee['type']; $empRows[] = $employee['fromdate']; $empRows[] = $employee['todate']; $empRows[] = $employee['scale']; $empRows[] = $employee['gross']; $empRows[] = '<button type="button" name="update" id="'.$employee["id"].'" class="btn btn-warning btn-xs update">Update</button>'; $empRows[] = '<button type="button" name="delete" id="'.$employee["id"].'" class="btn btn-danger btn-xs delete" >Delete</button>'; $employeeData[] = $empRows; } $output = array( "draw" => intval($_POST["draw"]), "data" => $employeeData ); echo json_encode($output); } public function getEmployee(){ if($_POST["empId"]) { $sqlQuery = " SELECT * FROM ".$this->empTable." WHERE id = '".$_POST["empId"]."'"; $result = mysqli_query($this->dbConnect, $sqlQuery); $row = mysqli_fetch_array($result, MYSQLI_ASSOC); echo json_encode($row); } } public function updateEmployee(){ if($_POST['empId']) { $updateQuery = "UPDATE ".$this->empTable." SET userid = '".$_POST["userid"]."', post = '".$_POST["post"]."', company = '".$_POST["company"]."', type = '".$_POST["type"]."', fromdate = '".$_POST["fromdate"]."' , todate = '".$_POST["todate"]."' , scale = '".$_POST["scale"]."', gross = '".$_POST["gross"]."' WHERE id ='".$_POST["empId"]."'"; $isUpdated = mysqli_query($this->dbConnect, $updateQuery); } } public function addEmployee(){ $insertQuery = "INSERT INTO ".$this->empTable." (userid, post, company, type, fromdate, todate,scale, gross) VALUES ('".$_POST["userid"]."','".$_POST["post"]."', '".$_POST["company"]."', '".$_POST["type"]."', '".$_POST["fromdate"]."', '".$_POST["todate"]."', '".$_POST["scale"]."', '".$_POST["gross"]."')"; $isUpdated = mysqli_query($this->dbConnect, $insertQuery); } public function deleteEmployee(){ if($_POST["empId"]) { $sqlDelete = " DELETE FROM ".$this->empTable." WHERE id = '".$_POST["empId"]."'"; mysqli_query($this->dbConnect, $sqlDelete); } } } ?>

employee.sql

CREATE TABLE `employment` (

  `id` int(10) NOT NULL,

  `userid` int(10) DEFAULT NULL,

  `post` varchar(100) DEFAULT NULL,

  `company` varchar(100) DEFAULT NULL,

  `type` varchar(25) DEFAULT NULL,

  `fromdate` varchar(25) DEFAULT NULL,

  `todate` varchar(25) DEFAULT NULL,

  `scale` varchar(50) DEFAULT NULL,

  `gross` varchar(50) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Unified Process (UP) Best Practices

• Get high risk and high value requirements first
• Constant user feedback and engagement
• Early cohesive core architecture
• Test early, often, and realistically
• Apply use cases where needed
• Do some visual modeling with UML
• Manage requirements and scope creep
• Manage change requests and configuration

How to persist the selected value of the select box after form submit?

 <?php
if ($_POST['submit']) {
    if ($_POST['srf'] != "") {
        $srf = $_POST['srf'];
    } else {
        $srfErr = "This field is required.";
    }
}
?>
<form method="post">
<select name="srf" id="srf" class="form-control">
<option value="">Select</option>
<option <?php if (isset($srf) && $srf=="1") echo "selected";?>>1</option>
<option <?php if (isset($srf) && $srf=="2") echo "selected";?>>2</option>
<option <?php if (isset($srf) && $srf=="3") echo "selected";?>>3</option>
</select> <?php if($srfErr!= ""){ ?>
        <p><b><?php echo $srfErr;  ?></b></p>
</div>
    <?php }?>
<input type="submit" name="submit" value="submit" />
</form>

Unified Process Phases

Inception

• Inception is not a requirements phase; rather a feasibility phase, where just enough investigation is done to support a decision to continue or stop. –
• The life-cycle objectives of the project are stated, so that the needs of every stakeholder are considered. Scope and boundary conditions, acceptance criteria and some requirements are established.
• Approximate vision, business case, scope, vague estimates.

Inception - Activities

•  Formulate the scope of the project: Needs of every stakeholder, scope, boundary conditions and acceptance criteria established.
•  Plan and prepare the business case: Define risk mitigation strategy, develop an initial project plan and identify known cost, schedule, and profitability trade-offs.
• Synthesize candidate architecture: Candidate architecture is picked from various potential architectures
• Prepare the project environment

Inception - Exit criteria

• An initial business case containing at least a clear formulation of the product vision - the core requirements - in terms of functionality, scope, performance, capacity, technology base.
• Success criteria (example: revenue projection).
• An initial risk assessment.
• An estimate of the resources required to complete the elaboration phase.

Elaboration

• An analysis is done to determine the risks, stability of vision of what the product is to become, stability of architecture and expenditure of resources. 
• Refined vision, iterative implementation of core architecture, resolution of high risks, identification of most requirements and scope, more realistic estimates

Elaboration - Entry criteria

• The products and artifacts described in the exit criteria of the previous phase. 
• The plan approved by the project management, and funding authority, and the resources required for the elaboration phase have been allocated

Elaboration - Activities

• Define the architecture: Project plan is defined. The process, infrastructure and development environment are described. 
• Validate the architecture.  
• Baseline the architecture: To provide a stable basis for the bulk of the design and implementation effort in the construction phase.

Elaboration - Exit criteria 

• A detailed software development plan, with an updated risk assessment, a management plan, a staffing plan, a phase plan showing the number and contents of the iteration , an iteration plan, and a test plan
• The development environment and other tools 
• A baseline vision, in the form of a set of evaluation criteria for the final product.
• A domain analysis model, sufficient to be able to call the corresponding architecture ‘complete’. 
• An executable architecture baseline. 

Construction 

• The Construction phase is a manufacturing process. It emphasizes managing resources and controlling operations to optimize costs, schedules and quality. This phase is broken into several iterations. 
•  Iterative implementation of the remaining lower risk and easier elements, and preparation for deployment. 

Construction - Entry criteria 

• The product and artifacts of the previous iteration. The iteration plan must state the iteration specific goals
• Risks being mitigated during this iteration. 
• Defects being fixed during the iteration. 

Construction - Activities 

• Develop and test components: Components required satisfying the use cases, scenarios, and other functionality for the iteration are built. Unit and integration tests are done on Components. 
• Manage resources and control process. 
• Assess the iteration: Satisfaction of the goal of iteration is determined.

Construction - Exit Criteria 

• The same products and artifacts, updated, plus
• A release description document, which captures the results of an iteration 
• Test cases and results of the tests conducted on the products
• An iteration plan, detailing the next iteration 
• Objective measurable evaluation criteria for assessing the results of the next iteration(s).  

Transition 

• The transition phase is the phase where the product is put in the hands of its end users. It involves issues of marketing, packaging, installing, configuring, supporting the user. community, making corrections, etc. 
• Beta tests, deployment. 

Transition - Entry criteria

• The product and artifacts of the previous iteration, and in particular a software product sufficiently mature to be put into the hands of its users.

Transition - Activities  

• Test the product deliverable in a customer environment. 
• Fine tune the product based upon customer feedback 
• Deliver the final product to the end user 
• Finalize end-user support material.

Transition - Exit criteria 

• An update of some of the previous documents, as necessary, the plan being replaced by a “post-mortem” analysis of the performance of the project relative to its original and revised success criteria; 
• A brief inventory of the organization’s new assets as a result this cycle.