将数据插入到mysql trought php中并带有过期日期

vbkedwbf  于 2021-06-21  发布在  Mysql
关注(0)|答案(2)|浏览(269)

所以这就是我的问题,我想通过html和php向我的数据库表中添加一个特定行的过期日期,但我不知道怎么了。由于某些原因,它不会在时间用完时删除特定的行。
有没有可能向数据库中添加一些内容,例如,在php中,在一列中引入过期时间?我只是从这个php mysqli交互开始,所以代码可能很糟糕!
html格式:

  1. <div class="modal fade" id="addModal" role="dialog">
  2. <div class="modal-dialog">
  3. <div class="modal-content">
  4. <div class="modal-header">
  5. <h4 class="modal-title">VIPS</h4>
  6. </div>
  7. <div class="modal-body">
  8. <div class="container">
  9. <form class="form-horizontal" action="insert.php" method="post" onsubmit="setTimeout('location.reload()', 10);">
  10. <div class="form-group">
  11. <label class="control-label col-sm-2" for="full name">SteamID</label>
  12. <input class="form-control" type="text" id="identity" name="identity" placeholder="SteamID" ng-model="newUser.fullname"/>
  13. </div>
  14. <div class="form-group">
  15. <label class="control-label col-sm-2" for="full name">Name</label>
  16. <input class="form-control" type="text" placeholder="Name" id="Name" name="name" ng-model="newUser.fullname"/>
  17. </div>
  18. <div class="form-group">
  19. <label class="control-label col-sm-2" for="full name">immunity</label>
  20. <input class="form-control" type="text" id="immunity" name="immunity" placeholder="Imunidade" ng-model="newUser.fullname"/>
  21. </div>
  22. <div class="form-group">
  23. <label class="control-label col-sm-2" for="full name">Time</label>
  24. <select id="time" name="time" data-plugin-selectTwo class="form-control populate">
  25. <optgroup label="Staff">
  26. <option value="1">1 minute</option>
  27. <option value="2">2 minute</option>
  28. </optgroup>
  29. </select>
  30. <div class="form-group">
  31. <label class="control-label col-sm-2" for="full name">Flags</label>
  32. <select id="flags" name="flags" data-plugin-selectTwo class="form-control populate">
  33. <optgroup label="Staff">
  34. <option value="zo">Founder</option>
  35. <option value="abcdefghjkp">Admin</option>
  36. <option value="abcdfgjkq">Mod</option>
  37. </optgroup>
  38. </select>
  39. <div class="modal-footer">
  40. <button class="btn btn-primary" type="submit" value="ADICIONAR">Add</button>
  41. <button class="btn btn-default" type="button" data-dismiss="modal">Close</button>
  42. </div>
  43. </div>
  44. </form>
  45. </div>

PHP:

  1. <?php
  2. $conn = new mysqli("", "", "", "");
  3. if ($conn->connect_error) {
  4. die("Connection failed");
  5. }
  6. // Escape user inputs for security
  7. $identity = mysqli_real_escape_string($conn, $_REQUEST['identity']);
  8. $name = mysqli_real_escape_string($conn, $_REQUEST['name']);
  9. $flags = mysqli_real_escape_string($conn, $_REQUEST['flags']);
  10. $immunity = mysqli_real_escape_string($conn, $_REQUEST['immunity']);
  11. $time=$_POST['time'];
  12. $sql = "SELECT COUNT(*) FROM sm_admins WHERE identity = ('$identity')";
  13. if($count = $conn->query($sql)){
  14. if($count == 1){
  15. $sql = "INSERT INTO sm_admins
  16. (identity, name, flags, immunity, '', '', now(),
  17. DATE_ADD(NOW(), INTERVAL time='time' MINUTE))
  18. VALUES ('$identity', '$name', '$flags', '$immunity')";
  19. if($conn->query($sql)){
  20. echo "Good Connection";
  21. }
  22. }else{
  23. echo "Identity already exist";
  24. }
  25. }
  26. // close connection
  27. header('Location: panel.php');
  28. mysqli_close($conn);
  29. ?>
vfwfrxfs

vfwfrxfs1#

…在时间用完时删除特定行。
你不能用mysql做这个。您必须使用php提出一个解决方案。在这种情况下,cron jobs可能会派上用场。
如何使用php创建cron作业?
一个解决方法是添加一列 DATETIME 到你的table上去。将默认值设置为 NOW() . 因此,在向表中插入记录时,插入的日期和时间将保存在该列中。
然后在检索数据时,向搜索查询中添加一个条件,以按过期日期和时间筛选信息

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

rur96b6h2#

您当前的查询是:

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

它需要更改,以便数据库知道您想要什么。因为我没有sm\u admin表的所有字段名,所以我将使用f1、f2、f3等,所以您需要替换为适当的字段名

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

编辑:
原来的sql语句不起作用。上面的更正将起作用,但您需要将f1..f5更改为相关的字段名。
至于自动删除记录,可以使用cronjob或mysql事件。文档可在https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html. cronjob将取决于您的服务器。下面是一个mysql事件调度的框架-详细信息请阅读文档。

  1. DELIMITER $$
  2. -- SET GLOBAL event_scheduler = ON$$ -- required for event to execute but not create
  3. CREATE /*[DEFINER = { user | CURRENT_USER }]*/ EVENT `_u3a_work`.`xx`
  4. ON SCHEDULE
  5. /* uncomment the example below you want to use */
  6. -- scheduleexample 1: run once
  7. -- AT 'YYYY-MM-DD HH:MM.SS'/CURRENT_TIMESTAMP { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] }
  8. -- scheduleexample 2: run at intervals forever after creation
  9. -- EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]
  10. -- scheduleexample 3: specified start time, end time and interval for execution
  11. /*EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]
  12. STARTS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1[HOUR|MONTH|WEEK|DAY|MINUTE|...] }
  13. ENDS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] } */
  14. /*[ON COMPLETION [NOT] PRESERVE]
  15. [ENABLE | DISABLE]
  16. [COMMENT 'comment']*/
  17. DO
  18. BEGIN
  19. (sql_statements)
  20. END$$
  21. DELIMITER ;
展开查看全部

相关问题