Welcome to Geeklog, Anonymous Tuesday, April 30 2024 @ 02:34 pm EDT
Geeklog Forums
SQL - How to get the username of a comment
Status: offline
::Ben
Forum User
Full Member
Registered: 01/14/05
Posts: 1569
Location:la rochelle, France
I'm new to sql and I would like to modify this query to get the username of a comment.
I know the {$_TABLES['comments']}.uid but how can I found the username?
if( !COM_isAnonUser() )
{
$stwhere .= "({$_TABLES['stories']}.owner_id IS NOT NULL AND {$_TABLES['stories']}.perm_owner IS NOT NULL) OR ";
$stwhere .= "({$_TABLES['stories']}.group_id IS NOT NULL AND {$_TABLES['stories']}.perm_group IS NOT NULL) OR ";
$stwhere .= "({$_TABLES['stories']}.perm_members IS NOT NULL)";
}
else
{
$stwhere .= "({$_TABLES['stories']}.perm_anon IS NOT NULL)";
}
$sql = "SELECT DISTINCT COUNT(*) AS dups, type, {$_TABLES['stories']}.title, {$_TABLES['comments']}.comment,{$_TABLES['comments']}.uid, {$_TABLES['stories']}.sid, max({$_TABLES['comments']}.date) AS lastdate FROM {$_TABLES['comments']} LEFT JOIN {$_TABLES['stories']} ON (({$_TABLES['stories']}.sid = {$_TABLES['comments']}.sid)" . COM_getPermSQL( 'AND', 0, 2, $_TABLES['stories'] ) . " AND ({$_TABLES['stories']}.draft_flag = 0) AND ({$_TABLES['stories']}.commentcode >= 0)" . $topicsql . COM_getLangSQL( 'sid', 'AND', $_TABLES['stories'] ) . ") WHERE ((({$stwhere}))) GROUP BY {$_TABLES['comments']}.sid,type, {$_TABLES['comments']}.comment, {$_TABLES['stories']}.title, {$_TABLES['stories']}.sid ORDER BY 5 DESC LIMIT 15";
$result = DB_query( $sql );
$nrows = DB_numRows( $result );
This code is from lib-common.php
::Ben
I'm available to customise your themes or plugins for your Geeklog CMS
I know the {$_TABLES['comments']}.uid but how can I found the username?
Text Formatted Code
$stwhere = '';if( !COM_isAnonUser() )
{
$stwhere .= "({$_TABLES['stories']}.owner_id IS NOT NULL AND {$_TABLES['stories']}.perm_owner IS NOT NULL) OR ";
$stwhere .= "({$_TABLES['stories']}.group_id IS NOT NULL AND {$_TABLES['stories']}.perm_group IS NOT NULL) OR ";
$stwhere .= "({$_TABLES['stories']}.perm_members IS NOT NULL)";
}
else
{
$stwhere .= "({$_TABLES['stories']}.perm_anon IS NOT NULL)";
}
$sql = "SELECT DISTINCT COUNT(*) AS dups, type, {$_TABLES['stories']}.title, {$_TABLES['comments']}.comment,{$_TABLES['comments']}.uid, {$_TABLES['stories']}.sid, max({$_TABLES['comments']}.date) AS lastdate FROM {$_TABLES['comments']} LEFT JOIN {$_TABLES['stories']} ON (({$_TABLES['stories']}.sid = {$_TABLES['comments']}.sid)" . COM_getPermSQL( 'AND', 0, 2, $_TABLES['stories'] ) . " AND ({$_TABLES['stories']}.draft_flag = 0) AND ({$_TABLES['stories']}.commentcode >= 0)" . $topicsql . COM_getLangSQL( 'sid', 'AND', $_TABLES['stories'] ) . ") WHERE ((({$stwhere}))) GROUP BY {$_TABLES['comments']}.sid,type, {$_TABLES['comments']}.comment, {$_TABLES['stories']}.title, {$_TABLES['stories']}.sid ORDER BY 5 DESC LIMIT 15";
$result = DB_query( $sql );
$nrows = DB_numRows( $result );
This code is from lib-common.php
::Ben
I'm available to customise your themes or plugins for your Geeklog CMS
3
2
Quote
Status: offline
jmucchiello
Forum User
Full Member
Registered: 08/29/05
Posts: 985
Quote by: cordiste
if( !COM_isAnonUser() )
{
$stwhere .= "({$_TABLES['stories']}.owner_id IS NOT NULL AND {$_TABLES['stories']}.perm_owner IS NOT NULL) OR ";
$stwhere .= "({$_TABLES['stories']}.group_id IS NOT NULL AND {$_TABLES['stories']}.perm_group IS NOT NULL) OR ";
$stwhere .= "({$_TABLES['stories']}.perm_members IS NOT NULL)";
}
else
{
$stwhere .= "({$_TABLES['stories']}.perm_anon IS NOT NULL)";
}
First, this code is not needed. Your call to COM_getPermSQL does this. And, you should not include anything in your ON clause that is not involved in joining of the tables. Since st.sid = c.sid covers the JOIN, everything else should be in the WHERE clause.
Since you are not doing an outer join, I prefer putting all tables on the FROM statement. (This also eliminates the need for an ON clause.) Also, SQL has table aliases, use them. I eliminated the parentheses as I find them to be clutter.
$sql = "SELECT DISTINCT COUNT(*) AS dups, type, st.title as title, c.comment as comment, c.uid as uid, st.sid as sid, max(c.date) AS lastdate "
. "FROM {$_TABLES['comments']} c, {$_TABLES['stories']} st "
. "WHERE st.sid = c.sid AND st.draft_flag = 0 AND st.commentcode >= 0"
. COM_getPermSQL('AND', 0, 2,'st') // notice we can use st here.
. $topicsql
. COM_getLangSQL('sid', 'AND','st')
. "GROUP BY c.sid,type, c.comment, st.title, st.sid, c.uid "
. "ORDER BY 5 DESC LIMIT 15";
Since you are doing an aggregate, it is a bit ugly to add the username here. Use COM_getDisplayName from lib-common.php. If you look it up, you will see it takes 1 or 5 parameters. So you either pass it uid or you need to join the user table above and select username, remote_user, remote_service, etc. And that just is a mess considering how involved your sql already is.
Text Formatted Code
$stwhere = '';if( !COM_isAnonUser() )
{
$stwhere .= "({$_TABLES['stories']}.owner_id IS NOT NULL AND {$_TABLES['stories']}.perm_owner IS NOT NULL) OR ";
$stwhere .= "({$_TABLES['stories']}.group_id IS NOT NULL AND {$_TABLES['stories']}.perm_group IS NOT NULL) OR ";
$stwhere .= "({$_TABLES['stories']}.perm_members IS NOT NULL)";
}
else
{
$stwhere .= "({$_TABLES['stories']}.perm_anon IS NOT NULL)";
}
First, this code is not needed. Your call to COM_getPermSQL does this. And, you should not include anything in your ON clause that is not involved in joining of the tables. Since st.sid = c.sid covers the JOIN, everything else should be in the WHERE clause.
Since you are not doing an outer join, I prefer putting all tables on the FROM statement. (This also eliminates the need for an ON clause.) Also, SQL has table aliases, use them. I eliminated the parentheses as I find them to be clutter.
Text Formatted Code
$sql = "SELECT DISTINCT COUNT(*) AS dups, type, st.title as title, c.comment as comment, c.uid as uid, st.sid as sid, max(c.date) AS lastdate "
. "FROM {$_TABLES['comments']} c, {$_TABLES['stories']} st "
. "WHERE st.sid = c.sid AND st.draft_flag = 0 AND st.commentcode >= 0"
. COM_getPermSQL('AND', 0, 2,'st') // notice we can use st here.
. $topicsql
. COM_getLangSQL('sid', 'AND','st')
. "GROUP BY c.sid,type, c.comment, st.title, st.sid, c.uid "
. "ORDER BY 5 DESC LIMIT 15";
Since you are doing an aggregate, it is a bit ugly to add the username here. Use COM_getDisplayName from lib-common.php. If you look it up, you will see it takes 1 or 5 parameters. So you either pass it uid or you need to join the user table above and select username, remote_user, remote_service, etc. And that just is a mess considering how involved your sql already is.
4
3
Quote
Status: offline
::Ben
Forum User
Full Member
Registered: 01/14/05
Posts: 1569
Location:la rochelle, France
Hi,
Yes I need to learn a lot
and read the lib-common.
COM_getDisplayName give me exactly what I'm looking for.
Thank you very much Joe.
::Ben
Edit : The above SQL query need a space at the end of that line
. "WHERE st.sid = c.sid AND st.draft_flag = 0 AND st.commentcode >= 0 "
I'm available to customise your themes or plugins for your Geeklog CMS
Yes I need to learn a lot
and read the lib-common.COM_getDisplayName give me exactly what I'm looking for.
Thank you very much Joe.
::Ben
Edit : The above SQL query need a space at the end of that line
Text Formatted Code
. "WHERE st.sid = c.sid AND st.draft_flag = 0 AND st.commentcode >= 0 "
I'm available to customise your themes or plugins for your Geeklog CMS
3
3
Quote
Status: offline
jmucchiello
Forum User
Full Member
Registered: 08/29/05
Posts: 985
Quote by: cordiste
There was only one typo? Amazing. Edit : The above SQL query need a space at the end of that line
Glad I could help.
4
3
Quote
Status: offline
Dirk
Site Admin
Admin
Registered: 01/12/02
Posts: 13073
Location:Stuttgart, Germany
Quote by: cordiste
COM_getDisplayName give me exactly what I'm looking for.
Just wanted to point out that the function is inefficient when you need to get a lot of usernames at once as it's doing an SQL request every time. It's fine for one or a few usernames.
bye, Dirk
2
4
Quote
Status: offline
jmucchiello
Forum User
Full Member
Registered: 08/29/05
Posts: 985
Yeah, put he's already joining two tables and doing aggregation on the result. Adding the user table can't be a good thing. Besides he has a limit 15 on the main select so the inefficiency only happens 15 times. Of course, when I gave that advice, I didn't realize he was making a block. In a block, you are correct, adding the user table is probably "better".
Of course, when I gave that advice, I didn't realize he was making a block. In a block, you are correct, adding the user table is probably "better".
4
2
Quote
All times are EDT. The time is now 02:34 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