SQL Select/ Insert / Update query PHP MySQL

Question

I have a table with columns userID(int),timeIN(date),timeOUT(date)

I am inserting a record in mysql database. First I check if the userID is correct from the other table and if its correct it will add a new record of the userID and timeIN(date) whereas the timeOUT will be NULL, else it will display error if the userID is not correct. I want my code to be able to check if the user is currently timeIN so it will prevent a double entry. I would also like to insert or update timeOUT(date) if the values of userID is equals to the user input and timeIN is not null and timeOUT is null.

Please kindly help...thanks.

Here is my code for inserting userID and timeIN: IT WORKS when inserting into mysql database.

<?php
if($_SERVER['REQUEST_METHOD']=='POST'){
require_once('dbConnect.php');

$userID = $_POST['userID'];
$sql = "SELECT * FROM employee WHERE userID='$userID'";
$result = mysqli_query($con,$sql);
$check = mysqli_fetch_array($result);

if(isset($check)){
    $sql = "INSERT INTO dtr (userID,timeIN) VALUES ('$userID', now())";
    mysqli_query($con, $sql);
    echo 'Time IN Successful!';
    }else{
        echo 'Invalid USER ID. Please try again!';
        }           
 mysqli_close($con);
}
?>

There is no need to check first. In fact, it's counter-productive. — Strawberry
– Strawberry, Commented Dec 20, 2016 at 13:08

Gordon Linoff · Accepted Answer · 2016-12-20 12:43:55Z

1

You should handle these checks inside the database. The current check you are doing in the database can be handled by a foreign key constraint:

alter table dtr add constraint fk_dtr_userId
    foreign key (userId) references employee(userId);

The second means that you want only one row with a NULl value. Ideally, this could be handled with a unique constraint:

alter table dtr add constraint unq_dtr_userId_timeOut
    unique (userId, timeOut);

Unfortunately (for this case), MySQL allows duplicate NULL values for unique constraints. So, you can do one of two things:

Use a default value, such as '2099-12-31' for time out.
Use a trigger to enforce uniqueness

In either case, the database itself will be validating the data, so you can be confident of data integrity regardless of how the data is inserted or updated.

answered Dec 20, 2016 at 12:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raymond Nijland Over a year ago

@topic starter you need to use database engine InnoDB for this to work if you are using MyISAM as table engine you can covert using this ALTER TABLE [your_table_name] ENGINE=InnoDB;

Ali Rasheed · Accepted Answer · 2016-12-20 12:50:49Z

0

I did it from my mobile not tested but you will get the idea of what is going on

if(isset($check))
{

$sql="SELECT * FROM dtr WHERE userID = $userID";
$result = mysqli_query($con,$sql);
$check = mysqli_fetch_array($result);

if(isset($check))
{
    echo "Already in";
    if(isset($check['timeIN']) && !isset($check['timeOUT']))
    {
        $sql = "UPDATE dtr SET timeOUT= now() WHERE userID=$userID";
        mysqli_query($con, $sql);
        mysqli_close($con);
    }
}
else
{
    $sql = "INSERT INTO dtr (userID,timeIN) VALUES ('$userID', now())";
    mysqli_query($con, $sql);
    mysqli_close($con);
    echo 'Time IN Successful!';
}
}
else
{
echo 'Invalid USER ID. Please try again!';
mysqli_close($con);
}

answered Dec 20, 2016 at 12:50

Ali Rasheed

2,8152 gold badges23 silver badges34 bronze badges

3 Comments

Sofia Benkirane Over a year ago

thanks...i modified the code. i separate the code for time in and time out. now it's working just how i want it :)

Ali Rasheed Over a year ago

Yea! You just needed a little go-ahead code I suppose. I would advise you to break your problems into chunks. I believe you got confused by thinking of a solution by looking all of the problems. This will make you much confused. Do it like this. For instance. First I would pick the date, then write down its code. Then I would check user, then write down its code. Then I would update the time, then right down its code. Well... glad I was of help. PS: I have been in these situations before... a lot of times :D

Ali Rasheed Over a year ago

PS2: Learn Prepared Statements. They are very effective.

Collectives™ on Stack Overflow

SQL Select/ Insert / Update query PHP MySQL

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related