PHP Versions Question posted in
You can find a list of all versions here.

Php versions – How to insert a new row into a database table with ajax

Currently, I have a grid of employees photos on a webpage.
Each employee has an IN and OUT photo. They are named firstname_here.jpg and firstname_away.jpg respectively.

When I click on a photo it toggles between the two.

This is just a simple in and out board we placed on a portrait touch screen at reception. When employees come in they click their photo to display the firstname_here.jpg image and when they leave touching the photo again changes to firstname_away.jpg.

I used this example
https://www.golangprograms.com/javascript-example-to-change-image-on-click.html

and produced this working version

<!DOCTYPE HTML>
<html>

<script>
function toggleImage(firstname) {
    var img1 = "http://localhost:8888/attendance/" + firstname + "_here.jpg";
    var img2 = "http://localhost:8888/attendance/" + firstname + "_away.jpg";
    var imgElement = document.getElementById(firstname);
    imgElement.src = (imgElement.src === img1)? img2 : img1;
    var checkstatus = imgElement.src;
    if (checkstatus.includes("away")) {
        var status = "check_out";
    } else {
        var status = "check_in";
    }
}

</script>
<img src="http://localhost:8888/attendance/david_away.jpg" id="david" onclick="toggleImage('david');"/>
<img src="http://localhost:8888/attendance/ada_away.jpg" id="ada" onclick="toggleImage('ada');"/>

</body>
</html>

Now, I would like to connect to a local mysql database so that when an employee clicks their image
it writes firstname, status, current date and time.

I used this post to setup my database
Design to represent employee check-in and check-out

My connection to the database works and I can send information manually using

$sql = "INSERT INTO Main (firstname, status)
        VALUES ('John', 'check_in')";

I did changed my fields for my purpose and the date and time are generated automatically.

HELP
I understand that PHP is server based and runs first and javascript is client side.
I’ve gone through so many stackoverflow pages and I’m having a very hard time figuring out how to write to the mysql database when someone clicks on an image.

I would like to write the firstname and status variables.

Tags:

2

Answers


  1. 0

    If you look at your code where it has

    if (checkstatus.includes("away")) {
    var status = "check_out";
        } else {
    var status = "check_in";
        }

    You can insert a call (ajax for example) to a URL on your server that has a PHP script which contains the MySQL query that you mentioned, since you clearly know at that point in your javascript whether the user is checking in or checking out.

    I would mention that your current solution for modifying status is a bit questionable. What happens for example if multiple people have the same first name? Should use an ID of some kind.

    Login or Signup to reply.
  2. 0

    First of all, I think I would adjust the directory structure of your images so that you can enjoy simpler programming and easier server maintenance.

    attendance
    ada
        here.jpg
        away.jpg
    david
        here.jpg
        away.jpg

    This makes all filenames identical/predictable in there respective subdirectories.

    When an employee leaves the company, you simply delete the employees directory to remove the image. Compare that to individually hunting for the files in the attendance directory.

    Ideally, you should be using numeric ids for each human so that you don’t encounter name collisions. Most professionally, you should have a db table of employees which are assigned an AUTOINCREMENTed id when they are first registered into your system, then that numeric id is used everywhere.

    Use a class toggling technique and move your image handling to css for cleaner html markup.

    <div id="david" class="tapInOut here"></div>
<div id="ada" class="tapInOut away"></div>

    Note that on pageload, you should be querying your db to see if respective employees are currently checked in or out for the current day.

    In the .css file:

    .tapInOut {
    width: 200px;
    height: 200px;
}
#ada.here {
    background-image: url("attendance/ada/here.jpg");
}
#ada.away {
    background-image: url("attendance/ada/away.jpg");
}

    Move the inline click event to an external .js file. It’s been a long time since I’ve written a plain js ajax call, but this untested block should get you pretty close.

    let togglables = document.getElementsByClassName("tapInOut"),
    checkInOut = function() {
        let http = new XMLHttpRequest(),
            params = 'firstname=' + this.id
                + '&status=' + (this.classList.contains('here') ? 'out' : 'in');
        http.open('POST', 'checkinout.php', true);
        http.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
        http.onreadystatechange = function() {
            if (http.readyState == 4 && http.status == 200) {
                if (http.responseText === 'Success') {
                    this.classList.toggle('here');
                    this.classList.toggle('away');
                }
                alert(http.responseText);
            }
        }
        http.send(params);
    };

for (let i = 0; i < togglables.length; ++i) {
    togglables[i].addEventListener('click', checkInOut, false);
}

    You might even like to dim, hide, disable, etc. the clickable image until the ajax responds so that the user knows that the program is processing the request.

    As for the receiving .php script…

    <?php
if (!isset($_POST['firstname'], $_POST['status']) || !ctype_alpha($_POST['firstname']) || !in_array($_POST['status'], ['here', 'away'])) {
    // you may want to validate the firstname against the db for best validation
    exit('Missing/Invalid Data Received');
}
$mysqli = new mysqli("localhost", "root", "", "myDB");
$query = "INSERT INTO Main (firstname, status) VALUES (?, ?)";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("ss", $_POST['firstname'], $_POST['status']);
if ($stmt->execute()) {
    exit('Insert Failed');
}
exit('Success');

    So why am I not passing thr current datetime in the query?

    The cleanest / most professional techniwue is to alter your Main (I really think this should be employee_status_logs) table to have a DEFAULT value of CURRENT_TIMESTAMP. This way, every time a new row is inserted and the datetime column value is not declared, the db will automatically write the current datetime into the row for you.

    Sorry for the long answer and suggested overhaul of your script, but there were many techniques to recommend. If I have made mistakes please leave me a comment so that I can refine my answer.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search