How to run loop every 24 hours and delete rows from mysql table

Question

I want to make a function which deletes all the jobs where status is pending. I want to run every 24 hours. I'm thinking to add sleep or something. can someone suggest how to do this?

Function

  function find_by_job_status($status) {
    global $db;
    // create a prepared statement
    $sql = "SELECT * FROM jobs WHERE status = ?";
    $stmt = mysqli_stmt_init($db);
    mysqli_stmt_prepare($stmt, $sql);
    // bind parameters for placeholders
    mysqli_stmt_bind_param($stmt, "s", $status);
    // execute the query
    mysqli_stmt_execute($stmt);
    return mysqli_stmt_get_result($stmt);
    // close statement
    mysqli_stmt_close($stmt);
  }

  function delete_pending_jobs() {
    $jobs = find_by_job_status('pending');
    while ($row = mysqli_fetch_assoc($$jobs)) {
      // Delete All Pending jobs
      // Wait for 24 hours
      // Loop again
    }
  }

I think best way to execute every 24 hours is a cronjob. There is no function call of `mysqli_fetch_array()` in your code that throws the error. — Robin Gillitzer, Mar 03 '20 at 08:00
Is your question only how to execute every 24 hours or the error message in your title? — Robin Gillitzer, Mar 03 '20 at 08:05
@RobinGillitzer Oh sorry, I didn't notice the title. It's updated now — , Mar 03 '20 at 08:08
*I want to run every 24 hours* You can do that with cron job! or sleep function is well, which will run your page every 24 hours, cron job is more usefull. change `while ($row = mysqli_fetch_assoc($$jobs)` to this `while ($row = mysqli_fetch_assoc($jobs)` — , Mar 03 '20 at 08:17
Why not use a cronjob that starts a new process once a day? PHP might not be the best language for long-running scripts — Nico Haase, Mar 03 '20 at 08:43

Robin Gillitzer · Accepted Answer · 2020-03-03T08:26:09.993

The best way to execute every 24 hours is cronjob. Here you can read about how to setup these: How to create cron job using PHP?

Your code should be like this. In the first function i have stored the result first because once you return something, the rest of the code wont execute.

function find_by_job_status($status) {
  global $db;

  // create a prepared statement
  $sql = "SELECT * FROM jobs WHERE status = ?";
  $stmt = mysqli_stmt_init($db);
  mysqli_stmt_prepare($stmt, $sql);

  // bind parameters for placeholders
  mysqli_stmt_bind_param($stmt, "s", $status);

  // execute the query
  mysqli_stmt_execute($stmt);

  // store the result in var
  $result = mysqli_stmt_get_result($stmt);

  // close statement
  mysqli_stmt_close($stmt);

  // return result
  return $result;
}

function delete_pending_jobs() {
  global $db;

  $jobs = find_by_job_status('pending');

  while ($row = mysqli_fetch_assoc($jobs)) {
    $stmt = mysqli_stmt_init($db);

    // create a prepared statement
    $sql = "DELETE FROM jobs WHERE id = ?";
    mysqli_stmt_prepare($stmt, $sql);

    // bind parameters for placeholders
    mysqli_stmt_bind_param($stmt, "s", $row['id']);

    // execute the query
    mysqli_stmt_execute($stmt);

    // close statement
    mysqli_stmt_close($stmt);
  }
}

I would prefer to outsource the delete_pending_jobs function to a new file that is executed by the cronjob.

How to run loop every 24 hours and delete rows from mysql table

1 Answers1