RT's database is heavily normalized.
You'll need to use "left join" in your mysql statements. Here's an example:
select
t.id,q.Name,t.Subject,t.FinalPriority,t.Status,t.Created,t.LastUpdated,t.Resolved,u.Name,u.Name
from Tickets t left join Users u on u.id = t.Owner and u.id = t.LastUpdatedBy
left join Queues q on q.id = t.Queue where t.Subject like '$searchsubject'
order by t.id;
where in this case I'm searching on a string, $searchsubject, and only
returning specific fields. Note that I'm left joining
on the Users and Queues tables to get the real name of the people involved, not
just the id.
Here's another one where I'm searching on an IP:
select
t.id,q.Name,t.Subject,t.FinalPriority,t.Status,t.Created,t.LastUpdated,t.Resolved,u.Name,u.Name,c.Content
from Tickets t left join Users u on u.id = t.Owner and u.id = t.LastUpdatedBy
left join Queues q on q.id = t.Queue left join TicketCustomFieldValues c on
c.Ticket = t.id where c.Content like '$sourcenum%'
Note I brought in the table TicketCustomFieldValues as source IP is one of our
custom fields.
For Attachments to a ticket, I actually call a different script and pop it up
in a separate browser window using javascript.
Here's the popup code:
<ahref=\"javascript:window.open('searchtickets.php?tid=$result[0]','Popup_Window','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=yes,copyhistory=no,width=1000,height=900,top=200,left=300')&&void(0)\">
and here's the search from the other script - I'm passing the ticket id as $tid:
select t.id,t.Type,t.OldValue,t.NewValue,u.Name,t.Created,a.Content from
Transactions t join Users u on u.id = t.Creator join Attachments a on
a.TransactionId = t.id where t.Ticket = '$tid'"
Hope this is enough to get you started. My needs came from the fact that I had
3.0.10 on a linux box for the old
ticketing system, and 3.6.1 on a different OS for the new, and considering the
upgrade path involved here, it
was simpler to write some php code.
Jud.
On Thu Oct 12 12:10 , 'Patrick Humpal' <[EMAIL PROTECTED]> sent:
>
>
>
>
>
>I've create a small
>interface in PHP that submits a ticket to RT using the
>rt-mailgate.
>
>However, I've been
>trying to pull data from the MySQL database so I don't have to constantly
>create/manage user accounts in RT. This cusotmized interface pulls the basic
>ticket info from the Tickets table fine. However, when I go to view the
>history
>of the ticket it pulls random info from Attachments.
>
>I understand that it
>is more an object-oriented thing from Perl's DBI-builder that allows RT to
>customize how it pulls data, but how should I go about constructing a MySQL
>query to pull all data on a particular ticket based on the Ticekts.id and
>Tickets.EffectiveId?
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
