1
I have a table that has ticket info. ( Ticket Subject, TicketDetails, StatusID, ClientID, PriorityID)
I have other tables that equate the StatusID, ClientID, PriorityID, etc with names for the IDs.
I need to query the DB to retreive all of the ticket data. But instead of the ClientID, StatusID, PiorityID, etc. I would like to have the equated name returned. I know that I need to do a fairly complex table join. But so far no luck with it.
Here is what I tried last:
-------------------------------
function chooseUserTickets( $UID )
{
global $xoopsDB;
$ret = array();
$ticket_table = $xoopsDB->prefix('obsclient_hd_ticketdata');
$client_table = $xoopsDB->prefix('obsclient_clients');
$category_table = $xoopsDB->prefix('obsclient_hd_categories');
$status_table = $xoopsDB->prefix('obsclient_hd_status');
$priority_table = $xoopsDB->prefix('obsclient_hd_priorities');
$sql = "SELECT ticket.TicketID, ticket.EntryDate, ticket.TicketSubject, client.ClientName, category.CategoryName, status.StatusName, prority.PriorityName FROM $ticket_table AS ticket LEFT JOIN $client_table AS client, $category_table AS category, $status_table AS status, $priority_table AS priority ON client.ClientID = ticket.ClientID, category.CategoryID = ticket.CategoryID, status.StatusID = ticket.StatusID, priority.PriorityID = ticket.PriorityID WHERE ticket.UID = $UID";
$result_choose = $xoopsDB->query($sql);
while ( $client_row = $xoopsDB->fetchArray($result_choose) )
{
$ret[] = array('TicketID' => $client_row['TicketID'], 'EntryDate' => $client_row['EntryDate'], 'TicketSubject' => $client_row['TicketSubject'], 'ClientName' => $client_row['ClientName'], 'CategoryName' => $client_row['CategoryName'], 'StatusName' => $client_row['StatusName'], 'PriorityName' => $client_row['PriorityName']);
}
return $ret;
}
}
-------------------------------
This give me MySql syntax errors!
Here is the DB structure:
-------------------------------
#
# Table structure for table `obsclient_hd_priorities`
#
CREATE TABLE obsclient_hd_priorities (
PriorityID int(10) NOT NULL auto_increment,
PriorityName varchar(50) NOT NULL default '',
PRIMARY KEY (PriorityID)
) TYPE=MyISAM;
#
# Insert data into 'obsclient_hd_clients'
#
INSERT INTO obsclient_hd_priorities VALUES (1, 'Unassigned');
INSERT INTO obsclient_hd_priorities VALUES (2, 'Low');
INSERT INTO obsclient_hd_priorities VALUES (3, 'Medium');
INSERT INTO obsclient_hd_priorities VALUES (4, 'High');
INSERT INTO obsclient_hd_priorities VALUES (5, 'Critical');
# --------------------------------------------------------
#
# Table structure for table `obsclient_hd_ticketdata`
#
CREATE TABLE obsclient_hd_ticketdata (
TicketID int(10) NOT NULL auto_increment,
UID int(10) NOT NULL default '0',
StatusID int(10) NOT NULL default '0',
ClientID int(10) NOT NULL default '0',
CategoryID int(10) NOT NULL default '0',
PriorityID int(10) NOT NULL default '0',
EntryDate date NOT NULL default '0000-00-00',
TicketSubject varchar(255) NOT NULL default '',
TicketDetails text NOT NULL default '',
PRIMARY KEY (TicketID)
) TYPE=MyISAM;
#
# Insert data into 'obsclient_hd_ticketdata'
#
INSERT INTO obsclient_hd_ticketdata VALUES (1, 1, 1, 1, 1, 1, '2003-11-11', 'TestSubject1', 'TestDetail1');
INSERT INTO obsclient_hd_ticketdata VALUES (2, 1, 2, 2, 2, 2, '2003-12-12', 'TestSubject2', 'TestDetail2');
INSERT INTO obsclient_hd_ticketdata VALUES (3, 0, 3, 3, 3, 3, '2003-10-10', 'TestSubject3', 'TestDetail3');
-------------------------------
Any sample code would be greatly appreciated!
JMass
