Topic: db_query function problem

[The Wizard]

This code works and will display the contents of the shop_Dog in this case thats - dog6. The problem I'm having is that it displays dog6 in every post for all users and not the posts of the user who owns the dog. So how can I make this work?   

Code: [Select]

function onUser () {
    global $smcFunc;

    $dog = '';

    $request = $smcFunc['db_query']('', '
            SELECT shop_Dog
            FROM {db_prefix}members',
array(
'shop_Dog' => $dog,
)
);

$temp = $smcFunc['db_fetch_assoc']($request);
echo $temp["shop_Dog"]; 

}

[Sorck]

It's simply searching for every user in the database... therefore will return a lot of results of which the first will be that of the lowest id user (usually 1, the admin/creator)

You need a WHERE id_member = {int:id_member} in your query (and you'll have to supply the id_member in your array).

Also, you have 'shop_Dog' in your array - what's the point of this? You're not injecting it into your query.

BTW, use lower case column names so it works with all of SMF's database options. I've not corrected that below as it'd break your query. Change it via PHPMyAdmin and then change it to shop_dog (I don't know why it was shop_Dog in the first place as that's mixing two different styles of naming).

Code: [Select]

function onUser () {
    global $smcFunc;
$id_member = 1; // not sure where this is running. if you just want the current viewers 'dog' then use $user_info['id']
    $dog = '';

    $request = $smcFunc['db_query']('', '
            SELECT shop_Dog
            FROM {db_prefix}members
            WHERE id_member = {int:id_member}
            LIMIT 0,1', // it's good practice to limit your query length
array(
'id_member' => $id_member,
)
);

$temp = $smcFunc['db_fetch_assoc']($request);
echo $temp["shop_Dog"]; 

}

[The Wizard]

When I run the code Sorck posted as is with no changes it works, but I still have the same problem it posts "dog6" in everybodys posts and not just in the posts of the dog owner.

When I try limiting the function to the curent user I get the following error:

An Error Has Occurred! The database value you're trying to insert does not exist: id_member

Any ideas why this is happening?

Code: [Select]

function onUser () {
    global $smcFunc;
//$id_member = 1; // not sure where this is running. if you just want the current viewers 'dog' then use $user_info['id']
   
$dog = $user_info['id'];    
   
   //$dog = '';

     $request = $smcFunc['db_query']('', '
             SELECT shop_Dog
             FROM {db_prefix}members
             WHERE id_member = {int:id_member}
             LIMIT 0,1', // it's good practice to limit your query length
array(
'id_member' => $id_member,
)
);

$temp = $smcFunc['db_fetch_assoc']($request);
echo $temp["shop_Dog"]; 

}

[vbgamer45]

Are you calling onUser inside the post display area is a function if so you should change it to something like

Code: [Select]

unction onUser ($id_member) {
    global $smcFunc;
     $request = $smcFunc['db_query']('', '
             SELECT shop_Dog
             FROM {db_prefix}members
             WHERE id_member = {int:id_member}
             LIMIT 1', // it's good practice to limit your query length
array(
'id_member' => $id_member,
)
);

$temp = $smcFunc['db_fetch_assoc']($request);
echo $temp["shop_Dog"]; 
}
Then call the function in post such as


onUser($user_info['id']);


But replace $user_info['id'] with the id of the member from the post

[The Wizard]

hopefully this will make things more clear:

I trying to create a function that will check the shop_Dog row in the database and if the user has any data in that table the data then gets displayed under that users karma only.

Wiz

[The Wizard]

I think I'm close on this.

Code: [Select]

function onUser () {
    global $smcFunc, $user_info;

$request = $smcFunc['db_query']('', '
                         SELECT shop_Dog
                         FROM {db_prefix}members
                     WHERE id_member = {int:member}
                         LIMIT 0,1', // it's good practice to limit your query length
                  array(
                         'member' => $user_info['id'],
                    )
                        );

// if user has data in shop_Dog show it on their posts only.

if (isset($message['member']['shop_Dog']))
{
    $temp = $smcFunc['db_fetch_assoc']($request);
echo $temp["shop_Dog"];
}
}

[Sorck]

That will only show the 'dog' of the current viewing user ($user_info['id'] is the current user's ID, not that of the post in question).

The query above could result in an extra 25 queries a page (if you're set to 25 posts per page) all in templates I would assume. It might be more efficient to add it to the query in Display.php to include 'shop_Dog' in the FROM statement.

You might then need to make sure it's put in the $message['member'] array, again, in Display.php

EDIT: is this in the user's profile or under forum posts?

[The Wizard]

Sorry I should have posted all my work to this point to give you all I better understanding of the problem
but I was up very late last night.

So here is what I have done so far:

just before the

Code: [Select]

?> in Load.php I have the following function:

Code: [Select]

//BEGIN SMFShop Item Cute Dog Image Version 0.1
function onUser ($id_member) {

    global $db_prefix, $smcFunc, $user_info, $boardurl;

      $request = $smcFunc['db_query']('', '
              SELECT shop_Dog
              FROM {db_prefix}members
              WHERE id_member = {int:id_member}',
              LIMIT 1', // it's good practice to limit your query length
array(
        'id_member' => $id_member,
)
);

$temp = $smcFunc['db_fetch_assoc']($request);
if($temp['shop_Dog'] !== "") {
                echo "Cute Dog: <img src='{$boardurl}/Sources/shop/dog_images/{$temp['shop_Dog']}'><br>";
                }

}

//END SMFShop Item Cute Dog Image Version 0.1
Also in Load.php in the $memberContext[$user] = array after this code:

Code: [Select]

'gender' => array(
'name' => $gendertxt,
'image' => !empty($profile['gender']) ? '<img class="gender" src="' . $settings['images_url'] . '/' . ($profile['gender'] == 1 ? 'Male' : 'Female') . '.gif" alt="' . $gendertxt . '" />' : ''
),
I added the following code:

Code: [Select]

'dog' => onUser($profile['id_member']),
Then in the Display.template.php after this code:

Code: [Select]

// Show the member's gender icon?
if (!empty($settings['show_gender']) && $message['member']['gender']['image'] != '' && !isset($context['disabled_fields']['gender']))
echo '
<li class="gender">', $txt['gender'], ': ', $message['member']['gender']['image'], '</li>';
                     
I added the following code:

Code: [Select]

// Show SMFShop Item Cute Dog Image Version 0.1
if (!empty($settings['show_gender']) && $message['member']['dog'] != '')
     echo'
                                <li class="im_icons">', $message['member']['dog'], '</li>';
I do get the result but not the way I want it displayed. See picture to understand.

[Sorck]

The function onUser() doesn't return what's needed for the $memberContext[$user] array does it not? It only echo's out your data so therefore $memberContext[$user]['dog'] should equal null.

Or at least that appears to be the case. Making it return rather than echo'ing the string might be better?

[The Wizard]

Hello:

I agree with this and came to the conclusion that the coded approach is the wrong way to go. Is there another simpler way to read data from the database in SMF?

Wiz