Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

Automatically delete record from ost_ticket_event table when ticket is deleted

Records displayed in Statistics even after deleting tickets in Osticket v1.7

Open class.ticket.php file.
Find:
$sql = 'DELETE FROM '.TICKET_TABLE.' WHERE ticket_id='.$this->getId().' LIMIT 1';
if(!db_query($sql) || !db_affected_rows())
return false;

Replace with:

$sql = 'DELETE FROM '.TICKET_TABLE.' WHERE ticket_id='.$this->getId().' LIMIT 1';
$sql1 = 'DELETE FROM '.TICKET_EVENT_TABLE.' WHERE ticket_id='.$this->getId();
if(!db_query($sql) || !db_query($sql1) || !db_affected_rows())
return false;

Save and refresh.

Comments

  • Thanks for posting this!


    I hope you don't mind, but I modified your code a bit to keep a one-to-one relationship:

    Under:
    $this->getThread()->delete();


    Insert:
    $this->deleteEvents();


    Then under the delete function - I inserted the following:
    	function deleteEvents() {

    $sql = 'DELETE FROM '.TICKET_EVENT_TABLE.' WHERE ticket_id='.$this->getId();
    if(!db_query($sql) || !db_affected_rows())
    return false;

    return true;
    }
  • thanks hawkhead99

    I tried it and it works as i need. Thanks for the modification you did.
  • I just updated this and it is working on 1.9.2. I took the Delete function and made the changes above and added the deleteEvents function as well (as listed above), but made just a few changes as the exact coding above was not used in 1.9.2.

        function delete() {

            //delete just orphaned ticket thread & associated attachments.
            // Fetch thread prior to removing ticket entry
            $t = $this->getThread();

            //$sql = 'DELETE FROM '.TICKET_TABLE.' WHERE ticket_id='.$this->getId().' LIMIT 1';
            //if(!db_query($sql) || !db_affected_rows())
            //    return false;
            $sql = 'DELETE FROM '.TICKET_TABLE.' WHERE ticket_id='.$this->getId().' LIMIT 1';
            $sql1 = 'DELETE FROM '.TICKET_EVENT_TABLE.' WHERE ticket_id='.$this->getId();
            if(!db_query($sql) || !db_query($sql1) || !db_affected_rows())
                return false;

            $t->delete();
            $t->deleteEvents();

            foreach (DynamicFormEntry::forTicket($this->getId()) as $form)
                $form->delete();

            $this->deleteDrafts();

            return true;
        }
       
        //deleteEvents has been added 07/10/2014 by Che Geiser per hawkhead99 on the osTicket forums
        function deleteEvents() {

            $sql = 'DELETE FROM '.TICKET_EVENT_TABLE.' WHERE ticket_id='.$this->getId();
            if(!db_query($sql) || !db_affected_rows())
                return false;

            return true;
        }
  • <rant>
    ARGH!
    Damn MySQL and not having referential integrity by default! A simple "ON DELETE CASCADE" clause would have been enough...
    </rant>
  • I've found that the code above allows you to delete the ticket (including from event_table) but won't allow for using check-boxes to delete multiple tickets and it also requires that you backup one screen in your browser after using it. I'm new to coding and have no idea how to fix these two issues. But at least there's a way to totally delete the record.

    I would normally never delete a record but right now I'm migrating a Spiceworks database as well as an osTicket test run into the production environment and being new to all this my ability to edit the MySQL files is limited. I ran into a problem in that phpMyAdmin (used to edit MySQL files) won't allow me to edit the Event table. Edit and delete options are there for most tables but not for the event table. Any possible solutions on this one? This would probably be better than a built in Delete function in osTicket that could be abused.

    Thanks!
Sign In or Register to comment.