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

No information on your obsclient_hd_ticketupdate table structure. Likely you would have to do a joint-query on the user table to pull out the username.

BTW, "technically declined?" No, you are doing great.

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

You would definitely need to do a joint-query on both obsclient_hd_ticketupdate and user table to pull out the username.

Good luck. It seems to be a nice of ticketing system you are developing.

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

Something like this:

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

Quote:

Just spend a few months writing PHP/MySQL stuff, and you'll pick it up.