Thought I had it....But still not quite right.
I no longer get syntax erros but does not return the info I need.


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, priority.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 AND category.CategoryID = ticket.CategoryID AND status.StatusID = ticket.StatusID AND 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;

}

Stupid "AND" instead of ","


Thanks,

JMass

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

Perfect!

I am very new to "Black Magic" of Table Joins. I am always amazed how you guys whip out great code like that. I can modify existing code to meat my needs very well, but the from scratch stuff takes me forever.

This is my final code that is implemented and working:

function chooseUpdateFields( $TicketID )
{

global $xoopsDB;
$ret = array();
$ticket_table = $xoopsDB->prefix('obsclient_hd_ticketupdate');
$users_table = $xoopsDB->prefix('users');

$sql = "SELECT u.uname, t.EntryDate, t.UpdateDetails FROM $ticket_table AS t LEFT JOIN $users_table AS u ON u.uid = t.UID WHERE t.ParentTicketID = $TicketID";

$result_choose = $xoopsDB->query($sql);

while ( $client_row = $xoopsDB->fetchArray($result_choose) )

{
$ret[] = array('UNAME' => $client_row['uname'], 'EntryDate' => $client_row['EntryDate'], 'UpdateDetails' => $client_row['UpdateDetails']);

}
return $ret;
}

Thanks again for the great code,

JMass

Thanks for the direction to look. If anyone has some example of a joint-query, that would be great.

Yes it is a Help Desk type Ticketing system.

Thanks,

JMass

Here is the table structure:

# Table structure for table `obsclient_hd_ticketupdate`

#

CREATE TABLE obsclient_hd_ticketupdate (

TicketUpdateID int(10) NOT NULL auto_increment,
ParentTicketID int(10) NOT NULL default '0',
UID int(10) NOT NULL default '0',
EntryDate date NOT NULL default '0000-00-00',
UpdateDetails text NOT NULL default '',

PRIMARY KEY (TicketUpdateID)

) TYPE=MyISAM;
#
# 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;
#

The ParentTicketID from obsclient_hd_ticketupdate is the TicketID from obsclient_hd_ticketdata.

UID in both cases is the user entering the data.

Thanks for the encouragement,

JMass

I don't know if I am technically inclined... seem like declined more and more lately.

But that is just fine. I don't mind picking apart the code to figure it out, I just did not knowwhere to look.

Thanks for pointing me in the right direction,

JMass

I have and array of arrays that are pulled from a MySQL table to pass to a Smarty section:

function chooseUpdateFields( $TicketID )
{

global $xoopsDB;
$ret = array();

$sql = "SELECT UID, EntryDate, UpdateDetails FROM ".$xoopsDB->prefix("obsclient_hd_ticketupdate")." WHERE ParentTicketID = ".$TicketID;

$result_choose = $xoopsDB->query($sql);

while ( $client_row = $xoopsDB->fetchArray($result_choose) )

{
$ret[] = array('UID' => $client_row['UID'], 'EntryDate' => $client_row['EntryDate'], 'UpdateDetails' => $client_row['UpdateDetails']);

}
return $ret;

}

The problem is that it passes the user id and I want to display the username. Any thoughts?

JMass

Thanks!

Where is that documented?

JMass

I am using the following code:

redirect_header("index.php",2,_SUBMIT_THANKS);

I saw it in the New module. It works fine, but I was curious about the syntax. What does the var 2 do?

The other question is wher can I find this out with out bothering all of you fine folks?

JMass

Way to general of a question to provide code examples. (That is how I laern best)

There is an excellent example of what you desribe in the Forums. A user can post and then come back later to edit his/her post. Check out how it is done there.