i'm working on an app that will be used for Employees Management and i'm using mysql for the database.
You can add employees, view/update/delete them, and on the dashboard i'm listing them in different tables depending on what it's needed.
I was wondering if my insert and update code can be improved somehow. Because my update code i think it looks hard to maintain/edit. And was wondering if there is a more simple way to do it. It's huge and i'm not even at the middle of adding all of the fields i need to edit/update info for.
Thank you very much.
This is my insert code ( for adding new employees to my db ):
<?php
$server = "localhost";
$user = "root";
$pass = "";
$dbname = "employees";
// Create connection
$conn = mysqli_connect($server, $user, $pass, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$fname = mysqli_real_escape_string($conn, $_POST['fname']);
$lname = mysqli_real_escape_string($conn, $_POST['lname']);
$dob = mysqli_real_escape_string($conn, $_POST['dob']);
$embg = mysqli_real_escape_string($conn, $_POST['embg']);
$address = mysqli_real_escape_string($conn, $_POST['address']);
$city = mysqli_real_escape_string($conn, $_POST['city']);
$mobile = mysqli_real_escape_string($conn, $_POST['mobile']);
$email = mysqli_real_escape_string($conn, $_POST['email']);
$workplace = mysqli_real_escape_string($conn, $_POST['workplace']);
$workposition = mysqli_real_escape_string($conn, $_POST['workposition']);
$jobstartdate = mysqli_real_escape_string($conn, $_POST['jobstartdate']);
$contractfrom = mysqli_real_escape_string($conn, $_POST['contractfrom']);
$contractto = mysqli_real_escape_string($conn, $_POST['contractto']);
$healthbookfrom = mysqli_real_escape_string($conn, $_POST['healthbookfrom']);
$healthbookto = mysqli_real_escape_string($conn, $_POST['healthbookto']);
$bankaccount = mysqli_real_escape_string($conn, $_POST['bankaccount']);
$bank = mysqli_real_escape_string($conn, $_POST['bank']);
$workcode = mysqli_real_escape_string($conn, $_POST['workcode']);
$gender = mysqli_real_escape_string($conn, $_POST['gender']);
$bloodtype = mysqli_real_escape_string($conn, $_POST['bloodtype']);
$notes = mysqli_real_escape_string($conn, $_POST['notes']);
$contract_file = basename($_FILES['contractupload']['name']);
$contract_path = "files/contracts/$contract_file";
$contract_file = mysqli_real_escape_string($conn, $contract_file);
copy($_FILES['contractupload']['tmp_name'], $contract_path); // copy the file to the folder
$sql = "INSERT INTO addemployees (fname, lname, dob, embg, address, city, mobile, email, workplace, workposition, jobstartdate, contractfrom, contractto, healthbookfrom,
healthbookto, contractupload, bankaccount, bank, workcode, gender, bloodtype, notes)
VALUES ('$fname', '$lname', '$dob', '$embg', '$address', '$city', '$mobile', '$email', '$workplace', '$workposition', '$jobstartdate', '$contractfrom', '$contractto',
'$healthbookfrom', '$healthbookto', '$contract_file', '$bankaccount', '$bank', '$workcode', '$gender', '$bloodtype', '$notes')";
if (mysqli_query($conn, $sql)) {
header("location: employees.php");
// echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
//Close the connection
mysqli_close($conn);
?>
And this is my update user info code:
<?php
// Include config file
require_once "config.php";
// Define variables and initialize with empty values
$fname = $lname = $dob = $embg = $address = $city = $mobile = $email = $workplace =
$workposition = $jobstartdate = $contractfrom = "";
$fname_err = $lname_err = $dob_err = $embg_err = $address_err = $city_err = $mobile_err =
$email_err = $workplace_err = $workposition_err = $jobstartdate_err = $contractfrom_err = "";
// Processing form data when form is submitted
if(isset($_POST["id"]) && !empty($_POST["id"])){
// Get hidden input value
$id = $_POST["id"];
// Validate First Name ($fname)
$input_fname = trim($_POST["fname"]);
if(empty($input_fname)){
$lname_err = "Please enter your First Name.";
} else{
$fname = $input_fname;
}
// Validate Last Name ($lname)
$input_lname = trim($_POST["lname"]);
if(empty($input_lname)){
$lname_err = "Please enter your Last Name.";
} else{
$lname = $input_lname;
}
// Validate Date of Birth ($dob)
$input_dob = trim($_POST["dob"]);
if(empty($input_dob)){
$dob_err = "Please enter your Date of Birth.";
} else{
$dob = $input_dob;
}
// Validate EMBG ($embg)
$input_embg = trim($_POST["embg"]);
if(empty($input_embg)){
$embg_err = "Please enter your EMBG.";
} else{
$embg = $input_embg;
}
// Validate Address ($address)
$input_address = trim($_POST["address"]);
if(empty($input_address)){
$address_err = "Please enter an address.";
} else{
$address = $input_address;
}
// Validate City ($city)
$input_city = trim($_POST["city"]);
if(empty($input_city)){
$city_err = "Please enter your City.";
} else{
$city = $input_city;
}
// Validate Mobile Number ($mobile)
$input_mobile = trim($_POST["mobile"]);
if(empty($input_mobile)){
$mobile_err = "Please enter your Mobile.";
} else{
$mobile = $input_mobile;
}
// Validate E-mail ($email)
$input_email = trim($_POST["email"]);
if(empty($input_email)){
$email_err = "Please enter your E-mail.";
} else{
$email = $input_email;
}
// Validate WorkPlace ($workplace)
$input_workplace = trim($_POST["workplace"]);
if(empty($input_workplace)){
$workplace_err = "Please choose your Work Place.";
} else{
$workplace = $input_workplace;
}
// Validate Work Position ($workposition)
$input_workposition = trim($_POST["workposition"]);
if(empty($input_workposition)){
$workposition_err = "Please choose your Work Position.";
} else{
$workposition = $input_workposition;
}
// Validate Job Start Date ($jobstartdate)
$input_jobstartdate = trim($_POST["jobstartdate"]);
if(empty($input_jobstartdate)){
$jobstartdate_err = "Please enter your Date of Birth.";
} else{
$jobstartdate = $input_jobstartdate;
}
// Validate Contract From ($contractfrom)
$input_contractfrom = trim($_POST["contractfrom"]);
if(empty($input_contractfrom)){
$contractfrom_err = "Please enter your Date of Birth.";
} else{
$contractfrom = $input_contractfrom;
}
// Check input errors before inserting in database jobstartdate
if(empty($fname_err) && empty($lname_err) && empty($dob_err) && empty($embg_err) && empty($address_err) && empty($city_err) && empty($mobile_err) &&
empty($email_err) && empty($workplace_err) && empty($workposition_err) && empty($jobstartdate_err) && empty($contractfrom_err)){
// Prepare an update statement
$sql = "UPDATE addemployees SET fname=?, lname=?, dob=?, embg=?, address=?, city=?, mobile=?, email=?, workplace=?,
workposition=?, jobstartdate=?, contractfrom=? WHERE id=?";
if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bind_param("ssssssssssssi", $param_fname, $param_lname, $param_dob, $param_embg, $param_address, $param_city, $param_mobile, $param_email,
$param_workplace, $param_workposition, $param_jobstartdate, $param_contractfrom, $param_id);
// Set parameters
$param_id = $id;
$param_fname = $fname;
$param_lname = $lname;
$param_dob = $dob;
$param_embg = $embg;
$param_address = $address;
$param_city = $city;
$param_mobile = $mobile;
$param_email = $email;
$param_workplace = $workplace;
$param_workposition = $workposition;
$param_jobstartdate = $jobstartdate;
$param_contractfrom = $contractfrom;
// Attempt to execute the prepared statement
if($stmt->execute()){
// Records updated successfully. Redirect to landing page
header("location: employees.php");
exit();
} else{
echo "Something went wrong. Please try again later.";
}
}
// Close statement
$stmt->close();
}
// Close connection
$mysqli->close();
} else{
// Check existence of id parameter before processing further
if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){
// Get URL parameter
$id = trim($_GET["id"]);
// Prepare a select statement
$sql = "SELECT * FROM addemployees WHERE id = ?";
if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bind_param("i", $param_id);
// Set parameters
$param_id = $id;
// Attempt to execute the prepared statement
if($stmt->execute()){
$result = $stmt->get_result();
if($result->num_rows == 1){
/* Fetch result row as an associative array. Since the result set contains only one row, we don't need to use while loop */
$row = $result->fetch_array(MYSQLI_ASSOC);
// Retrieve individual field value
$fname = $row["fname"];
$lname = $row["lname"];
$dob = $row["dob"];
$embg = $row["embg"];
$address = $row["address"];
$city = $row["city"];
$mobile = $row["mobile"];
$email = $row["email"];
$workplace = $row["workplace"];
$workposition = $row["workposition"];
$jobstartdate = $row["jobstartdate"];
$contractfrom = $row["contractfrom"];
} else{
// URL doesn't contain valid id. Redirect to error page
header("location: error.php");
exit();
}
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
$stmt->close();
// Close connection
$mysqli->close();
} else{
// URL doesn't contain id parameter. Redirect to error page
header("location: error.php");
exit();
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Update Record</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
<style type="text/css">
.wrapper{
width: 500px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div class="page-header">
<h2>Измени Податоци</h2>
</div>
<form action="<?php echo htmlspecialchars(basename($_SERVER['REQUEST_URI'])); ?>" method="post">
<div class="form-group <?php echo (!empty($fname_err)) ? 'has-error' : ''; ?>">
<label>Име</label>
<input type="text" id="fname" name="fname" class="form-control" value="<?php echo $fname; ?>">
<span class="help-block"><?php echo $fname_err;?></span>
</div>
<div class="form-group <?php echo (!empty($lname_err)) ? 'has-error' : ''; ?>">
<label>Презиме</label>
<input type="text" name="lname" id="lname" class="form-control" value="<?php echo $lname; ?>">
<span class="help-block"><?php echo $lname_err;?></span>
</div>
<div class="form-group <?php echo (!empty($dob_err)) ? 'has-error' : ''; ?>">
<label>Дата на Раѓање</label>
<input type="date" name="dob" id="dob" class="form-control" value="<?php echo $dob; ?>">
<span class="help-block"><?php echo $dob_err;?></span>
</div>
<div class="form-group <?php echo (!empty($embg_err)) ? 'has-error' : ''; ?>">
<label>ЕМБГ</label>
<input type="text" name="embg" id="embg" class="form-control" maxlength="13" value="<?php echo $embg; ?>">
<span class="help-block"><?php echo $embg_err;?></span>
</div>
<div class="form-group <?php echo (!empty($address_err)) ? 'has-error' : ''; ?>">
<label>Адреса</label>
<input type="text" id="address" name="address" class="form-control" value="<?php echo $address; ?>">
<span class="help-block"><?php echo $address_err;?></span>
</div>
<div class="form-group <?php echo (!empty($city_err)) ? 'has-error' : ''; ?>">
<label>Град</label>
<input type="text" name="city" id="city" class="form-control" value="<?php echo $city; ?>">
<span class="help-block"><?php echo $city_err;?></span>
</div>
<div class="form-group <?php echo (!empty($mobile_err)) ? 'has-error' : ''; ?>">
<label>Мобилен</label>
<input type="text" name="mobile" id="mobile" class="form-control" maxlength="9" value="<?php echo $mobile; ?>">
<span class="help-block"><?php echo $mobile_err;?></span>
</div>
<div class="form-group <?php echo (!empty($email_err)) ? 'has-error' : ''; ?>">
<label>Е-маил</label>
<input type="text" name="email" id="email" class="form-control" value="<?php echo $email; ?>">
<span class="help-block"><?php echo $email_err;?></span>
</div>
<div class="form-group <?php echo (!empty($workplace_err)) ? 'has-error' : ''; ?>">
<label>Работно Место <span style="font-size: 15px; color: rgb(255, 0, 0); margin-right: 15px;">(ПРОВЕРИ)</span></label>
<select type="text" name="workplace" id="workplace" class="form-control" value="<?php echo $workplace; ?>">
<option value="Кафич ГТ-1 - Широк Сокак бр. 55">Кафич ГТ-1 - Широк Сокак бр. 55</option>
<option value="Кафич ГТ-2 - Широк Сокак бр. 94">Кафич ГТ-2 - Широк Сокак бр. 94</option>
<option value="Ланч Бар ГТ - Широк Сокак бр. 55">Ланч Бар ГТ - Широк Сокак бр. 55</option>
<option value="Главен Магацин - Боримечка">Главен Магацин - Боримечка</option>
</select>
<span class="help-block"><?php echo $workplace_err;?></span>
</div>
<div class="form-group <?php echo (!empty($workposition_err)) ? 'has-error' : ''; ?>">
<label>Работна Позиција <span style="font-size: 15px; color: rgb(255, 0, 0); margin-right: 15px;">(ПРОВЕРИ)</span></label>
<select type="text" name="workposition" id="workposition" class="form-control" value="<?php echo $workposition; ?>">
<option value="Келнер">Келнер</option>
<option value="Шанкер">Шанкер</option>
<option value="Колачи">Колачи</option>
<option value="Сладолед">Сладолед</option>
<option value="Производство Сладолед">Производство Сладолед</option>
<option value="Производство Торти">Производство Торти</option>
<option value="Кувар">Кувар</option>
<option value="Помошник Кувар">Помошник Кувар</option>
<option value="Салатер">Салатер</option>
<option value="Пицер">Пицер</option>
<option value="Менаџер">Менаџер</option>
<option value="Книговодител">Книговодител</option>
<option value="Хигиеничар">Хигиеничар</option>
<option value="Стражар">Стражар</option>
<option value="Магационер">Магационер</option>
<option value="Шофер">Шофер</option>
<option value="Дистрибутер">Дистрибутер</option>
</select>
<span class="help-block"><?php echo $workposition_err;?></span>
</div>
<div class="form-group <?php echo (!empty($jobstartdate_err)) ? 'has-error' : ''; ?>">
<label>Дата на Почнување на Работа <span style="font-size: 15px; color: rgb(255, 0, 0); margin-right: 15px;">(Месец/Ден/Година)</span></label>
<input type="date" name="jobstartdate" id="jobstartdate" class="form-control" value="<?php echo $jobstartdate; ?>">
<span class="help-block"><?php echo $jobstartdate_err;?></span>
</div>
<div class="form-group <?php echo (!empty($contractfrom_err)) ? 'has-error' : ''; ?>">
<label>Договор за работа од <span style="font-size: 15px; color: rgb(255, 0, 0); margin-right: 15px;">(Месец/Ден/Година)</span></label>
<input type="date" name="contractfrom" id="contractfrom" class="form-control" value="<?php echo $contractfrom; ?>">
<span class="help-block"><?php echo $contractfrom_err;?></span>
</div>
<input type="hidden" name="id" value="<?php echo $id; ?>"/>
<input type="submit" class="btn btn-primary" value="Submit">
<a href="employees.php" class="btn btn-default">Cancel</a>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
