DB_Query Displaying Problem

[frostalf]

Alright I have a custom page and tab made, I have managed to successfully do that. Now what I need this page to do is to grab information from the database and display it. The code I have displayed is just something I have done to get the hang of some of the functions SMF has for grabbing information from the Database. But I can not for the life of me seem to get the result I queried to not display at all no matter what I do. I have tried globalizing the name of the result I have tried calling the function into the other function etc. I may be missing something but I hope someone here can point me in the right direction or at least show me where I went wrong. Also, I am having trouble grasping the array function that is part of the DB_Query for SMF.  For now I left it as a blank array as I do not know how to use that. I am fairly good with PHP and MySQL in General. Just not all that familiar customizing SMF on this level lol.

using SMF 2.0.2 and I have included the error message as well

Code Select Expand


<?php

if (!defined('SMF'))
die('Hacking attempt...');

function Test() {
   global $context, $scripturl, $smcFunc;
   $context['page_title'] = 'Name of Page';
   $result = $smcFunc['db_query']('', 'SELECT member_name FROM {db_prefix}members', array());

   while ($row = $smcFunc['db_fetch_assoc']($result)){
       
       $mname = $row['member_name'];
       return $mname;
   }
  $smcFunc['db_free_result']($result);

}
   function template_main() {  
       
   echo "<center><b>" . $mname . "</b></center>";
}
?>

Error Message

Code Select Expand


http://players4play.com/index.php?action=test
8: Undefined variable: mname
File: /players4play/Sources/test.php
Line: 28

[frostalf]

Would appreciate some help with this. Still unsure as to how, the variable is undefined after it has been defined etc. I am trying to display Stuff from the database as that is what my project requires. I used the members database to mess around with SMF's database query functions before I actually tried to make anything to ensure I knew what I was doing. I can't put the query in the second function as this causes the custom page not to load correctly and as I read somewhere esle its generally not a good idea to put it in the template function anyways. So, I am stuck with how do I get my resulting query to display on the page? Everytime I try it just results in just not showing up. Anyways I will continue to mess around with it and hope to either find the solution or someone could help me please.

[Joker™]

Most of the people are volunteer here, so please don't bump the topics within 24 hrs.


Try this code

Code Select Expand

<?php

if (!defined('SMF'))
die('Hacking attempt...');

function Test() {
   global $context, $scripturl, $smcFunc;
   $context['page_title'] = 'Name of Page';
   $result = $smcFunc['db_query']('', 'SELECT member_name FROM {db_prefix}members', array());

   while ($row = $smcFunc['db_fetch_assoc']($result)){
       
       $context['mname'] = $row['member_name'];
       return $context['mname'];
   }
  $smcFunc['db_free_result']($result);

}
   function template_main() {  
   global $context;
   echo "<center><b>" . $context['mname'] . "</b></center>";
}
?>

Your code has few issues. You need to return the value on a global array, so that other function can pick the value from same global array.

[frostalf]

sorry about the double posting within 24 hours. Thank you for your reply and help. The alterations you supplied work. The new problem I have now, is it does grab information, but now, I am having problems how to grab all the information. The current code is only grabbing one name. I assume I am going to have to alter the function that grabs the information to grab all the information instead of just one name. I do really appreciate the help as I am one step further in trying to get this to work. Also by any chance could someone explain to me what the array does in the db_query function does?

[Joker™]

Let me show you an example first.

Code Select Expand

<?php

if (!defined('SMF'))
die('Hacking attempt...');

function Test() {
   global $context, $scripturl, $smcFunc, $user_info;

   $context['page_title'] = 'Name of Page';
   $posts_count = 0;

   $result = $smcFunc['db_query']('', '
       SELECT id_member, member_name, posts
       FROM {db_prefix}members
       WHERE posts > {int:post_count}',
       array(
           'post_count' => $posts_count
       )
   );

   while ($row = $smcFunc['db_fetch_assoc']($result)) {
       $context['cust_info'][$row['id_member']] = array(
           'id' => $row['id_member'],
           'name' => $row['member_name'],
           'posts' => $row['posts'],            
       );
       return $context['cust_info'];
   }
  $smcFunc['db_free_result']($result);

}

function template_main() {  
   global $context;

   foreach($context['cust_info'] as $val){
       echo $val['id'] . '<br />';
       echo $val['name'] . '<br />';
       echo $val['posts'] . '<br />';
   }
}

