[Solved] Delete row if key code is there for more than 15 min - wolfgang1983 - 02-05-2017
I have a database table which has a column called code.
I would like to know using codeigniter active record if any row that has a code still present for more that 15 min then it will delete that row.
I am not sure if I have my clear_unconfirmed_post correct?
PHP Code: public function clear_unconfirmed_post() { $this->db->where('code IS NOT NULL', null); $query = $this->db->get($this->db->dbprefix . 'post');
if ($query->num_rows() > 0) {
// Delete rows if code have been there for 15 min return $query->num_rows(); } }
Model
PHP Code: <?php
class Newthread_model extends CI_Model {
public function check_if_code_set($code = NULL) { $this->db->where('code', $code); $query = $this->db->get($this->db->dbprefix . 'post');
if ($query->num_rows() > 0) { return TRUE; } else { return FALSE; } }
public function get_tmp_post() { $this->db->where('post_id', $this->session->userdata('post_id')); $query = $this->db->get($this->db->dbprefix . 'post');
if ($query->num_rows() > 0) { return $query->row_array(); } else { return FALSE; } }
public function insert_tmp_post($data = array()) { $this->db->set($data); $this->db->insert($this->db->dbprefix . 'post'); return $this->db->insert_id(); }
public function update_tmp_post($data = array()) { $this->db->set($data); $this->db->where('post_id', $this->session->userdata('post_id')); $this->db->update($this->db->dbprefix . 'post'); }
public function delete_post() { $this->db->where('post_id', $this->session->userdata('post_id')); $this->db->delete($this->db->dbprefix . 'post');
$this->session->unset_userdata('post_id'); }
public function clear_unconfirmed_post() {
} }
Controller
PHP Code: <?php
class Newthread extends MX_Controller {
public function __construct() { parent::__construct(); $this->load->library('form_validation'); $this->load->helper('string'); $this->load->model('catalog/forum/newthread_model'); }
public function index($forum_id = NULL) { $code = random_string('alpha', 32);
$data['forum_id'] = $forum_id;
$this->form_validation->set_rules('subject', 'subject', 'trim|required'); $this->form_validation->set_rules('message', 'message', 'trim|required');
if ($this->form_validation->run() == TRUE) {
if ($this->input->post('newthread_preview')) {
$tmp_post_data = array( 'forum_id' => '1', 'user_id' => '1', 'subject' => $this->input->post('subject'), 'message' => $this->input->post('message'), 'date_modified' => date('Y-m-d H:i:s'), 'code' => $this->input->post('code') );
if ($this->newthread_model->check_if_code_set($this->input->post('code'))) { $this->newthread_model->update_tmp_post($tmp_post_data);
} else { $post_id = $this->newthread_model->insert_tmp_post($tmp_post_data);
$this->session->set_userdata(array('post_id' => $post_id)); } }
if ($this->input->post('newthread_delete')) {
$this->newthread_model->delete_post();
redirect(base_url('forum')); }
}
$post_data = $this->newthread_model->get_tmp_post();
if ($this->input->post('code')) { $data['code'] = $this->input->post('code'); } elseif (!empty($post_data)) { $data['code'] = $post_data['code']; } else { $data['code'] = $code; }
if ($this->input->post('subject')) { $data['subject'] = $this->input->post('subject'); } elseif (!empty($post_data)) { $data['subject'] = $post_data['subject']; } else { $data['subject'] = ''; }
if ($this->input->post('message')) { $data['message'] = $this->input->post('message'); } elseif (!empty($post_data)) { $data['message'] = $post_data['message']; } else { $data['message'] = ''; }
$data['header'] = Modules::run('catalog/common/header/index'); $data['footer'] = Modules::run('catalog/common/footer/index'); $data['preview'] = Modules::run('catalog/forum/newthread_preview/index', $post_data);
$this->load->view('template/forum/newthread_view', $data); } }
RE: Delete row if key code is there for more than 15 min - casa - 02-05-2017
hye.
If i understand your issue, you want to delete all rows where date_modified (field in your table) are older than 15 minutes.
If you want it at every request who call your controller/method, you take the time at the controller access and you make a sql query which compare the time now et the time with date_modified. If delta between now and date_mdified > 15 minutes your delete the row. Use timestamp and you compare (15*60 seconds) between.
See sql query delete and had when your delta > 15*60 seconds.
If you want to delete rows when 15 minutes have passed without call your controller/method, develop a trigger in your database for example or a method using javascript for example combined with php to have the elapsed time, and after make your removal request.
Advices in your code : you will protect it more, one example among others, use $this->input->post('data', TRUE) and add rules for your form validation and perhaps use config file to store rules for your form validation.
RE: Delete row if key code is there for more than 15 min - Diederik - 02-05-2017
Just perform a single sql
Code: DELETE FROM post
WHERE date_modifieda < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 15 MINUTE))
RE: Delete row if key code is there for more than 15 min - wolfgang1983 - 02-05-2017
(02-05-2017, 03:29 AM)Diederik Wrote: Just perform a single sql
Code: DELETE FROM post
WHERE date_modifieda < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 15 MINUTE))
I tried this but does not work
PHP Code: public function clear_unconfirmed_post() { $this->db->where('code is NOT NULL', NULL, FALSE); $this->db->where('date_modified < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 15 MINUTE))'); $this->db->delete($this->db->dbprefix . 'post'); }
RE: Delete row if key code is there for more than 15 min - Wouter60 - 02-05-2017
Just like the first "where" clause, add two extra paramaters NULL and FALSE to it:
PHP Code: $this->db->where('date_modified < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 15 MINUTE))',NULL,FALSE);
This will override the normal syntax of db->where, which takes 2 parameters: fieldname and conditional value.
RE: Delete row if key code is there for more than 15 min - wolfgang1983 - 02-06-2017
(02-05-2017, 11:57 PM)Wouter60 Wrote: Just like the first "where" clause, add two extra paramaters NULL and FALSE to it:
PHP Code: $this->db->where('date_modified < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 15 MINUTE))',NULL,FALSE);
This will override the normal syntax of db->where, which takes 2 parameters: fieldname and conditional value.
I did not work I placed the function in the construct area so once I reload page it checks it.
PHP Code: public function __construct() { parent::__construct(); $this->load->model('catalog/forum/newthread_model'); $this->newthread_model->clear_unconfirmed_post(); }
What I am trying to achieve is if there is any columns in my table that still have a code present after 15 min then will remove that row.
date_modified looks like 2017-02-16 13:53:18
PHP Code: public function clear_unconfirmed_post() { $this->db->where('code is NOT NULL', NULL, FALSE); $this->db->where('date_modified < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 15 MINUTE))', NULL, FALSE); $this->db->delete($this->db->dbprefix . 'post'); }
RE: Delete row if key code is there for more than 15 min - Wouter60 - 02-06-2017
Temporarily add these lines after $this->db->delete(...) :
PHP Code: echo $this->db->last_query(); die();
This will output the query which was generated by the Query Builder. You can verify if this is what you could expect.
RE: Delete row if key code is there for more than 15 min - wolfgang1983 - 02-07-2017
(02-06-2017, 11:04 PM)Wouter60 Wrote: Temporarily add these lines after $this->db->delete(...) :
PHP Code: echo $this->db->last_query(); die();
This will output the query which was generated by the Query Builder. You can verify if this is what you could expect.
I had to change column on DB from datetime to int(10) and use time() instead of date('Y-m-d H:i')
RE: [Solved] Delete row if key code is there for more than 15 min - Wouter60 - 02-07-2017
Another way would be this:
PHP Code: $dt = new Datetime(); //create object for current date/time $dt->modify('15 minutes ago'); //substract 15 minutes $sdt = $dt->format('Y-m-d H:i:s'); //format it into a datetime string
$this->db->where('date_modified <' , $sdt);
|