MyBB Community Forums

So basically I have an SQL simple select query, and a fetch_array statement, and then the results (Facebook & Yahoo ID's) are displayed in the postbit of each user's post.

Here's the problem:

For every single post by the same user, it fetches the arrays again and again. So if the same user posted 10 times on the page, it would fetch the arrays 10 times (10 for Facebook, and 10 for Yahoo). I need it to just fetch 1 array for Facebook & 1 array for Yahoo.

How can I fetch the arrays only once for each user, but still show the result in the postbit?

Here is my code:

$query = $db->simple_select("socialusers", "yahoo,facebook", "uid='".$post['uid']."'");
while ($result = $db->fetch_array($query)) {
	print_r($result); //Testing to see how many arrays it fetches
		$facebook_id = '<a href="'.$result['facebook'].'">Facebook Profile</a>';
		$yahoo_id = '<a href="'.$result['yahoo'].'">Yahoo Address</a>';
	}
}

So if a user posts 10 times on a thread page, print_r() displays 10 Arrays for Yahoo, and 10 Arrays for Facebook.

How can I reduce it to 1 query instead of 10, for the same users posts?

You need to query all data with one single query using the $pids variable in a normal thread view. This variable holds the PIDs for all post being displayed in the page. Using this you can get the posts authors data (user's facebook & yahoo data).
http://community.mybb.com/thread-159689.html
http://community.mybb.com/thread-126575-...#pid916349

The first post will get all data, subsequent posts will use that data too.

Thanks Omar G. That is the exact thing I need.

Those threads you linked to contain quite heated arguments between the users

Hi there Greg, I'm replying because of your request.

Please read my following posts:
http://community.mybb.com/thread-159689-...pid1104568
http://community.mybb.com/thread-126575-...#pid916402

Even though the code may not be fully functional (I just noticed some errors in there) because I code without testing I'm sure they will be of some help to understand how to achieve what you are looking for.

Now, if the code you share in your first post is working for you, and assuming you are hooking only at postbit the following code should be almost functional. Hopefully >_>

$post['facebook_id'] = $post['yahoo_id'] = '';

if(THIS_SCRIPT == 'showthread.php' && $mybb->get_input('mode') != 'threaded')
#if(THIS_SCRIPT == 'showthread.php' && (string)$mybb->input['mode']  != 'threaded') // mybb 1.6
{
	static $my_custom_cache = null;

	if($my_custom_cache === null)
	{
		global $pids;

		$query = $db->query("
				SELECT s.uid, s.facebook, s.yahoo
				FROM ".TABLE_PREFIX."socialusers s
				LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=s.uid)
				LEFT JOIN ".TABLE_PREFIX."posts p ON (p.uid=u.uid)
				WHERE p.{$pids}
		");

		while($social = $db->fetch_array($query))
		{
			$my_custom_cache[$row['uid']] = $row;       
		}
	}
}
else
{
	// do a normal query because there is only one post in the page anyways
	// code here should work for postbit_pm, postbit_annoucmenet hooks.
	// postbit_prev (hook while editing a post that may not yet exists) may need special handle
	// Things get complicated the more you dig into details :P
}

if(isset($my_custom_cache[$post['uid']]))
{
    $post['facebook_id'] = $my_custom_cache[$post['uid']]['facebook'];
    $post['yahoo_id'] = $my_custom_cache[$post['uid']]['yahoo'];
}
else
{
    // this post has no user/social data? Hmm..
}

Note that you may need to sanitaze the output values as needed, example:

if(isset($my_custom_cache[$post['uid']]))
{
    $post['facebook_id'] = htmlspecialchars_uni($my_custom_cache[$post['uid']]['facebook']);
    $post['yahoo_id'] = htmlspecialchars_uni($my_custom_cache[$post['uid']]['yahoo']);
}

Please don't hesinate about asking anything you don't understand, or even google it for your self. when I learned about this I didn't even knew what static was about.

I tried the code, but it replied with "Fatal error: Call to undefined method MyBB::get_input()"

Are you using MyBB 1.6? If so replace:

$mybb->get_input('mode')

With:

(string)$mybb->input['mode']

In the above code.

I'm actually brand new to this query, and I don't know what it does exactly:

$query = $db->query("
                SELECT s.uid, s.facebook, s.yahoo
                FROM ".TABLE_PREFIX."socialusers s
                LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=s.uid)
                LEFT JOIN ".TABLE_PREFIX."posts p ON (p.uid=u.uid)
                WHERE p.{$pids}
        ");

I've never learned about "s.fieldname" or "u ON (u.uid=s.uid)" or "p ON (p.uid=u.uid)". What do these lines mean?

Which part of this query deals with reducing 10 queries to 1 query? I'm stuck.

Try to see if this helps:
http://www.sitepoint.com/understanding-s...-database/

Actually I think the following will work the same way:

$query = $db->query("
 SELECT s.uid, s.facebook, s.yahoo
 FROM ".TABLE_PREFIX."socialusers s
 LEFT JOIN ".TABLE_PREFIX."posts p ON (p.uid=s.uid)
 WHERE p.{$pids}
"); 

Since the posts and socialusers table share the UID column therefor joining the users table is not necessary. I missed that probably when copied the code from my awards plugin.

Quote:I've never learned about "s.fieldname"

This is simply to associate fields with tables. Note the s at the end of _FROM ".TABLE_PREFIX."socialusers_

If the users table has a facebook column MySQL will throw an error. That is why you need to associate fields in the query with a table. If it doesn't have such a field it may ignore the error IIRC.

Take the following as examples:

$query = $db->simple_select('users', 'username', 'uid=1');
$query = $db->simple_select('users u', 'u.username', 'u.uid=1');

Both do the same, but you don't really need the last one since you are not joining any other table really.

Quote:"u ON (u.uid=s.uid)" or "p ON (p.uid=u.uid)"

The posts, users, and yours socialusers tables all share an UID key, so you need to associate fields.

$query = $db->query("
    SELECT s.uid, s.facebook, s.yahoo /* we need these fields */
    FROM ".TABLE_PREFIX."socialusers s /* from this table*/
    LEFT JOIN ".TABLE_PREFIX."posts p ON (p.uid=s.uid) /* where s.uid (socialusers.uid) belongs to this post UID*/
    WHERE p.{$pids} /* post being this list of posts, actually*/
");

The code doesn't seem to work. It only shows the result for the 1st post on the page, but not on the rest of them, regardless of the user.

Is there an easier way of doing it?

I would need to see your current code.