?>If you have any further queries, please don't hesitate in posting them .

[frostalf]

Ok, see you have that array what I am having problem grasping is the part of array('post count' = > $posts_count)      From the code it does not make sense of why the array is there if you can just use a query to select people with 0 post count? I think the reason I am having hard time understanding the array part is because I don't understand its purpose or function knowing in a normal MySQL query its not needed. and the Where part is how do you know to use {int:post_count}   sorry just trying to learn the SMF functions. I know how to grab all the information I want using the Default MySQL PHP functions   but I prefer to use the SMF functions so it does not present new security problems to the website I am making and it will eliminate the part of making Custom MySQL functions if SMF already has some.  And as for the last part of the code you presented Thank you think that will resolve the problem I was facing for displaying all the usernames as I now remember. Thank you for your reply. I am familiar with PHP but I am not a pro at it but further explanation would be appreciated.

[Joker™]

Lets start

Take an overview of the function over here
- smf_db_query() in Sources/Subs-Db-mysql.php

$db_values holds the values sent via array. The query is cleaned separately. Moreover by describing the parameter like {int:id_member} in query, tells the function that the value should be an integer type in array and not a char etc type.


Also see smf_db_replacement__callback() in the same file, to know how array has been verified.

[emanuele]

http://wiki.simplemachines.org/smf/db_query

[Joker™]

Grrrr, completely forgot about the wiki .

[frostalf]

Well the wiki did not really explain the array too well for me to really understand also, your last code Joker. I tried it and it works, but the same problem exists. It only displays one person as opposed to all the names. And that is what I need, is for all the information to display. Thought maybe I could use the PHP foreach but atlas to no avail it still does not work. I am not quite sure why its not working but $context would still be an array for the other function so that is why I am not understanding why the foreach is not going through and displaying all the information and the only thing I can figure is the query is not grabbing all the information like I want it to. Is there way I could check to see what the query is actually grabbing? Also, thanks for explaining the array. I think the wiki should explain further what that array is there for and how to properly use it or at the very least explain in there what each part of the code does.

[emanuele]

Grrrr, completely forgot about the wiki .

I remember it too well! (and sorry to jump in in the middle of the discussion )

Well the wiki did not really explain the array too well for me to really understand

Well, it doesn't because arrays are php "basics".

Is there way I could check to see what the query is actually grabbing?

Run a query from phpmyadmin for example.
The equivalent of the above query is:

Code Select Expand


        SELECT id_member, member_name, posts
        FROM {db_prefix}members
        WHERE posts > 0


But the reason is here:

Code Select Expand

    while ($row = $smcFunc['db_fetch_assoc']($result)) {
        $context['cust_info'][$row['id_member']] = array(
            'id' => $row['id_member'],
            'name' => $row['member_name'],
            'posts' => $row['posts'],           
        );
        return $context['cust_info'];
    }

you are returning the data in the middle of the loop, that means after the first retrieval the loop is interrupted and the execution proceed with the rest.

That code should simply be:

Code Select Expand

    while ($row = $smcFunc['db_fetch_assoc']($result)) {
        $context['cust_info'][$row['id_member']] = array(
            'id' => $row['id_member'],
            'name' => $row['member_name'],
            'posts' => $row['posts'],           
        );
    }

Also, thanks for explaining the array. I think the wiki should explain further what that array is there for and how to properly use it or at the very least explain in there what each part of the code does.

Sorry, but the wiki is not intended to be a php tutorial, the text on the wiki is meant to explain how the specific internal SMF functions works. There are tons of php tutorials out there, there is also a board called: Parham's PHP Tutorials that has lessons and examples about php.
And there is this board and Joker™ (and many others) that may help explaining things.

[frostalf]

Thank you emanuele. I have resolved the issue of it going through the entire database. Also, I know the wiki is not a PHP tutorial and I know how arrays work. I was just having trouble of figuring its purpose in the query and the wiki did not explain its purpose very well or explain how it was used in the example. The only reason the array confused me is because  using the MySQL PHP query function there is no array you add onto the query. Anyways, thanks to you both I have resolved my problems for now. If there is any more trouble I will be sure to make a new topic of it     Also, how do I know when the problem I am having should be in this board or the Support board, or is that posted somewhere for me to see as well? Thank you too Joker  sorry meant to thank the both of your guys help.

[Joker™]

Lol, feeling drunk now. How can one return the result in middle of loop