Insert data INTO MySQL trought php with expiration DATE

Question

So here is my problem, I want to add to my database table an expiration date to a specific row trough HTML and PHP but I dont know what is wrong. For some reason it dont delete the specific row when the time runs out.

And is possible to add something to the database for example, on the php introduce the time to expire in a column? I'm just starting with this PHP MySQLi interactions so the code will be probabily bad!

HTML:

<div class="modal fade" id="addModal" role="dialog">
   <div class="modal-dialog">
      <div class="modal-content">
         <div class="modal-header">
            <h4 class="modal-title">VIPS</h4>
         </div>
         <div class="modal-body">
            <div class="container">
               <form class="form-horizontal" action="insert.php" method="post" onsubmit="setTimeout('location.reload()', 10);">
                  <div class="form-group">
                     <label class="control-label col-sm-2" for="full name">SteamID</label>
                     <input class="form-control" type="text" id="identity" name="identity" placeholder="SteamID" ng-model="newUser.fullname"/>
                  </div>
                  <div class="form-group">
                     <label class="control-label col-sm-2" for="full name">Name</label>
                     <input class="form-control" type="text" placeholder="Name" id="Name" name="name" ng-model="newUser.fullname"/>
                  </div>
                  <div class="form-group">
                     <label class="control-label col-sm-2" for="full name">immunity</label>
                     <input class="form-control" type="text" id="immunity" name="immunity" placeholder="Imunidade" ng-model="newUser.fullname"/>
                  </div>
                  <div class="form-group">
                     <label class="control-label col-sm-2" for="full name">Time</label>
                     <select id="time" name="time" data-plugin-selectTwo class="form-control populate">
                        <optgroup label="Staff">
                           <option value="1">1 minute</option>
                           <option value="2">2 minute</option>
                        </optgroup>
                     </select>
                  <div class="form-group">
                     <label class="control-label col-sm-2" for="full name">Flags</label>
                     <select id="flags" name="flags" data-plugin-selectTwo class="form-control populate">
                        <optgroup label="Staff">
                           <option value="zo">Founder</option>
                           <option value="abcdefghjkp">Admin</option>
                           <option value="abcdfgjkq">Mod</option>
                        </optgroup>
                     </select>
                     <div class="modal-footer">
                        <button class="btn btn-primary" type="submit" value="ADICIONAR">Add</button>
                        <button class="btn btn-default" type="button" data-dismiss="modal">Close</button>
                     </div>
                  </div>
               </form>
            </div>

PHP:

<?php

$conn = new mysqli("", "", "", "");

if ($conn->connect_error) {
    die("Connection failed");
} 

// Escape user inputs for security
$identity = mysqli_real_escape_string($conn, $_REQUEST['identity']);
$name = mysqli_real_escape_string($conn, $_REQUEST['name']);
$flags = mysqli_real_escape_string($conn, $_REQUEST['flags']);
$immunity = mysqli_real_escape_string($conn, $_REQUEST['immunity']);
$time=$_POST['time'];

$sql = "SELECT COUNT(*) FROM sm_admins WHERE identity = ('$identity')";
if($count = $conn->query($sql)){

    if($count == 1){
        $sql = "INSERT INTO sm_admins 
                        (identity, name, flags, immunity, '', '', now(), 
                         DATE_ADD(NOW(), INTERVAL time='time' MINUTE)) 
                VALUES ('$identity', '$name', '$flags', '$immunity')";
        if($conn->query($sql)){
            echo "Good Connection";
        }
    }else{
        echo "Identity already exist";
    }
}
// close connection
header('Location: panel.php');
mysqli_close($conn);

?>

