Forums

Unfortunately no one can be told what FluxBB is - you have to see it for yourself.

You are not logged in.

#1 2008-12-05 12:35:09

twohawks
Member
From: Stateline, NV USA
Registered: 2008-05-11
Posts: 135

SQL Query Help Needed - UserList Related...

Hi All,
I know a lot of us need to keep hiking 1.2 up a little.  Practically speaking many of us won't be able to migrate a complex BB over for some time, even if we were showering in 1.3 months ago.    I have run into my userbase screaming for upgrades, and I have to deliver.....

Just to be clear - I ain't complaining - I am so happy with FluxBB1.2.20.  Its like a good old Ford Truck (or a Scout 4WD - my fav), takes a lickin' and keeps on keepin on... and I am very grateful to you coders, and support your hard work.

---------------------------------------------------------------------------------------------------------------
PURPOSE HERE: Obtain some help sussing a decent logical approach to a needed query.
---------------------------------------------------------------------------------------------------------------
I have been working on the multigroup mod from noof at punres... got it working tamely now, and making some improvements, including integration with the SubForum Mod ;^)

***I am focussed right now on the userlist.php file with consideration to where the userslist is displaying the user's group Title (I'll refer to that as the title-cell for now).

With the mod installed I want to have this "title-cell" present a comma delimited list of 'g.group_user_titles' the user is a member of.
I have somethings worked out in that direction, but I could use a little help.

SPECIFICS
***I am looking for some pointers on
- how to get groups:g_user_title
- upon matching a custom value in the users:membergroupids
    (its a new column - lets forget for the moment it holds an array if need be - until and unless I get some other basics down)
- where the value in users:membergroupids matches group:g_id.

HERE IS THE CODE I AM TWEAKING..............
1) In userlist.php ~LN 191 under 'grab the users'...........
is this slightly modified Select Statement:

SELECT u.id, u.username, u.title, u.membergroupids, u.num_posts, u.registered, g.g_id, g.g_user_title 
FROM '.$db->prefix.'users AS u LEFT JOIN '.$db->prefix.'groups AS g ON g.g_id=u.group_id 
WHERE u.id>1'.(!empty($where_sql) ? ' AND '.implode(' AND ', $where_sql) : '').' 
ORDER BY '.$sort_by.' '.$sort_dir.', u.id ASC LIMIT '.$start_from.', 50

All that's added right now is the reference for u.membergroupids.
From my limited experience so far I presume one cannot really pull another obtuse correlation out of this statment, for instance...
...in non-technical language...
AND SELECT g.g_user_title WHERE u.membergroupids for this u.id MATCHES g.g_id
AND JOIN THAT to the same ROW AS is currently being assembled for that user.

??Does this make sense?

2a) A LITTLE MORE....
Just below the queries containing the referenced SELECT statement(s), there is this code...

