• We just launched and are currently in beta. Join us as we build and grow the community.

How to Set up Expiration on MySQL Row in PHP/MySQLi

flareclare100

Anime News Reporter
F
F Rep
0
0
0
Rep
0
F Vouches
0
0
0
Vouches
0
Posts
96
Likes
47
Bits
0
2 MONTHS
2 2 MONTHS OF SERVICE
LEVEL 1 200 XP
This tutorial will show you how to set up expiration on mysql row in PHP/MySQLi. In this tutorial, I've set up to update the expired row but in case that you wanted to delete the row, I've added the delete code in the comment. So feel free to switch it. This tutorial also has two mysqli methods that I've included in the comments as well so you can switch between them.

Creating our Database

I've created a sample database that we are going to use in this tutorial.
1. Open phpMyAdmin.
2. Click databases, create a database and name it as "expiration".
3. After creating a database, click the SQL and paste the below code. See image below for detailed instruction.

  1. CREATE

    TABLE

    `user`

    (
  2. `userid`

    INT

    (

    11

    )

    NOT

    NULL

    AUTO_INCREMENT

    ,
  3. `username`

    VARCHAR

    (

    30

    )

    NOT

    NULL

    ,
  4. `password`

    VARCHAR

    (

    30

    )

    NOT

    NULL

    ,
  5. `login_date`

    datetime NOT

    NULL

    ,
  6. `status`

    VARCHAR

    (

    15

    )

    NOT

    NULL

    ,
  7. PRIMARY

    KEY

    (

    `userid`

    )
  8. )

    ENGINE=

    InnoDB DEFAULT

    CHARSET=

    latin1;

database_6.png

Creating our Connection

Next step is to create a database connection and save it as "conn.php". This file will serve as our bridge between our form and our database. To create the file, open your HTML code editor and paste the code below after the tag.

  1. <?php

  3. //MySQLi Procedural
  4. //$conn = mysqli_connect("localhost","root","","expiration");
  5. //if (!$conn) {
  6. // die("Connection failed: " . mysqli_connect_error());
  7. //}

  9. //MySQLi Object-oriented
  10. $conn

    =

    new

    mysqli(

    "localhost"

    ,

    "root"

    ,

    ""

    ,

    "expiration"

    )

    ;
  11. if

    (

    $conn

    ->

    connect_error

    )

    {
  12. die

    (

    "Connection failed: "

    .

    $conn

    ->

    connect_error

    )

    ;
  13. }

  15. ?>

Creating our Sample Table

Lastly, we create our sample table. This table will show the expiration of our row. We name this as "index.php".

  1. <DOCTYPE html>
  2. <html>
  3. <head>
  4. <title>Set up Expiration on MySQL Row in PHP</title>
  5. </head>
  6. <body>
  7. <h2>Sample Login Table</h2>
  8. <table border="1">
  9. <thead>
  10. <th>UserID</th>
  11. <th>Username</th>
  12. <th>Password</th>
  13. <th>Login Date</th>
  14. <th>Expiry</th>
  15. <th>Status</th>
  16. </thead>
  17. <tbody>
  18. <?php
  19. include

    (

    'conn.php'

    )

    ;

  21. //$query=mysqli_query($conn,"select * from `user`");
  22. //while($row=mysqli_fetch_array($query)){
  23. /* ?>
  24. <tr>
  25. <td><?php echo $row['userid']; ?></td>
  26. <td><?php echo $row['username']; ?></td>
  27. <td><?php echo $row['password']; ?></td>
  28. <td><?php echo $row['login_date']; ?></td>
  29. <td>
  30. <?php
  31. //set up your timezone using date_default_timezone_set
  32. $today=date('Y-m-d H:i:s');
  33. //we set up our row to expire 3 days after login_date
  34. $expire=date('Y-m-d H:i:s', strtotime($row['login_date']. '+3 days'));

  36. if ($today>=$expire){
  37. //if you wanted to delete row if expired you can do so by substituting the code in the comment below
  38. //mysqli_query($conn,"delete `user` where userid='".$row['userid']."'");

  40. mysqli_query($conn,"update `user` set status='Expire' where userid='".$row['userid']."'");
  41. echo $expire;
  42. }
  43. else{
  44. echo $expire;
  45. }
  46. ?>
  47. </td>
  48. <td><?php echo $row['status']; ?></td>
  49. </tr>

  51. <?php */
  52. //}

  54. $query

    =

    $conn

    ->

    query

    (

    "select * from `user`"

    )

    ;
  55. while

    (

    $row

    =

    $query

    ->

    fetch_array

    (

    )

    )

    {
  56. ?>
  57. <tr>
  58. <td><?php

    echo

    $row

    [

    'userid'

    ]

    ;

    ?>

    </td>
  59. <td><?php

    echo

    $row

    [

    'username'

    ]

    ;

    ?>

    </td>
  60. <td><?php

    echo

    $row

    [

    'password'

    ]

    ;

    ?>

    </td>
  61. <td><?php

    echo

    $row

    [

    'login_date'

    ]

    ;

    ?>

    </td>
  62. <td>
  63. <?php
  64. //set up your timezone using date_default_timezone_set
  65. $today

    =

    date

    (

    'Y-m-d H:i:s'

    )

    ;
  66. //we set up our row to expire 3 days after login_date
  67. $expire

    =

    date

    (

    'Y-m-d H:i:s'

    ,

    strtotime

    (

    $row

    [

    'login_date'

    ]

    .

    '+3 days'

    )

    )

    ;

  69. if

    (

    $today

    >=

    $expire

    )

    {

  71. //if you wanted to delete row if expired you can do so by substituting the code in the comment below
  72. //$conn->query("delete `user` where userid='".$row['userid']."'");

  74. $conn

    ->

    query

    (

    "update `user` set status='Expire' where userid='"

    .

    $row

    [

    'userid'

    ]

    .

    "'"

    )

    ;
  75. echo

    $expire

    ;
  76. }
  77. else

    {
  78. echo

    $expire

    ;
  79. }
  80. ?>
  81. </td>
  82. <td><?php

    echo

    $row

    [

    'status'

    ]

    ;

    ?>

    </td>
  83. </tr>

  85. <?php
  86. }

  88. ?>
  89. </tbody>
  90. </table>
  91. </body>
  92. </html>


Download
You must upgrade your account or reply in the thread to view hidden text.
 
You must log in or register to reply here.

452,292

323,340

323,349

R rathi
Top