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:- ";
for($i=65; $i<=90; $i=$i+1)
{
echo '<a href="' . $_SERVER['SCRIPT_NAME'] . '?u_letter=&#' . $i . '">&#' . $i . ' </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);
?>