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

How to pull via SQL custom form entered data

Folks, I know this is easy, and I am not asking to MOD the distribution. 

I have a help topic called travel task, and it uses a custom form called travel form which asks some special questions.

After someone enters all of the data into this travel ticket. I want to simply later run a php/sql query outside of osticket so I can pull all the info that has been gathered.

But I simply don't know in which osticket database table the info will be sitting in.

I want to later simply give a sql query similar to

SELECT field1, field2....etc from table where key is equal to topic id = 17   (I was able to find out that my travel ticket topic has been assigned topic id 17)

I have attached some screen shots of the version number, as well as the ticket custom form that defines the fields/columns of the entries that I assume are being placed in the database somewhere....

Similarly, what would be other table and fields that I should query to get the basic ticket info that every ticket collects. 

Many thanks everyone.

Mike





Comments

  • edited June 2016
    imageimageimageimage
    Screen Shot 2016-06-01 at 5.40.33 PM.png
    890 x 502 - 119K
    Screen Shot 2016-06-01 at 5.29.45 PM.png
    688 x 407 - 84K
    Screen Shot 2016-06-01 at 5.29.16 PM.png
    456 x 298 - 49K
    Screen Shot 2016-05-28 at 8.00.02 PM.png
    636 x 312 - 56K
  • You can turn on MySQL logging,


    if you have access to the MySQL server.  Then you can see what's being pulled up when you hit the various screens.


  • edited June 2016
    My post got cut off from too many characters.


    (Disclaimer: I wrote that one, for better or worse.)

    This might point you in the right direction.  I can't post the SQL here because it cuts me off in the post saying it's too many characters.
    SELECT    ht.topic, 
              d.dept_name, 
              p.priority_desc, 
              ts.state, 
              Concat_ws(',', sf.lastname, sf.firstname) AS staff_member, 
              t.number, 
              u.NAME, 
              lsit.extra, 
              t.created, 
              t.ticket_id 
    FROM      ost_ticket t 
    LEFT JOIN ost_user u 
    ON        t.user_id = u.id 
    LEFT JOIN ost_staff sf 
    ON        t.staff_id = sf.staff_id 
    LEFT JOIN ost_department d 
    ON        t.dept_id = d.dept_id 
    LEFT JOIN ost_ticket__cdata tcd 
    ON        t.ticket_id = tcd.ticket_id 
    LEFT JOIN ost_list_items lsit 
    ON        Substring_index(tcd.location, ' ', 1) = lsit.id 
    LEFT JOIN ost_help_topic ht 
    ON        t.topic_id = ht.topic_id 
    LEFT JOIN ost_ticket_status ts 
    ON        t.status_id = ts.id 
    LEFT JOIN ost_ticket_priority p 
    ON        tcd.priority = p.priority_id 
    WHERE     t.created BETWEEN $p{ticketStartDate} AND       $p{ticketEndDate}
  • Thanks, it is going to take me a bit of time to figure out what this query is trying to do. Yes I have direct access to the sql server so I can run queries against it 



  • It just retrieves who did what.  You have to look at my note on Github, however, about "location."
Sign In or Register to comment.