Very small db_query code to smcFunc

Started by shumilica, December 09, 2009, 07:35:08 PM

Previous topic - Next topic

shumilica

Hello there.
I'm trying to make a mod be usable on my smf 2.0.
I managed 90% of the work (not much), and I only have to convert this code to new smcFunc:


$result_groups = db_query("
SELECT groupName
FROM {$db_prefix}membergroups
WHERE ID_GROUP IN (" . $row['additional_groups'] . ')
ORDER BY groupName ASC', __FILE__, __LINE__);


Anyone willing to use their brilliance for 3 minutes in this code for me?
Thank you!
Today, if you're not confused it means you're not thinking clear!

Arantor

$result_groups = $smcFunc['db_query']('', '
  SELECT group_name
  FROM {db_prefix}membergroups
  WHERE id_group IN ({raw:groups})
  ORDER BY group_name ASC',
  array(
    'groups' => $row['additional_groups'],
  )
);


That said, that implies you're doing a query for every user that's applicable, which is massively inefficient. What are you trying to do overall? There's almost certainly a way that doesn't involve more than 2 queries total.

shumilica

Thank you very much
It seems that after changing this, this error shows up when trying to view somebodys profile (see below the mod):

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '27)
ORDER BY group_name ASC' at line 3
File: /public_html/forum/Sources/Load.php
Line: 1025


I'm trying to port this mod:
http://custom.simplemachines.org/mods/index.php?mod=620

It's not a big code to change. I'm refering only to the one that changes load.php, the profile.template.php i should manage... can you help? thanks!
Today, if you're not confused it means you're not thinking clear!

Arantor

Looks like the opening ( was missed in the query when you added it to your code.

shumilica

Don't understand, i just copy pasted your code as you gave it to me...

This is the hole code from to be converted:

// Begin modification - Additional membergroups on profile
// Let's start with an empty array
$user_profile[$row['ID_MEMBER']]['additionalGroups2'] = array();

// Any additional membergroups for this user?
if (isset($row['additionalGroups']) && $row['additionalGroups'] != '')
{
// There is? Great! Get the names of the groups
$result_groups = db_query("
SELECT groupName
FROM {$db_prefix}membergroups
WHERE ID_GROUP IN (" . $row['additionalGroups'] . ')
ORDER BY groupName ASC', __FILE__, __LINE__);

// Loop through all the groups...
while ($row_groups = mysql_fetch_assoc($result_groups))
// ... and add them to the user's list
$user_profile[$row['ID_MEMBER']]['additionalGroups2'][] = $row_groups['groupName'];

mysql_free_result($result_groups);
}
// End modification


And from what i know the only thing that had to be changed was the code i asked in the first place. I already changed ID_MEMBER, additionalGroups, etc. to their current name in smf2.
Today, if you're not confused it means you're not thinking clear!

Arantor

Yes, I went and looked at the mod's code, and it should have been the only thing that needed changing, and AFAIK it should have worked (as I didn't test it)

shumilica

Where did you say I have to put an opening "("?
Today, if you're not confused it means you're not thinking clear!

Arantor

At the start of ({raw:groups}) so it has a bracket in front and after - like the code I originally put.

shumilica

It has the brackets, because i just copy pasted your code....
I don't know why is that error... i searched and searched, but to no avail.

But I found another way for displaying additional membegroups in profile/display.template, but unfortunately it's still for smf1, can you help me again with recoding?


$result = db_query("
SELECT groupName
FROM {$db_prefix}membergroups
WHERE ID_GROUP IN (" . $groups . ')
', __FILE__, __LINE__);


it should be something like this:

$result = db_query("
SELECT group_name
FROM {$db_prefix}membergroups
WHERE id_group IN ({$groups})
');


but it gives me aproximately the same error:


You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '})' at line 3


What's wrong with my code?
Today, if you're not confused it means you're not thinking clear!

Arantor

In 2.0, first up db_query doesn't exist.

Secondly, the { } syntax is reserved for special uses. {$db_prefix} isn't valid, it should only ever be {db_prefix} in 2.0.

As for parameters, that has to take the form {type:variable} with the variable then being specified outside the query as I originally had.

shumilica

Ups, yes, in the file I wrote db_query, db_prefix but when i wrote it here again i pasted from the source file (for smf1) without modifying all of the code.

I don't know, I can't seem to get it working at all... i just can't....

If you want, I can donate to you 5$ trough paypal if you make this little code working in SMF2 RC2 :

http://www.simplemachines.org/community/index.php?topic=226827.msg1676636#msg1676636

what do you say?
Today, if you're not confused it means you're not thinking clear!

Arantor

I don't accept donations for fixes in the forum as a general rule, but I appreciate the offer.

* Arantor thinks for a moment.

What happens if you use this code in 2.0:
$result_groups = $smcFunc['db_query']('', '
  SELECT group_name
  FROM {db_prefix}membergroups
  WHERE id_group IN ({array_string:groups})
  ORDER BY group_name ASC',
  array(
    'groups' => explode(',', $row['additional_groups']),
  )
);

shumilica

Hmmmmmmmmmm, let me see....

so, entering load.php, copy paste, loading back to ftp...

it's taking too much time...

you know what happens?

IT WORKS! IT WORKS! IT WORKS! :o


What did you just do? Because it's working flawlessly!

Thank you very much!

One more question tough, from what you said in the beginning, you think that this query puts stress on the server and slows the forum down? If i call this only in display.template and profile.template, do you think it would have a big impact in servers performance and forums speed?

Are you sure you don't want to pm your email address to send the donation? :) Just to show my graditude because "you thought for a moment", no biggie, just a beer/drink!
Today, if you're not confused it means you're not thinking clear!

Arantor

The difference is that instead of using the raw contents of the field as returned, I forced it to be cleaned up instead, so instead of it just pushing a comma-separated field into the query directly (as you can in 1.1), I made it split it up and have it correctly.

Yes, this could get expensive to run in Display.php/Display.template.php since it's potentially a query for each user on the page, but without a complete rewrite of the entire process of gathering those, there isn't much you can do about it.

And seriously, don't worry about it - I love coding and helping everyone, so it's cool. Just enjoy the beer yourself :)

shumilica

I see. That was clever :)

After making it work, and display additional member groups on profile, I modified a little bit the code that was put to profile.template and put it where i wanted in display.template:


// Begin modification - Additional membergroups on profile
$x = 1;
// Loop through each additional group
foreach ($message['member']['additional_groups2'] as $additional_group)
{
// Add one to our counter
$x++;
// Output this membergroup
echo '
<li class="membergroup">', $additional_group, '</li>';
}
// End modification


but it just doesn't display anything :O
Do you know why?


I think this code, taken from here: http://www.simplemachines.org/community/index.php?topic=226827.msg1676636#msg1676636 work better if I want to show in display.template too:


$groups = $profile['additionalGroups'];
// additional groups
if (!empty($groups))
{
$result = db_query("
SELECT groupName
FROM {$db_prefix}membergroups
WHERE ID_GROUP IN (" . $groups . ')
', __FILE__, __LINE__);

while ($row = mysql_fetch_assoc($result))
$memberContext[$user]['additional_groups']['name'][] = $row['groupName'];
mysql_free_result($result);
}


But i'm hitting again by the problem with converting the db_query to smcFunc, wich I can't handle...
Am I asking too much? :o If yes, just ignore me....
Today, if you're not confused it means you're not thinking clear!

Arantor

Just quickly before I go to bed...

$groups = $profile['additional_groups'];
// additional groups
if (!empty($groups))
{
$result = $smcFunc['db_query']('', '
SELECT group_name
FROM {db_prefix}membergroups
WHERE id_group IN ({array_string:groups})',
array(
'groups' => explode(', ', $groups),
)
);

while ($row = $smcFunc['db_fetch_assoc']($result))
$memberContext[$user]['additional_groups']['name'][] = $row['group_name'];
$smcFunc['db_free_result']($result);
}


For performance I would not do it like this. I'd load the entire memberlist in a query at the start of Display.php, store it in $context somewhere, then pick and choose so I never did more than one extra query per thread page load.

shumilica

Sorry if I kept you late!

That worked, but just on half. I don't know if it was your modification to the database query but the mod instead of showing all additional membergroups  (as advertised) it shows only the first one.

This was the code used in the display.template:


      if (!empty($message['member']['additional_groups2']))
      echo implode('<br />', $message['member']['additional_groups2']['name']), '<br />';
Today, if you're not confused it means you're not thinking clear!

Arantor

Change additional_groups2 to additional_groups in the template.

shumilica

No, it's not that additional_groups2 it's good (because the modder, if you take a look at the link i put above, created additional_groups without knowing that this would be the next naming of additionalGroups in SMF2, so i modified in the code you've gave me the 'additionalGroup' to 'additional_group' and 'additional_group' to 'additional_group2') , if i put without 2 it won't show up anything, if i put 2 it shows up the first additional group, but only the first, even tough the user is a member of more than one.

It's something with how the output of the database is beeing shown, or somethin.... i think (as much as I can...)...
Today, if you're not confused it means you're not thinking clear!

Arantor

So have you modified my code or not?

The code edit I posted (all of it) pushes the additional groups into $memberContext[$user]['additional_groups']['name'] as an array.

shumilica

So, in Load.php is this one:
Code (Load.php) Select

$groups = $profile['additional_groups'];

// additional groups modification
if (!empty($groups))
{
$result = $smcFunc['db_query']('', '
SELECT group_name
FROM {db_prefix}membergroups
WHERE id_group IN ({array_string:groups})',
array(
'groups' => explode(', ', $groups),
)
);

while ($row = mysql_fetch_assoc($result))
$memberContext[$user]['additional_groups']['name'][] = $row['group_name'];
mysql_free_result($result);
}


And in display.template.php is now like this:


      if (!empty($message['member']['additional_groups']))
      echo implode('<br />', $message['member']['additional_groups']['name']), '<br />';


It's now working without the 2 ( i forgot about the $groups = $profile['additional_groups']; ), but is STILL showing only the first additional group...
Today, if you're not confused it means you're not thinking clear!

Arantor

And how many should there be (excluding primary group and post count group) ?

shumilica

all the additional groups they are in (excluding the primary group, and the post count group, wich are shown perfectly). If they have 2-3-4-5 additional groups that should be shown. In this way it only shows the first additional group found (it seems to be in alfabetical order :-? don't know for sure)
Today, if you're not confused it means you're not thinking clear!

Arantor


shumilica

Today, if you're not confused it means you're not thinking clear!

Arantor

Please show me an example of a user's profile and a thread they posted in, so I can take a look.

shumilica

Ok, but my bord is not in english (plus the link is to a test forum):
http://fcrp.ro/forumdetest/index.php?topic=10072.msg112077#msg112077 (username Adeela is part of at least 3 additional groups: 'tip:universitar zi', 'anul ii', 'grupa 1', 'grupa 2' etc.) but in posts it only show the 'tip:universitar zi', and it shows it first because when i created the group i've puted some spaces in the beggining so that it will show up above the groups wich started with 'a' (i told you this to explain why i said earlier in the topic that it sorts alfabetical, and only the first alfabetical addtional group is shown).
Today, if you're not confused it means you're not thinking clear!

Arantor

So what primary group does that user have, then? (That's why I wanted to see profile too)

shumilica

That example hadn't any primary membgroup, if you look again, i've put the username to a primary membergroup (Copil Inalt). Still no show (i checked with other examples).
Today, if you're not confused it means you're not thinking clear!

Arantor

It might help if you used the entire code segment I posted. mysql_fetch_assoc should not be used in 2.0 by mods.

shumilica

wow, i didn't even notice that you've changed that code too. I changed it now, but still, the same...
Today, if you're not confused it means you're not thinking clear!

Arantor

The only reason I think of is that the [] is missing in the declaration.

shumilica

Today, if you're not confused it means you're not thinking clear!

Arantor

$memberContext[$user]['additional_groups']['name'][] = $row['group_name'];

They're there in that code excerpt.

shumilica

they are in my load.php too...
So, i think this solution just doesn't work for smf2... :(
Today, if you're not confused it means you're not thinking clear!

shumilica

Hmmm, if this second solution doesn't work, the first one, that prooved working in the profile.template, do you know how to make it work in the display.template too?


// Begin modification - Additional membergroups on profile
$x = 1;
// Loop through each additional group
foreach ($message['member']['additional_groups2'] as $additional_group)
{
// Add one to our counter
$x++;
// Output this membergroup
echo '
<li class="membergroup">', $additional_group, '</li>';
}
// End modification
Today, if you're not confused it means you're not thinking clear!

Arantor

Well, assuming Display.php loads it in to $message['member']['additional_groups'] as before...


// Begin modification - Additional membergroups on profile
$x = 1;
// Loop through each additional group
foreach ($message['member']['additional_groups'] as $additional_group)
{
// Add one to our counter
$x++;
// Output this membergroup
echo '
', $additional_group, '<br />';
}
// End modification

Advertisement: