MySQL Query from PHP

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?

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’ phumpal@execinc.com sent:

RT’s database is heavily normalized.

I wouldn’t say “heavily” normalized, just moderately normalized. I’ve
seen much, MUCH worse.

Eric Schultz
United Online, Inc.

RT’s database is heavily normalized.

I wouldn’t say “heavily” normalized, just moderately normalized. I’ve
seen much, MUCH worse.

And we have a few places where we’ve explicitly denormalized for some
very realworld issues. (Owner is one). But that’s nothing compared to
what you need to do to handle tags properly in web 2.0 apps :wink:

hey,

Thanks for the direction. I had been trying to do joins off the wrong
table and did not include Transactions in my statement.

As an FYI, the query was something like this:

$query = “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.ObjectId
= ‘$ticketId’”

Now I have a fully functional PHP interface for people to view the
status of tickets without having to log into RT itself. Users can select
between Queues and see the progress.

All that’s left is to hack together a RegEx to parse Attachments.Content
to the correct format and I’m golden.

Again, thanks for everyone’s time and comments. Its much appreciated.

patFrom: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Judson
Main
Sent: Thursday, October 12, 2006 11:45 AM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] MySQL Query from PHP

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]’,‘Popu
p_Window’,‘toolbar=no,location=no,directories=no,status=no,menubar=no,sc
rollbars=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’ phumpal@execinc.com 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:
sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com