View all custom profile fields in a sortable table - Please try this

Need some custom code changes to the phpBB core simple enough that you feel doesn't require an extension? Then post your request here so that community members can provide some assistance.

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
dave2565
Registered User
Posts: 10
Joined: Mon Mar 25, 2024 2:15 am

View all custom profile fields in a sortable table - Please try this

Post by dave2565 »

I have put together some php code to enable you to view all of the phpbb3 custom profile fields in a table.
Unlike the memberlist each custom field is displayed in a seperate column of the table and can easily be sorted.
The list can be sorted by the first letter of the username and ascending or descending order by clicking on the column name.
The attached files contain instructions on how to use the code and an example of the table header (the code will retreive your column names automatically).
If you find this code useful or have any comments please reply.
Thanks
Dave

Code: Select all

  
<html>
<title>Display All Members and Custom Profiles</title> 
<script src="sorttable.js"></script>
<head>

<style>
 body, td, th {
 font-family: Tahoma, Geneva, sans-serif;
}
.sortable th:not(.sorttable_nosort):not(.sorttable_sorted):not(.sorttable_sorted_reverse):after{
	opacity:0.75;
	display:inline;
	content:'\a0\25B4';
}
</style>

</head>
<?php

include 'config.php';
$dbhost = "localhost";

//-------------------------------------------------
// Modify this session to your requirements
//-------------------------------------------------
// Custom field name prefix
// ALL Custom field name MUST have this prefix
//-------------------------------------------------

    $field_prefix = "phpbb_";

//-------------------------------------------------
// Set Custom Column Order
//
// See # numbers of field names in structure of 
// phpbb_profile_fields_data  - starting from #2
//-------------------------------------------------

// Don't set field1, field2, and field3
// All other fields must be included

    $field3="4";
    $field4="5";
    $field5="6";
    $field6="8";
    $field7="7";
    $field8="3";
    $field9="2";
    
//-------------------------------------------------

$conn = mysqli_connect($dbhost, $dbuser, $dbpasswd, $dbname);

    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }
//establishing connection to the database

$mysqli = mysqli_connect($dbhost, $dbuser, $dbpasswd, $dbname);
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//-------------------------------------------------

?>

<script>
function refresh() 
{ 
    window.location.reload(); 
}
</script>

<body text="#000000" link="#006600" vlink="#990000" >
<table>
<div align="left">
  <p><span><u>Display All Members and Custom Profiles</u></span></p>
 
  <table width="1550" border="0">
       <td width="528" height="26" valign="middle" ><div align="justify">
 
<?php

//-------------------------------------------------
// $query_1
//-------------------------------------------------

$query_1 = "SELECT  * from phpbb_profile_fields_data ORDER BY user_id";

//-------------------------------------------------
// Sending $query_1 to obtain custom column names
//-------------------------------------------------

 if ($result = $mysqli->query($query_1)) {

        /* Get field names for custom columns */
        $finfo = $result->fetch_fields();

        foreach ($finfo as $val) {
           
            $column_names = $column_names . ",' phpbb_profile_fields_data." . $val->name . "'";
        
       }   
    
    }
   
   $result -> free_result(); 

$my_array = explode(",",  $column_names);

//-------------------------------------------------
// Display alphabet
//-------------------------------------------------

echo "Select Initial of User Name:- &nbsp;&nbsp;&nbsp;";

for($i=65; $i<=90; $i=$i+1)
	{
		echo '<a href="' . $_SERVER['SCRIPT_NAME'] . '?u_letter=&#' . $i . '">&#' . $i . '&nbsp;</a>';		
	}

    echo '<a href="' . $_SERVER['SCRIPT_NAME'] . '?u_letter=*' . '"> All' . '</a>';
    $u_letter = trim($_GET['u_letter']);
   
    if ($u_letter == ""){
    $u_letter = "*" ;
    }

setcookie('$u_letter', $u_letter);

 ?>
 
 <!----------------------------------------------->
 <!-- Display sort titles                       -->
 <!----------------------------------------------->
 
 </div>
 
      <tr>
        <td height= "40">Click on column title to sort</td>
      </tr>
 
 </td>
</table>
 
<?php
                                    
    $str = $u_letter;
   
    $str2 =  str_replace('phpbb_profile_fields_data.pf_' . $field_prefix , ' ',$str);

