Jump to the post that solved this thread.
Solved: 9 Months, 3 Weeks, 5 Days ago Use simple_select to get custom profile fields
#1
Solved: 9 Months, 3 Weeks, 5 Days ago
Hello,

I'm using Page Manager to make separate memberlists for member groups. I want to add a custom profile field next to the usernames.

Here's the code I'm using for group id 18:

<?php

global $headerinclude, $header, $theme, $footer, $db, $xtpf, $userfields;

$altbg = alt_trow();
$userlist = '';
$query = $db->simple_select("users", "*", "usergroup IN ('18')", array("order_by" => 'username', "order_dir" => 'ASC'));


 while($users = $db->fetch_array($query))
 {
	 
 $username = build_profile_link(format_name(htmlspecialchars_uni($users['username']), $users['usergroup'], $users['displaygroup']), $users['uid']);
 
 $userlist .= '<tr><td class="'.$altbg.'">'.$username.', '.$users["usertitle"].'</td></tr>';
 $altbg = alt_trow();
 }







$template='<html>
<head>
<title>'.$pages['name'].'</title>
{$headerinclude}
</head>
<body>
{$header}
<table border="0" cellspacing="'.$theme['borderwidth'].'" cellpadding="'.$theme['tablespace'].'" class="tborder">
<thead>
<tr>
<td class="thead">
<strong>'.$pages['name'].'</strong>
</td>
</tr>
</thead>
<tbody>
{$userlist}
</tbody>
</table>
<br />
{$footer}
</body>
</html>';

$template=str_replace("\'", "'", addslashes($template));

add_breadcrumb($pages['name']);

eval("\$page=\"".$template."\";");

output_page($page);

?>

As profile fields are stored in a different table, I can't fetch the custom fields with the code I have above. My PHP knowledge is limited. I tried to fetch fid4 inside the while loop, as I needed to use the user_id variable I defined above.

Here's what I got:
$userid = $users['uid'];
	$useroptq = $db->simple_select("userfields", "*", "ufid='.$userid.'");
$user_opt = $db->fetch_array($useroptq);
	 $user_opt2 = $user_opt["fid4"];

This is not working, though. No errors, no outputs, when I add $user_opt2 in the page.

What am I doing wrong?
Reply
#2
Solved: 9 Months, 3 Weeks, 5 Days ago
Well, first I don't think using "IN" clause to get an unique value is optimal.
And this is the case you'd better use write_query than simple_select to extract your datas:
$query = $db->write_query("SELECT u.*, f.fid4 FROM ".TABLE_PREFIX."users u LEFT JOIN ".TABLE_PREFIX."userfields f ON f.ufid=u.uid WHERE u.usergroup=18 ORDER BY u.username ASC");
while($users = $db->fetch_array($query))
{
   $username = build_profile_link(format_name(htmlspecialchars_uni($users['username']), $users['usergroup'], $users['displaygroup']), $users['uid']);
   $ufield = '';
   if ($users['fid4']=='')
   {
      $ufield = '<br />'.$users['fid4'];
   }
   $userlist .= '<tr><td class="'.$altbg.'">'.$username.', '.$users["usertitle"].'<br />'.$ufield.'</td></tr>';
   $altbg = alt_trow();
 }
[MyBB 1.8 mods] - Offer me a coffee
Do not ask me help through PM
Reply
#3
Solved: 9 Months, 3 Weeks, 5 Days ago
(2020-09-22, 01:49 PM)Crazycat Wrote: Well, first I don't think using "IN" clause to get an unique value is optimal.
And this is the case you'd better use write_query than simple_select to extract your datas:
$query = $db->write_query("SELECT u.*, f.fid4 FROM ".TABLE_PREFIX."users u LEFT JOIN ".TABLE_PREFIX."userfields f ON f.ufid=u.uid WHERE u.usergroup=18 ORDER BY u.username ASC");
while($users = $db->fetch_array($query))
{
   $username = build_profile_link(format_name(htmlspecialchars_uni($users['username']), $users['usergroup'], $users['displaygroup']), $users['uid']);
   $ufield = '';
   if ($users['fid4']=='')
   {
      $ufield = '<br />'.$users['fid4'];
   }
   $userlist .= '<tr><td class="'.$altbg.'">'.$username.', '.$users["usertitle"].'<br />'.$ufield.'</td></tr>';
   $altbg = alt_trow();
 }

Thank you very much.
Your code didn't show user fields though (although they are filled).
I deleted the if and it works now, here is the final code:

<?php

global $headerinclude, $header, $theme, $footer, $db, $xtpf, $userfields;

$altbg = alt_trow();

$query = $db->write_query("SELECT u.*, f.fid4 FROM ".TABLE_PREFIX."users u LEFT JOIN ".TABLE_PREFIX."userfields f ON f.ufid=u.uid WHERE u.usergroup=18 ORDER BY u.username ASC");
while($users = $db->fetch_array($query))
{
   $username = build_profile_link(format_name(htmlspecialchars_uni($users['username']), $users['usergroup'], $users['displaygroup']), $users['uid']);

      $ufield = $users['fid4'];
  
   $userlist .= '<tr><td class="'.$altbg.'">'.$username.', '.$users["usertitle"].' '.$ufield.'</td></tr>';
   $altbg = alt_trow();
 }











$template='<html>
<head>
<title>'.$pages['name'].'</title>
{$headerinclude}
</head>
<body>
{$header}
<table border="0" cellspacing="'.$theme['borderwidth'].'" cellpadding="'.$theme['tablespace'].'" class="tborder">
<thead>
<tr>
<td class="thead">
<strong>'.$pages['name'].'</strong>
</td>
</tr>
</thead>
<tbody>
{$userlist}
</tbody>
</table>
<br />
{$footer}
</body>
</html>';

$template=str_replace("\'", "'", addslashes($template));

add_breadcrumb($pages['name']);

eval("\$page=\"".$template."\";");

output_page($page);

?>

Would this cause a problem when the field is empty? (for the case of fid4, it is required but what about other fields?)
Reply
#4
Solved: 9 Months, 3 Weeks, 5 Days ago
I add the condition because I wanted to add a <br /> only if the field was filled. I just made an operator error: if ($users['fid4']!='') is the right condition Smile
[MyBB 1.8 mods] - Offer me a coffee
Do not ask me help through PM
Reply
#5
Solved: 9 Months, 3 Weeks, 5 Days ago
Quote:Use simple_select to get custom profile fields

$query = $db->simple_select("users u LEFT JOIN {$db->table_prefix}userfields f ON (f.ufid=u.uid)", 'u.*, f.fid4', 'u.usergroup=18', ['order_by' => 'u.username', 'order_dir' => 'asc']);
Reply
#6
Solved: 9 Months, 3 Weeks, 5 Days ago
(2020-09-22, 08:01 PM)Omar G. Wrote:
Quote:Use simple_select to get custom profile fields

$query = $db->simple_select("users u LEFT JOIN {$db->table_prefix}userfields f ON (f.ufid=u.uid)", 'u.*, f.fid4', 'u.usergroup=18', ['order_by' => 'u.username', 'order_dir' => 'asc']);

I never figured it could be possible to use join in simple_select... Next time, I'll look at simple_select() code before answering Smile
Thanks Omar for the tip.
[MyBB 1.8 mods] - Offer me a coffee
Do not ask me help through PM
Reply
Jump to the post that solved this thread.


Forum Jump:


Users browsing this thread: 1 Guest(s)