Welcome Guest, Not a member yet? Register   Sign In
[Solved] Delete row if key code is there for more than 15 min
#1

(This post was last modified: 02-07-2017, 01:07 AM by wolfgang1983.)

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();
        }
    

[Image: 3BKCemcmTc8i.png]

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);
    }



Attached Files
.php   Newthread.php (Size: 2.34 KB / Downloads: 103)
.php   Newthread_model.php (Size: 1.17 KB / Downloads: 106)
There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!
Reply
#2

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.
Reply
#3

Just perform a single sql
Code:
DELETE FROM post
WHERE date_modifieda < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 15 MINUTE))
Reply
#4

(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'NULLFALSE);
     $this->db->where('date_modified < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 15 MINUTE))');
     $this->db->delete($this->db->dbprefix 'post');

There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!
Reply
#5

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.
Reply
#6

(This post was last modified: 02-06-2017, 06:03 PM by wolfgang1983.)

(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'NULLFALSE);
    $this->db->where('date_modified < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 15 MINUTE))'NULLFALSE);
    $this->db->delete($this->db->dbprefix 'post');



[Image: 3BW8oHNZZMAP.png]
There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!
Reply
#7

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.
Reply
#8

(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:iConfused')
There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!
Reply
#9

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); 
Reply




Theme © iAndrew 2016 - Forum software by © MyBB