//-------------------------------------------------
// Creating $query_2 to obtain all columns
//-------------------------------------------------

$mid_query_str = str_replace("'", "", $column_names);

$query_2 = "SELECT phpbb_users.user_id, phpbb_users.username, phpbb_users.user_email " . $mid_query_str . " FROM phpbb_users LEFT JOIN phpbb_profile_fields_data ON phpbb_users.user_id = phpbb_profile_fields_data.user_id";

$query_2 = str_replace("'", "", $query_2);

$query_2 = str_replace(" phpbb_profile_fields_data.user_id, ", "", $query_2);

//-------------------------------------------------
//Sort by Lastname Initial selected
//-------------------------------------------------

if ($u_letter == "*") {
    
             $sql =  $query_2 . "  ORDER BY phpbb_users.user_id";
        
            
        } elseif (  (ctype_alpha($u_letter) == 1)  && (strlen($u_letter) == 1 ) ) {
            
             $sql = $query_2 . " where left(phpbb_users.username,1)='$u_letter'";     
             
        } else {
             
        if ($u_letter = $str2) {
           
            $sql =  $query_2 .  "  ORDER BY " . $str;
        }
                
         if (!$result) {
             
            die("Query to show fields from table failed");
         }
}

//-------------------------------------------------
// Display table column names and data
//-------------------------------------------------

$my_array = explode(",",  $column_names);

 $result = mysqli_query($mysqli, $sql);

    if (mysqli_num_rows($result) > 0) 
        {
         ?>
                  <table class="sortable" border='1'>
                        <tr style="height:35px" bgcolor = '#DDEEAA' >  
                        
                      <th  width="100"><font color="#006A4E">user_id</font></td>
                        
                            <th  width="150"><font color="#006A4E">username</font></td>
                                
                            <th width="260"><font color="#006A4E">user_email</font></td>    
                             
                                
         <?php 
                                    for ($x =3; $x <= count($my_array); $x++) {
                                        
                                      $a = str_replace(array("'", '"'), ' ', $my_array[${"field" . $x}]);
                                      $b = str_replace(array("'", 'phpbb_profile_fields_data.pf_phpbb_'), ' ', $my_array[${"field" . $x}]);  
         ?>
                                     <th width="240" ><font color="#006A4E"><?php echo $b ;           ?></font></td>
         <?php
                                }
         ?>
                        </tr>
                      
         <?php
                      
           while($row = mysqli_fetch_array($result)) 
                            {
                                
                    ?>
                                <tr>
                                    <td width="140"><?php echo $row["user_id"]; ?></td>
                                    <td width="150"><?php echo $row["username"]; ?></td>
                                    <td width="260"><?php echo $row["user_email"]; ?></td>
  
                                 <?php
                                        for ($x =3; $x <= count($my_array); $x++) {
                                   ?>
                                 
                                    <td width="240"><?php echo $row[${"field" . $x}+1]; ?></td>
                                  
                                   <?php
                                    }
                                    ?>   
                           </tr> 
                    
                     <?php         
                       
                            $i++;
                            }                     
                   ?>
                        </table>
                       <?php
                            }
                            else{
                            echo "No result found";              
 }

mysql_free_result($result);
 
?>
   
 
You do not have the required permissions to view the files attached to this post.
Last edited by dave2565 on Tue May 21, 2024 2:15 pm, edited 5 times in total.
dave2565
Registered User
Posts: 10
Joined: Mon Mar 25, 2024 2:15 am

Re: How to view all custom profile fields in a sortable table

Post by dave2565 »

Has anyone tried this ?
If so please could you let me know. Do you have any comments ?

Thanks dave
Farraday
Registered User
Posts: 8
Joined: Mon Jan 01, 2024 1:08 am

Re: How to view all custom profile fields in a sortable table - Has anyone tried this ?

Post by Farraday »

This is almost exactly what I'm going to need soon, thank you!! It may be some months before I try it out, but I'll try to remember to let you know how it goes.
dave2565
Registered User
Posts: 10
Joined: Mon Mar 25, 2024 2:15 am

Re: How to view all custom profile fields in a sortable table - Has anyone tried this ?

Post by dave2565 »

Thank You.

Return to “phpBB Custom Coding”