Adding cust_field to recent query (also about changing time-output)

[Ahadawan]

Hello

I should start by stating that I'm using SMF 2.0.10 and have a portal installed so that I don't forget and you have to ask.

I have been looking around in the boards and would appreciate if someone had time to help me out with this since I'm uncertain how to merge what I found into the query I have.
Also I'm trying to add a custom time to the output and on that note I'm not exactly sure how to do it.

I have a query which I think is originally from a portal or maybe the recent function. It's working great but I want to add two custom fields to it. My users have a field where they can add a small image and another for a nickname. I understand that there fields are located under the themes or sometimes options and are not be default loaded in the member table.

I've searched around and found this topic (linking to the message I think is related to my issue)
http://www.simplemachines.org/community/index.php?topic=495304.msg3473561#msg3473561

In that topic Arantor gives the thread owner an example of a query:

Code Select Expand

SELECT mem.id_member, th.value
FROM smf_members AS mem
LEFT JOIN smf_themes AS th
  ON (th.id_theme = 1 AND th.id_member = mem.id_member AND th.variable = 'cust_fieldname')
WHERE mem.id_member = <whatever the member's id is>

I think I should be able to use this if I was able (and allowed) to integrate it to the code I have.

I have included the query and construction of the row further below, it is a little long but I did not want to cut into it leaving something relevant out of it.

I feel that I am semi-confident when it comes to simple stuff but adding to a query and thinking about page-loads et cetera is new-ish territory for me.

This is what I need:
1. my cust_fields are: 'cust_myava' and 'cust_mynick' and I need to grab that for both the first and last poster I'm uncertain how to merge and change the code above with mine below. 

2. I also need to try and customize the time so that it displays only the time if today, and only the date if earlier. (Should I start a new topic for that or can I keep this as one topic with two questions?)  So '12:03' if today and 'may 20' if earlier. If I could still use "yesterday" (I have Today & Yesterday active in forum time) that would be awesome.

If you are reading my topic yet are not sure how to help I thank you for taking the time to read it.
If you have a code-snippet or some help to give me I am even more thankful.
Regardless: Have a great day/evening or whatever time it is at your location.


Below is my code mentioned above:

Code Select Expand

if ($do_query == 1)
{
$request = $smcFunc['db_query']("", "
SELECT
      ms.subject AS firstSubject, ms.poster_time AS firstPosterTime, ms.id_topic, t.id_last_msg, t.id_board, b.name AS bname,
      t.num_replies, t.num_views, ms.id_member AS ID_FIRST_MEMBER, ml.id_member AS ID_LAST_MEMBER,
      ml.poster_time AS lastPosterTime, IFNULL(mems.real_name, ms.poster_name) AS firstPosterName,
      IFNULL(meml.real_name, ml.poster_name) AS lastPosterName,
      mems.id_group as mems_group, meml.id_group as meml_group,
      ml.subject AS lastSubject, b.member_groups,
      t.is_sticky, t.locked, ml.modified_time AS lastModifiedTime,
      LEFT(ml.body, 384) AS lastBody, LEFT(ms.body, 384) AS firstBody,
      ml.smileys_enabled AS lastSmileys, ms.smileys_enabled AS firstSmileys, t.id_first_msg,"
      . ($user_info['is_guest'] ? '1 AS isRead, 0 AS new_from' : '
      IFNULL(lt.id_msg, IFNULL(lmr.id_msg, 0)) >= ml.id_msg_modified AS isRead,
      IFNULL(lt.id_msg, IFNULL(lmr.id_msg, -1)) + 1 AS new_from') . "
   FROM (".$db_prefix."messages AS ms, ".$db_prefix."messages AS ml, ".$db_prefix."topics AS t, ".$db_prefix."boards AS b)
      LEFT JOIN ".$db_prefix."members AS mems ON (mems.id_member = ms.id_member)
      LEFT JOIN ".$db_prefix."members AS meml ON (meml.id_member = ml.id_member)
      LEFT JOIN ".$db_prefix."log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = ".$user_info['id'].")
      LEFT JOIN ".$db_prefix."log_mark_read AS lmr ON (lmr.id_board = t.id_board AND lmr.id_member = ".$user_info['id'].")
   WHERE " . $where_clause . $ex_board_clause . "
      AND t.id_topic = ms.id_topic
      AND b.id_board = t.id_board" . (!empty($modSettings['recycle_enable']) && $modSettings['recycle_board'] > 0 ? " AND b.id_board != ".$modSettings['recycle_board'] : "") . "
      AND t.locked = 0
      AND ms.id_msg = t.id_first_msg
      AND ml.id_msg = t.id_last_msg
      AND " . $user_info['query_see_board'] . "
   ORDER BY " . $order_clause . " " . $limit_clause,
array());

$topic_ids = array();
while ($row = $smcFunc['db_fetch_assoc']($request))
{

$topic_ids[] = $row['id_topic'];

// Some stuff for censoring titles and what not

// And build the array.
$topics[$row['id_topic']] = array(
   'id' => $row['id_topic'],
   'first_post' => array(
      'id' => $row['id_first_msg'],
      'member' => array(
         'name' => $row['firstPosterName'],
         'name2' => '<font color="' . $color_start . '">' . $row['firstPosterName'] . '</font>',
         'id' => $row['ID_FIRST_MEMBER'],
         'href' => $scripturl . '?action=profile;u=' . $row['ID_FIRST_MEMBER'],
         'link' => !empty($row['ID_FIRST_MEMBER']) ? '<a href="' . $scripturl . '?action=profile;u=' . $row['ID_FIRST_MEMBER'] . '" title="' . $txt['profile_of'] . ' ' . $row['firstPosterName'] . '">' . '<font color="' . $color_start . '">' . $row['firstPosterName'] . '</font>' . '</a>' : $row['firstPosterName']
      ),
      'time' => timeformat($row['firstPosterTime']),
      'timestamp' => forum_time(true, $row['firstPosterTime']),
      'short_subject' => shorten_subject($row['firstSubject'], 20),
'subject' => $row['firstSubject'],
      'preview' => $row['firstBody'],
      'href' => $scripturl . '?topic=' . $row['id_topic'] . '.0;topicseen',
      'link' => '<a href="' . $scripturl . '?topic=' . $row['id_topic'] . '.0;topicseen">' . $row['firstSubject'] . '</a>'
   ),
   'last_post' => array(
      'id' => $row['id_last_msg'],
      'member' => array(
         'name' => $row['lastPosterName'],
         'name2' => '<font color="' . $color_last . '">' . $row['lastPosterName'] . '</font>',
         'id' => $row['ID_LAST_MEMBER'],
         'href' => $scripturl . '?action=profile;u=' . $row['ID_LAST_MEMBER'],
         'link' => !empty($row['ID_LAST_MEMBER']) ? '<a href="' . $scripturl . '?action=profile;u=' . $row['ID_LAST_MEMBER'] . '">' . '<font color="' . $color_last . '">' . $row['lastPosterName'] . '</font>' . '</a>' : $row['lastPosterName']
      ),
      'time' => timeformat($row['lastPosterTime']),
      'timestamp' => forum_time(true, $row['lastPosterTime']),
         'short_subject' => shorten_subject($row['firstSubject'], 20),
'subject' => $row['lastSubject'],
      'preview' => $row['lastBody'],
      'href' => $scripturl . '?topic=' . $row['id_topic'] . ($row['num_replies'] == 0 ? '.0' : '.msg' . $row['id_last_msg']) . ';topicseen#msg' . $row['id_last_msg'],
      'link' => '<a href="' . $scripturl . '?topic=' . $row['id_topic'] . ($row['num_replies'] == 0 ? '.0' : '.msg' . $row['id_last_msg']) . ';topicseen#msg' . $row['id_last_msg'] . '">' . $row['lastSubject'] . '</a>'
   ),
   'new' => $row['isRead'],
   'new_from' => $row['new_from'],
   'new_href' => $scripturl . '?topic=' . $row['id_topic'] . '.msg' . $row['new_from'] . ';topicseen#new',
   'href' => $scripturl . '?topic=' . $row['id_topic'] . ($row['num_replies'] == 0 ? '.0' : '.msg' . $row['new_from']) . ';topicseen' . ($row['num_replies'] == 0 ? '' : 'new'),
   'link' => '<a href="' . $scripturl . '?topic=' . $row['id_topic'] . ($row['num_replies'] == 0 ? '.0' : '.msg' . $row['new_from']) . ';topicseen#msg' . $row['new_from'] . '">' . $row['firstSubject'] . '</a>',
   'is_sticky' => !empty($modSettings['enableStickyTopics']) && !empty($row['isSticky']),
   'is_locked' => !empty($row['locked']),
   'is_posted_in' => false,
   'subject' => $row['firstSubject'],
   'replies' => $row['num_replies'],
   'views' => $row['num_views'],
   'board' => array(
      'id' => $row['id_board'],
      'name' => $row['bname'],
      'href' => $scripturl . '?board=' . $row['id_board'] . '.0',
      'link' => '<a href="' . $scripturl . '?board=' . $row['id_board'] . '.0">' . $row['bname'] . '</a>'
   )
);

}
$smcFunc['db_free_result']($request);
}