Alter table after X minutes, even if user closed window, is this a good way?

Question

I'm trying to make a mission system where users can accept a mission or not, and if it hasn't been accepted after X minutes it will be inactivated. Is this a good way to do it, would it be able to handle 10K missions per day?

<?php
    $mission_id = htmlspecialchars($_POST["mission_id"]));
    $user = $_SESSION["user"];

    // Verify that user is same as mission agent

    $verify = $conn->prepare("SELECT agent FROM missions WHERE id = ? AND active = 0 ORDER BY id limit 1");
    $verify->bindParam(1, $user);
    $verify->execute();
    $verify = $verify->fetch(PDO::FETCH_ASSOC);

    if($verify["agent"] == $user)   {
        unset($verify);
        // Do time code.
        ignore_user_abort(true);
        set_time_limit(300);
        $time = 0;
        while(time < 300)   {
            sleep(15);
            time += 15;
            // check if mission was accepted

            $verify = $conn->prepare("SELECT accepted FROM missions WHERE id = ? ORDER BY id LIMIT 1");
            $verify->bindParam(1, $mission_id);
            $verify->execute();
            $verify = $verify->fetch(PDO::FETCH_ASSOC);
            if($verify["accepted"] == 0)    { // not accepted
                unset($verify);
                // Inactivate mission
                $inactivate = $conn->prepare("UPDATE missions SET active = 0 WHERE id = ?");
                $inactivate->bindParam(1, $id);
                $inactivate->execute();
                unset($inactivate);
            }
            else {
                break;
            }
        }
    }
    else    {
        header("location: logout.php");
        // Log user out
    }
?>

I think Cron is the way to go here. https://duckduckgo.com/?q=cron+jobs - Good luck! — MyUser, Dec 28 '15 at 00:43

score 1 · Answer 1 · edited May 23 '17 at 10:28

1

Use mysql Create Event functionality that is perfect for these types of situations. Think of them as scheduled stored procedures (as complicated as you want) to fire often in very flexible re-occurring fashion.

This functionality was put in to do away with cron especially when database-only operations are to occur.

A high-level view of it can be seen here in an Answer I wrote up.

edited May 23 '17 at 10:28

Community

1
1

answered Dec 28 '15 at 01:05

Drew

24,851
10
43
78

1

Very interesting, will definitely try this! – Alpha Kitten Dec 28 '15 at 01:20
I think I might've done something wrong, it's not working – Alpha Kitten Dec 28 '15 at 01:29
show me all that you've done. Right now I know nothing as you've said nothing – Drew Dec 28 '15 at 01:32
CREATE EVENT `checkup_missions` ON SCHEDULE EVERY 10 SECOND STARTS '2015-12-28 02:24:44' ON COMPLETION PRESERVE DO BEGIN UPDATE missions SET active = 0 WHERE TIMESTAMPDIFF(MINUTE, created, now())>5; END Sorry had to figure out how to make it indent edit: I can't make it indent because mysql uses backticks.. :$ – Alpha Kitten Dec 28 '15 at 01:32
read my other link that shows you how to turn on events etc. That means play with it for half an hour – Drew Dec 28 '15 at 01:33
Ah yes I read it at first but forgot about the whole you need to start your events thingy, so let's just forget about this and pretend it never happened. – Alpha Kitten Dec 28 '15 at 01:51
Your solution works well on localhost, how well does it handle <10K table rows per day – Alpha Kitten Dec 28 '15 at 01:51
I think if properly indexed you could handle anything that Alpha wants to do – Drew Dec 28 '15 at 02:02

score 0 · Accepted Answer · answered Dec 28 '15 at 01:12

0

I would use an expire date timestamp and set it To 17 minutes in the future when u create the mission

If they don't act upon it. It will automatically expire If they do.. Update the expire record to 9999-12-31

And not use active/inactive. Just use current time to get all active missions.

Thrn no event needed. No crontab. No extra code

answered Dec 28 '15 at 01:12

DaveTheRave

463
2
5

Instead of having $time and increasing by 5 after every sleep(5);? – Alpha Kitten Dec 28 '15 at 01:20
It's an autoexpire concept. You control active by timestamp and u set it to expire based by setting this to a future daytime If user does nothing. It will be inactive once "now" is 17 minutes in future.. – DaveTheRave Dec 28 '15 at 01:33
I see! That will work on my online host too since they disabled events.. Thanks buddy – Alpha Kitten Dec 28 '15 at 02:21
Sorry for poor typing. I was on iphone killing time and saw this. You can have a status flag to control state of mission. I see two columns, "status" pending(0) or approved(1), and "expireson". Create the request, set expireson to 17 minutes in the future.. If the user accepts the mission, you update status to approved(1) and expireson '9999-12-31'. I have used this to streamline anything that has a future expire date. – DaveTheRave Dec 28 '15 at 03:11

Alter table after X minutes, even if user closed window, is this a good way?

2 Answers2