Please use error reporting. There are multiple issues with that query. — user3783243, Jul 22 '18 at 01:23
The thing is that everything works exept the expiration date — , Jul 22 '18 at 01:24
That doesent work, that is why I'm here, that is the only function and I said that in the post. — , Jul 22 '18 at 01:25
From `, '', '', now(), DATE_ADD(NOW(), INTERVAL time='time' MINUTE)` on is unclear what you want to do. The first `()` are for columns. The second list is for the values. — user3783243, Jul 22 '18 at 01:26
In the HTML I choose the expiration date of that row and I want just that, but I implmented that in the PHP but it doesent work. — , Jul 22 '18 at 01:27
Can you please remove the onsubmit part from the form in html ? — PHP Web, Jul 22 '18 at 02:31

score 1 · Answer 1 · 2018-07-23T03:48:28.423

Your current query is:

$sql = "INSERT INTO sm_admins 
                (identity, name, flags, immunity, '', '', now(), 
                 DATE_ADD(NOW(), INTERVAL time='time' MINUTE)) 
        VALUES ('$identity', '$name', '$flags', '$immunity')";

it needs to be changed so that the database knows what you want. Since I don't have all the fieldnames for you sm_admin table I'll use f1,f2, f3 etc so you'll need to replace to the appropriate fieldname

    $sql = "INSERT INTO sm_admins 
                    (identity, name, flags, immunity, f1, f2, f3, f4, f5 ) 
            VALUES ('$identity', '$name', '$flags', '$immunity', '', '', now(), 
                     DATE_ADD(NOW(), INTERVAL time='time' MINUTE))";

Edit:

Your original sql statement wouldn't work. The above correction will work but you'll need to change f1..f5 to your relevant fieldnames.

As for the automatic deletion of records you could use a cronjob or a mySQL event. Documentation can be found at https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html. Cronjob will depend upon your server. Here is a skeleton mysql event schedule - read documentation for details.

DELIMITER $$
-- SET GLOBAL event_scheduler = ON$$     -- required for event to execute but not create    

CREATE  /*[DEFINER = { user | CURRENT_USER }]*/ EVENT `_u3a_work`.`xx`

ON SCHEDULE
     /* uncomment the example below you want to use */

    -- scheduleexample 1: run once

       --  AT 'YYYY-MM-DD HH:MM.SS'/CURRENT_TIMESTAMP { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] }

    -- scheduleexample 2: run at intervals forever after creation

       -- EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]

    -- scheduleexample 3: specified start time, end time and interval for execution
       /*EVERY 1  [HOUR|MONTH|WEEK|DAY|MINUTE|...]

       STARTS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1[HOUR|MONTH|WEEK|DAY|MINUTE|...] }

       ENDS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] } */

/*[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']*/

DO
    BEGIN
        (sql_statements)
    END$$

DELIMITER ;

@Peter , what Jeff has proposed is correct but you are saying it doesn't work . What is your requirement actually ? Please be clear . — PHP Web, Jul 22 '18 at 02:26
My requirement is to I insert in the HTML code the time in minutes that I want to expire for example if 1 choose 1 the row will be deleted 1 minute after his creation, that is what I want, I dont have any column for the expiration time or something. — , Jul 22 '18 at 02:45
@Peter then insert the values how Jeff has written and after that set cron every minute which will check the time of creation of posts and the time set for delete , if the current time is 1 minute ahead of post time and deletion time is 1 then it will delete otherwise not . The same for 2 . — PHP Web, Jul 22 '18 at 03:27

Roshana Pitigala · Answer 2 · 2018-07-22T03:34:37.523

...delete the specific row when the time runs out.

You can't do this with MySQL. You'll have to come up with a solution using PHP. In this case cron jobs may come in handy.

How to create cron job using PHP?

A work around would be to add a column with DATETIME to your table. Set the default value to NOW(). So when you insert a record to the table inserted date and time will be saved in that column.

Then when you are retrieving data add a condition to the search query to filter information by expiration date and time

... WHERE `dateTime` > '2018-07-22 00:05:48'

Insert data INTO MySQL trought php with expiration DATE

2 Answers2