Welcome to Geeklog, Anonymous Sunday, December 22 2024 @ 09:54 pm EST
Geeklog Forums
Userlist hack SQL question
Status: offline
jordydme
Forum User
Full Member
Registered: 11/03/05
Posts: 135
Hey, I have been using the the userlist hack by Dirk. I need a little help. Been paluing around with it on my own and not haveing any progress.
Currenly I have my members sorted the way I want them (by fullname). I have a separate table in my GL database called custom_userinfo. This table has a 4 digit graduation year column called grad_year. I would like the last two digits of that four digit number to appear after each name. For example, the names would look like this:
Joe Shmo 87
Ralph Peano 89
This is the code from DIrk's userlist hack:
function listusers($curpage)
{
global $_TABLES, $_CONF, $LANG01, $LANG04;
$limit = 50; // this is the number of users listed per page
$retval = COM_startBlock($LANG01[114]);
$retval .= '<table width="100%" border="0">' . LB;
$retval .= '<tr><td><b>' . $LANG04[3] . '</b></td><td><b>' . $LANG04[2] . '</b></td></tr>' . LB;
$num_pages = ceil(DB_getItem($_TABLES['users'],'count(*)','uid > 1') / $limit);
$offset = ($curpage - 1) * $limit;
$sql = "SELECT uid,username,fullname,email,photo FROM {$_TABLES['users']} WHERE uid > 1 ORDER BY fullname LIMIT $offset,$limit";
$result = DB_query($sql);
$nrows = DB_numRows($result);
for ($i = 0; $i < $nrows; $i++) {
$A = DB_fetchArray($result);
$retval .= '<tr><td><a href="' . $_CONF['site_url']
. '/users.php?mode=profile&uid=' . $A['uid'] . '">'
. $A['fullname'] . '</a>';
if (!empty($A['photo']) AND $_CONF['allow_user_photo'] == 1) {
$retval .= ' <a href="' . $_CONF['site_url'] . '/users.php?mode=profile&uid=' . $A['uid'] . '"><img src="' . $_CONF['layout_url'] . '/images/smallcamera.gif" border="0" alt=""></a>';
}
$retval .= '</td><td>' . $A['username']
. '</td><td><a href="' . $_CONF['site_url']
. '/profiles.php?uid=' . $A['uid']
. '">' . $LANG04[81] .'</a></td></tr>' . LB;
}
$retval .= '</table>' . LB;
if ($num_pages > 1) {
$retval .= '<p>' . COM_printPageNavigation ($PHP_SELF, $curpage, $num_pages) . '</p>';
}
$retval .= COM_endBlock();
return $retval;
}
// MAIN
$display = COM_siteHeader ('menu');
if (empty ($_USER['username'])) { // prevent anon users from viewing the list
This is what I tried to do but I don't really understand this fully
$sql = "SELECT uid,username,fullname,email,photo FROM {$_TABLES['users']} WHERE uid > 1 ORDER BY fullname LIMIT $offset,$limit";
$sql = "SELECT uid,grad_year FROM {$_TABLES['custom_userinfo']} WHERE uid > 1 ORDER BY fullname LIMIT $offset,$limit";
Any guidance would be greatly appreciated,
Jordy
Currenly I have my members sorted the way I want them (by fullname). I have a separate table in my GL database called custom_userinfo. This table has a 4 digit graduation year column called grad_year. I would like the last two digits of that four digit number to appear after each name. For example, the names would look like this:
Joe Shmo 87
Ralph Peano 89
This is the code from DIrk's userlist hack:
Text Formatted Code
function listusers($curpage)
{
global $_TABLES, $_CONF, $LANG01, $LANG04;
$limit = 50; // this is the number of users listed per page
$retval = COM_startBlock($LANG01[114]);
$retval .= '<table width="100%" border="0">' . LB;
$retval .= '<tr><td><b>' . $LANG04[3] . '</b></td><td><b>' . $LANG04[2] . '</b></td></tr>' . LB;
$num_pages = ceil(DB_getItem($_TABLES['users'],'count(*)','uid > 1') / $limit);
$offset = ($curpage - 1) * $limit;
$sql = "SELECT uid,username,fullname,email,photo FROM {$_TABLES['users']} WHERE uid > 1 ORDER BY fullname LIMIT $offset,$limit";
$result = DB_query($sql);
$nrows = DB_numRows($result);
for ($i = 0; $i < $nrows; $i++) {
$A = DB_fetchArray($result);
$retval .= '<tr><td><a href="' . $_CONF['site_url']
. '/users.php?mode=profile&uid=' . $A['uid'] . '">'
. $A['fullname'] . '</a>';
if (!empty($A['photo']) AND $_CONF['allow_user_photo'] == 1) {
$retval .= ' <a href="' . $_CONF['site_url'] . '/users.php?mode=profile&uid=' . $A['uid'] . '"><img src="' . $_CONF['layout_url'] . '/images/smallcamera.gif" border="0" alt=""></a>';
}
$retval .= '</td><td>' . $A['username']
. '</td><td><a href="' . $_CONF['site_url']
. '/profiles.php?uid=' . $A['uid']
. '">' . $LANG04[81] .'</a></td></tr>' . LB;
}
$retval .= '</table>' . LB;
if ($num_pages > 1) {
$retval .= '<p>' . COM_printPageNavigation ($PHP_SELF, $curpage, $num_pages) . '</p>';
}
$retval .= COM_endBlock();
return $retval;
}
// MAIN
$display = COM_siteHeader ('menu');
if (empty ($_USER['username'])) { // prevent anon users from viewing the list
This is what I tried to do but I don't really understand this fully
Text Formatted Code
$sql = "SELECT uid,username,fullname,email,photo FROM {$_TABLES['users']} WHERE uid > 1 ORDER BY fullname LIMIT $offset,$limit";
$sql = "SELECT uid,grad_year FROM {$_TABLES['custom_userinfo']} WHERE uid > 1 ORDER BY fullname LIMIT $offset,$limit";
Any guidance would be greatly appreciated,
Jordy
8
7
Quote
Status: offline
jmucchiello
Forum User
Full Member
Registered: 08/29/05
Posts: 985
Text Formatted Code
$sql = "SELECT u.uid as uid,u.username as username, u.fullname as fullname, u.email as email, u.photo as photo, cu.grad_year as grad_yearFROM {$_TABLES['users']} u LEFT JOIN {$_TABLES['custom_userinfo']} ON u.uid = cu.uid
WHERE u.uid > 1 ORDER BY u.fullname LIMIT $offset,$limit";
Text Formatted Code
<a href="' . $_CONF['site_url']. '/users.php?mode=profile&uid=' . $A['uid'] . '">'
. $A['fullname'] . substr($A['grad_year'], -2) , '</a>'
6
6
Quote
Status: offline
jordydme
Forum User
Full Member
Registered: 11/03/05
Posts: 135
Hey thanks for the help. i just tried what you suggested and it didn't work, the memberlist now becomes inoperative (white page). This is what my code looks like with your changes:
$A = DB_fetchArray($result);<br /> $retval .= '<br />//new test code<br /><a />
. '/users.php?mode=profile&uid=' . $A['uid'] . '">'<br /> . $A['fullname'] . substr($A['grad_year'], -2) , '</a>'<br /> if (!empty($A['photo']) AND $_CONF['allow_user_photo'] == 1) {<br /> $retval .= ' <a href="' . $_CONF['site_url'] . '/users.php?mode=profile&uid=' . $A['uid'] . '"></a>';<br /> }<br /> $retval .= '' . $A['username']<br /> . '<a />
. '/profiles.php?uid=' . $A['uid']<br /> . '">' . $LANG04[81] .'</a>' . LB;<br />
I do believe I made the two changes you suggested but nothing changed. Are you trying to join the two tables using UNION to combine the results form the select queries?
Text Formatted Code
//old code that worked<br /> // $sql = "SELECT uid,username,fullname,email,photo FROM {$_TABLES['users']} WHERE uid > 1 ORDER BY fullname LIMIT $offset,$limit";<br /> // $result = DB_query($sql);<br /> // $nrows = DB_numRows($result);<br /> <br /> // new test code<br /> $sql = "SELECT u.uid as uid,u.username as username, u.fullname as fullname, u.email as email, u.photo as photo, cu.grad_year as grad_year<br /> FROM {$_TABLES['users']} u LEFT JOIN {$_TABLES['custom_userinfo']} ON u.uid = cu.uid <br /> WHERE u.uid > 1 ORDER BY u.fullname LIMIT $offset,$limit";<br /><br /> for ($i = 0; $i $A = DB_fetchArray($result);<br /> $retval .= '<br />//new test code<br /><a />
. '/users.php?mode=profile&uid=' . $A['uid'] . '">'<br /> . $A['fullname'] . substr($A['grad_year'], -2) , '</a>'<br /> if (!empty($A['photo']) AND $_CONF['allow_user_photo'] == 1) {<br /> $retval .= ' <a href="' . $_CONF['site_url'] . '/users.php?mode=profile&uid=' . $A['uid'] . '"></a>';<br /> }<br /> $retval .= '' . $A['username']<br /> . '<a />
. '/profiles.php?uid=' . $A['uid']<br /> . '">' . $LANG04[81] .'</a>' . LB;<br />
I do believe I made the two changes you suggested but nothing changed. Are you trying to join the two tables using UNION to combine the results form the select queries?
9
6
Quote
Status: offline
jmucchiello
Forum User
Full Member
Registered: 08/29/05
Posts: 985
There's a missing few letters in my sql:
$sql = "SELECT u.uid as uid,u.username as username, u.fullname as fullname, u.email as email, u.photo as photo, cu.grad_year as grad_year
FROM {$_TABLES['users']} u LEFT JOIN {$_TABLES['custom_userinfo']} cu ON u.uid = cu.uid
WHERE u.uid > 1 ORDER BY u.fullname LIMIT $offset,$limit";
This is not a union. It is a left outer join.
$sql = "SELECT u.uid as uid,u.username as username, u.fullname as fullname, u.email as email, u.photo as photo, cu.grad_year as grad_year
FROM {$_TABLES['users']} u LEFT JOIN {$_TABLES['custom_userinfo']} cu ON u.uid = cu.uid
WHERE u.uid > 1 ORDER BY u.fullname LIMIT $offset,$limit";
This is not a union. It is a left outer join.
6
6
Quote
Status: offline
jordydme
Forum User
Full Member
Registered: 11/03/05
Posts: 135
Tried it, and still got the blank white page. This is what i have for my code now:
$sql = "SELECT u.uid as uid,u.username as username, u.fullname as fullname, u.email as email, u.photo as photo, cu.grad_year as grad_year
FROM {$_TABLES['users']} u LEFT JOIN {$_TABLES['custom_userinfo']} cu ON u.uid = cu.uid
WHERE u.uid > 1 ORDER BY u.fullname LIMIT $offset,$limit";
for ($i = 0; $i < $nrows; $i++) {
$A = DB_fetchArray($result);
$retval .= '<tr><td><a href="' . $_CONF['site_url']
. '/users.php?mode=profile&uid=' . $A['uid'] . '">'
. $A['fullname'] . substr($A['grad_year'], -2) , '</a>'
if (!empty($A['photo']) AND $_CONF['allow_user_photo'] == 1) {
$retval .= ' <a href="' . $_CONF['site_url'] . '/users.php?mode=profile&uid=' . $A['uid'] . '"><img src="' . $_CONF['layout_url'] . '/images/smallcamera.gif" border="0" alt=""></a>';
}
$retval .= '</td><td>' . $A['username']
. '</td><td><a href="' . $_CONF['site_url']
. '/profiles.php?uid=' . $A['uid']
. '">' . $LANG04[81] .'</a></td></tr>' . LB;
Text Formatted Code
// new test code$sql = "SELECT u.uid as uid,u.username as username, u.fullname as fullname, u.email as email, u.photo as photo, cu.grad_year as grad_year
FROM {$_TABLES['users']} u LEFT JOIN {$_TABLES['custom_userinfo']} cu ON u.uid = cu.uid
WHERE u.uid > 1 ORDER BY u.fullname LIMIT $offset,$limit";
for ($i = 0; $i < $nrows; $i++) {
$A = DB_fetchArray($result);
$retval .= '<tr><td><a href="' . $_CONF['site_url']
. '/users.php?mode=profile&uid=' . $A['uid'] . '">'
. $A['fullname'] . substr($A['grad_year'], -2) , '</a>'
if (!empty($A['photo']) AND $_CONF['allow_user_photo'] == 1) {
$retval .= ' <a href="' . $_CONF['site_url'] . '/users.php?mode=profile&uid=' . $A['uid'] . '"><img src="' . $_CONF['layout_url'] . '/images/smallcamera.gif" border="0" alt=""></a>';
}
$retval .= '</td><td>' . $A['username']
. '</td><td><a href="' . $_CONF['site_url']
. '/profiles.php?uid=' . $A['uid']
. '">' . $LANG04[81] .'</a></td></tr>' . LB;
7
6
Quote
All times are EST. The time is now 09:54 pm.
- Normal Topic
- Sticky Topic
- Locked Topic
- New Post
- Sticky Topic W/ New Post
- Locked Topic W/ New Post
- View Anonymous Posts
- Able to post
- Filtered HTML Allowed
- Censored Content