Krdytkyu

I'm working on an app that will be used for employee 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'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. I was also 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.

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>

In your INSERT script:

• Use your config.php file everywhere instead of hardcoding your connection credentials.


• You should be checking that the POST elements actually exist before trying to access their values as a matter of best practice. I'd probably use a null coalescing operator or perhaps one giant isset() conditional (isset() can handle multiple arguments).


• If this was my script, I'd probably incorporate stronger validation checks on each incoming value so that the database is kept clean and meaningful. Rather than tell the user when something is missing, ratchet up the value requirements and inform the user that an expected value didn't have the expected format and describe in detail what is expected (dob format, email, bloodtype, gender etc).


• I'll recommend object-oriented mysqli syntax because it is more concise and in my opinion easier to read and maintain.


• Using a prepared statement will avoid all of that bloat with value escaping.


• You must never provide the actual mysql error when your application is public.

In your UPDATE script:

• I don't know that I like the chained declaration of default empty strings for so many values -- it has a negative impact on readability and maintainability for a slight (unnecessary) benefit in script length.


• You should be checking that the POST elements actually exist before trying to access/trim() their values as a matter of best practice. I'd probably write the trim() call in the else portion of the condition block.


• 
  isset($_POST["id"]) && !empty($_POST["id"]) is a redundant check, just remove the isset() condition because !empty() will accomplish the same thing.


• Rather than using lots of similar yet separate _err variables, just create an $error array and if there are any invalid values passed, just push them into the array. When deciding to proceed with the update query, just check the size of the error array. If !sizeof($errors), then perform the update, else display all of the invalid values.


• Rather than trimming $id, just cast it as an integer with (int).


• Jamming a value attribute like value="<?php echo $workposition; ?>" is not going to work in your <select> fields.

As a matter of personal preference, I tend to write all my negative/failure/error outcomes before my successful outcomes in my condition blocks. By writing the SELECT statement last, you can move directly into your html form portion which should make things easier to associate and debug.

Try to avoid single-use variable declarations. If they improve the readability of your code, that can be a sound justification. However, generally your code will be easier to maintain if you have fewer variables in your global scope.

Finally, because you are processing multibyte characters, be sure to do UTF-8 All The Way Through.