if ($db->num_rows($result))
{
    while ($user_data = $db->fetch_assoc($result))
    {
        echo "memberGroupIds = ".$user_data['membergroupids']."<br />\n";  //debug
        $user_title_field = get_title($user_data);

2b) THEN if you open include/functions.php, and under get_title($user), at ~ln500 , if you adjust the code there as follows, you will be able to observe the addition of the multigroupids values (numbers) next to the main g.g_user_title...

####### In include/functions.php, Find at about ln 510 ####################
###
    else if ($user['g_user_title'] != '')
        $user_title = pun_htmlspecialchars($user['g_user_title'].$user['membergroupids']);
###
###### REPLACE WITH #######
    else if ($user['g_user_title'] != '')
        $user_title = pun_htmlspecialchars(   $user['g_user_title'].(!is_null($user['membergroupids'])&&(!$user['membergroupids']==0)  ? ', '.$user['membergroupids'] : ''   ) );

================================================
From what I have wrestled with so far (and my brain is a bit overworked, it could be the answer is staring me right in the face - as usually!),
   I am thinking I may need to prepare a second query and nestle it in just above that following while loop in #2a above, and then catch the value in the while loop process. 
   And then when I got that figured out for, say, as if multigroupids was a single value...  then I may be ready to figure out how to properly iterate the array for the values and match things up for the desired result.

I am looking for some suggestions for logic as to how to tacl;e this problem.  THANKS.

----------------------------------------------------
I know, I know... its a messy and difficult mod (lots of edits), but its not so poorly thought out really (kudos to noop and cybmat for taking the tough stab))
...but I did succeed in cleaning it up and tweaking it toward some more accomodating functionality (I have listed this at punres here: http://www.punres.net/viewtopic.php?id=150), and it seems to be testing out really nicely at the present time, though I need to do more testing.

Any help and direction would be greatly apreciated, thanks.
Cheers, HTH

Last edited by twohawks (2008-12-05 19:46:29)


TwoHawks
Love is the Function.
No Form is the Tool.

Offline

#2 2008-12-05 21:14:25

twohawks
Member
From: Stateline, NV USA
Registered: 2008-05-11
Posts: 135

Re: SQL Query Help Needed - UserList Related...

Well so far I am thinking a second query, something like....

$result2 = $db->query('Select u.id, u.membergroupids, g.g_id, g.g_user_title 
FROM '.$db->prefix.'users AS u 
LEFT JOIN '.$db->prefix.'groups AS g ON g.g_id=u.membergroupids 
...blah blah...
 

is going to be needed.

You know, you work 24 hours straight, two days in a row, and.. well, you know how it is.

If the above logic seems essentially good enough, say as a second query to work from, then all I need to do is sort out  iterating the array values for u.membergroupids during each row/recordset pass, which I think will be easy to do.

I guess I was thinking, jeez, I already have all the data from the initial query, couldn't I just use that, but it would seem that the existing query would not allow re-correlating the data in a different way in this case... because the new correlation is contrary to the original.
Does this make sense? Did I get that right?

Anyone tracking this please feel free to hit me with a hammer - you know, don't hesitate to point out stupidity, ignorance, the obvious, you get the idea.

Last edited by twohawks (2008-12-05 21:19:34)


TwoHawks
Love is the Function.
No Form is the Tool.

Offline

#3 2008-12-06 10:12:48

twohawks
Member
From: Stateline, NV USA
Registered: 2008-05-11
Posts: 135

Re: SQL Query Help Needed - UserList Related...

jeez, TwoHawks, get some freaking sleep!....

You (me) were a bit off on the query statement, and almost close on the idea of injecting one above the while loop.
The first problem is obvious (sleep on it and then realize you just need to get the group data there)
...and the second issue has to do with you will need to freshly query the table while the current rs (recordset/row) is being iterated in the while loop.

Okay, so I worked it out.  If anyone has passed by, would you please look at the code and comment on it for the sake/hope of improvement if need be... there is so much I have not yet learned.

(oops..., copy/paste error... updated)

if ($db->num_rows($result))
{
    while ($user_data = $db->fetch_assoc($result))
    {
        //reset the multiusergroup_titles variables
        $mugttls = '';
        $mugttls_comp = '';
        $mgrps = split(',', $user_data['membergroupids']);  //split the values returned from the cell into an array
        $count = 1;
        foreach($mgrps as $mgrp) {
            /*now query the groups table, pulling the g_user_title 
              where g_id matches the current number being passed from membergroupids for this rs */
            if((int)$mgrp !=0) {
                $result2 = $db->query('SELECT g_id, g_user_title FROM '.$db->prefix.'groups WHERE g_id = '.$mgrp.'') or error('Unable to fetch user list', __FILE__, __LINE__, $db->error());
                $cur_mgusr_title = $db->fetch_assoc($result2);

                /* gather the result into a variable. It will be passed to the concatenating if statement 
                   at completion of iterating this table cell for this rowset */
                $mugttls = $cur_mgusr_title['g_user_title'];
                $count++;
            }
            //this if statement concantonates the result of this array parser (for/each statment) for the cell value
            if ($mugttls != NULL)
                $mugttls_comp = $mugttls_comp.", ".$mugttls ;
        }

        // now add the result to the user_title_field variable  
        $user_title_field = get_title($user_data).$mugttls_comp;
        //injected code ends here.  A concatenated string of all groups this user is member of will appear in the userlist.

So how did I do?
Your expert criticisms and guidance is greatly apreciated.
Cheers,

Last edited by twohawks (2008-12-07 01:21:15)


TwoHawks
Love is the Function.
No Form is the Tool.

Offline

Board footer

Powered by FluxBB