MySQL Warning When Dealing With the smf_members Table

[bneacetp]

I am trying to access the smf_members table to check and see how many members are in a particular ID_GROUP.  The data is correctly retrieved, but I get at least some variation of the following three warnings showing up at the end of the pages that try to access the smf_members table:

Warning: mysql_query(): 10 is not a valid MySQL-Link resource in [...]\forum\Sources\Subs.php on line 316

Warning: mysql_affected_rows(): 10 is not a valid MySQL-Link resource in [...]\forum\Sources\Subs.php on line 331

Warning: mysql_query(): 10 is not a valid MySQL-Link resource in [...]\forum\Sources\Subs.php on line 316

Basically what I am trying to do is to:

1. Connect to and select the forum database.
2. Run a query:

Code Select Expand

$query = 'SELECT ID_MEMBER FROM smf_members WHERE ID_GROUP = ' . $row['group_id'] . ';';
3. Close the connection.
4. Use mysql_num_rows() on the returned result stored in a variable to see how many users are part of the particular group.

Any idea as to what is causing the warnings and any possible workarounds?

[Tony Reid]

Shouldnt it be something like this....

$query = "SELECT ID_MEMBER FROM smf_members WHERE ID_GROUP = ' . $row['group_id'] . ';";

[bneacetp]

Shouldnt it be something like this....

$query = "SELECT ID_MEMBER FROM smf_members WHERE ID_GROUP = ' . $row['group_id'] . ';";

That code generates an error.  The code I have in my first post on this topic works but it generates the the kind of warnings mentioned above.  The code seems to work, but I don't understand why the warnings come up. 

[Leipe Po]

are you using db_query(smf own query thingy) or mysql_query()?
if you use mysql, change the mysql bit to db so
mysql_query => db_query
mysql_affected_rows => db_affected_rows

ect. ect.

als make sure you use __FILE__,__LINE__ inside querys, so it would be like:

db_query("SELECT ID_MEMBER FROM smf_members WHERE ID_GROUP = ' . $row['group_id'] . ', __FILE__,__LINE__");

or something close to that

[bneacetp]

When I try the suggestion above, I get:

Warning: Cannot modify header information - headers already sent by (output started at .....\staff_avail.php:10) in .....\forum\Sources\Subs.php on line 3176

Warning: Cannot modify header information - headers already sent by (output started at .....\staff_avail.php:10) in .....\forum\Sources\Subs.php on line 3177

Warning: Cannot modify header information - headers already sent by (output started at .....\staff_avail.php:10) in .....\forum\Sources\Subs.php on line 3183

Warning: Cannot modify header information - headers already sent by (output started at .....\staff_avail.php:10) in .....\forum\Sources\Subs.php on line 3186

Warning: template_main_above(../ssi/core_settings.php): failed to open stream: No such file or directory in .....\forum\Sources\Load.php(1607) : eval()'d code on line 54

Warning: template_main_above(): Failed opening '../ssi/core_settings.php' for inclusion (include_path='.;c:\php4\pear') in .....\forum\Sources\Load.php(1607) : eval()'d code on line 54

Warning: template_main_above(../ssi/header_scripts.php): failed to open stream: No such file or directory in .....\forum\Sources\Load.php(1607) : eval()'d code on line 135

Fatal error: template_main_above(): Failed opening required '../ssi/header_scripts.php' (include_path='.;c:\php4\pear') in .....\forum\Sources\Load.php(1607) : eval()'d code on line 135

I am closer to the end result that I want just using mysql_query but I get those three pesky warnings that I mentioned earlier in this post.  Any suggestions? 

[Rudolf]

Make sure that the include statment for the SSI.php is the very first line of your file. No matter how you write it, php mixed with plain html or not, that line has to be the first thing there is.

Occasionally it could happen that the SSI.php connects to the databse but doesn't selects the schema. It happened to me, and I still couldn't figure it out why. The solution was to call the @mysql_select_db($db_name, $db_connection); manually from the custom script.
The invalid link-resource error might be cause by the fact that there's no database selected.

Rudolf

[bneacetp]

Make sure that the include statment for the SSI.php is the very first line of your file. No matter how you write it, php mixed with plain html or not, that line has to be the first thing there is.

Occasionally it could happen that the SSI.php connects to the databse but doesn't selects the schema. It happened to me, and I still couldn't figure it out why. The solution was to call the @mysql_select_db($db_name, $db_connection); manually from the custom script.
The invalid link-resource error might be cause by the fact that there's no database selected.

Rudolf

Thanks for your time spent, but still no success.  If anyone else has any ideas, please let me know.  Thanks.

[kegobeer]

How are you including SSI.php into your custom script?  Did you happen to look at the ssi_examples.php file or review the SSI faq?

[bneacetp]

My SSI.php include currently looks like this:

Code Select Expand

<?php require_once("C:\\Inetpub\\wwwroot\\twsyfree.org\\forum\\SSI.php"); ?>

The above line is at the very top of the PHP file.  I have done SSI.php includes before and they have worked.  The big difference is that I am trying to access one of SMF's db tables directly.  Below is basically the script that I am trying to work with.  The area in maroon is where I think the problem may be.  If you discover any problems, please let me know.  Thanks.

<?php
    $connection = mysql_connect('localhost', '###', '###');
    mysql_select_db('###', $connection);
            
    $query = 'SELECT name, description, img_url, openings, group_id FROM occupations WHERE visible = 1 ORDER BY name ASC;';
    $result0 = mysql_query($query);
    mysql_close($connection);
            
    while($row = mysql_fetch_assoc($result0))
    {
        $connection1 = mysql_connect('localhost', '###', '###');
        mysql_select_db('forum', $connection1);
        $result1 = db_query('SELECT ID_MEMBER FROM smf_members WHERE ID_GROUP = ' . $row['group_id'] . ';', __FILE__,__LINE__);
               
        mysql_close($connection1);
            
        $fills = mysql_num_rows($result1);
            
        echo '<table border="0" width="100%" cellspacing="1" cellpadding="0">
           <tr>
      <td class="header">&nbsp;Occupation Title: ' . $row['name'];
        if($row['openings'] > 0)
   echo ', <a href="contact_form.php"><font color="#FFFFFF">Apply</font></a>';
        echo '</td>
          </tr>
          <tr>
      <td><b><u>Description:</u></b> ' . $row['description'] . '</td>
          </tr>   
          <tr>
      <td><b><u>Number Filled:</u></b> ' . $fills . ', <b><u>Number of Openings:</u></b> ' . $row['openings'] . '.</td>
          </tr>
     </table>
     <hr/>';
    }
?>

[^DooM^]

This is the SQL code I use to return a users high score from the arcade. It should be the same principle for your work you just need to alter your select statement.

Code Select Expand

    $scorerequest = db_query("SELECT score FROM smf_games_high WHERE member=$nUserID AND game='$DBGameName'", __FILE__, __LINE__);
    $personalscore = mysql_fetch_object($scorerequest);
    mysql_free_result($scorerequest);


$personalscore contains an object of the returned value.

HTH

-Jon

[Rudolf]

First of all, I don't see point in reconnecting to the database everytime you wish to make a query.
Also you should use the connection that the SSI.php creates.
That being said your code should look like this.

Code Select Expand


<?php
    mysql_select_db('###', $db_connection);
            
    $query = "SELECT name, description, img_url, openings, group_id FROM occupations WHERE visible = 1 ORDER BY name ASC";
    $result0 = db_query($query,__FILE__, __LINE__);

    mysql_select_db('forum', $db_connection);
    while($row = mysql_fetch_assoc($result0))
    {
        $result1 = db_query("SELECT ID_MEMBER FROM smf_members WHERE ID_GROUP = $row[group_id]", __FILE__,__LINE__);
               
        $fills = mysql_num_rows($result1);
            
        echo '<table border="0" width="100%" cellspacing="1" cellpadding="0">
           <tr>
      <td class="header">&nbsp;Occupation Title: ' . $row['name'];
        if($row['openings'] > 0)
   echo ', <a href="contact_form.php"><font color="#FFFFFF">Apply</font></a>';
        echo '</td>
          </tr>
          <tr>
      <td><b><u>Description:</u></b> ' . $row['description'] . '</td>
          </tr>   
          <tr>
      <td><b><u>Number Filled:</u></b> ' . $fills . ', <b><u>Number of Openings:</u></b> ' . $row['openings'] . '.</td>
          </tr>
     </table>
     <hr/>';
    }
?>

This should work. Also probably you should do a JOIN instead of doing tens or hundreds of queries (depending on the resultset from the first query). But I really don't know what are you trying to do, so is up to you.

[kegobeer]

rudiksz, that code is incorrect.  bneacetp, some time this morning I will have time to show you the correct code.

[kegobeer]

Code Select Expand


<?php 
    require_once(dirname(__FILE__) . '/SSI.php'); // This means the script is in the SMF directory     
    $result0 = db_query("SELECT name, description, img_url, openings, group_id FROM occupations WHERE visible = 1 ORDER BY name ASC", __FILE__, __LINE__);
            
    while($row = mysql_fetch_assoc($result0))
    {
        $result1 = db_query('SELECT ID_MEMBER FROM smf_members WHERE ID_GROUP = ' . $row['group_id'], __FILE__, __LINE__);
            
        $fills = mysql_num_rows($result1);
            
        echo '<table border="0" width="100%" cellspacing="1" cellpadding="0">
           <tr>
      <td class="header">&nbsp;Occupation Title: ' . $row['name'];
        if($row['openings'] > 0)
   echo ', <a href="contact_form.php"><font color="#FFFFFF">Apply</font></a>'; 
        echo '</td>
          </tr>
          <tr>
      <td><b><u>Description:</u></b> ' . $row['description'] . '</td>
          </tr>   
          <tr>
      <td><b><u>Number Filled:</u></b> ' . $fills . ', <b><u>Number of Openings:</u></b> ' . $row['openings'] . '.</td>
          </tr>
     </table>
     <hr/>';
    }
?>


I assume your table and the SMF tables are in the same database.  If not, you should consider moving your tables into the SMF database.  There is no reason to have separate databases.

[Rudolf]

Off records:
@kegobear

I fail to see where is the incorrectness of my code compared with yours. You see your code is just like mine except the database selections.
Which I left there assuming that he has the tables in different databases.
Obviously I haven't tested the code I wrote, I just took the thing bneacetp posted and modified it. The same way you did.
That being said, I don't mean to be polemic, it should work in any of the ways.

[kegobeer]

@rudiksz: You never included SSI.php in your code, and when switching databases, you need to specify the database connection string.  You can't use the same string when connecting to different databases.  Unless $db_connection is declared in a global statement, the variable would be without value.  You also can't throw $row[group_id] (which should be $row['group_id']) inside of a string variable without enclosing it in braces {}.  However, that really isn't a recommended method.  Instead, it should be added to the string with a dot ("stuff " . $row['group_id'] . "more stuff").

[Rudolf]

Ok, now i see.
About the SSI and the database connections I was aware, as I said I just took his code and modified it. I supposed that he will figure it out by himself that he has to include the SSI (and declare the db_connections). Anyway, my purpose was to give him a hint on how to do it. I have the sensation that he haven't posted all the content of his file. For example leaving out the part where the SSI is being included. So i just modified what he gave.

About the $row['group_id'] thingy I would like to hear more, why it is  not the recommended way. I use it often (but with moderation) and never had any problems. The curly braces are needed only when your variable is complex(like multidimensional array) or in a context where the PHP parser would need 'help' (see the way SMF does the queries). With simple variables it is not necessary.
So why you don't recommend it? The SMF software is using it in each and every query. 

[Leipe Po]

if anyone has problems with the code of kegobeer, change this line:

Code Select Expand


require_once('dirname(__FILE__) . '/SSI.php'); // This means the script is in the SMF directory


into

Code Select Expand


require_once(' . dirname(__FILE__) . '/SSI.php'); // This means the script is in the SMF directory


or

Code Select Expand


require_once(dirname(__FILE__) . '/SSI.php'); // This means the script is in the SMF directory


-Leipe Po

[kegobeer]

@Leipe: Thanks.  I fixed my code.

@rudiksz: Putting an array in-line works with some indices and not others.

Works: echo "Hello $variable[things]";
Doesn't work: echo "Hello $variable['things']";

If you have your array outside of a string, always enclose the index inside of single quotes.  If you enclose your index inside single quotes, you must use a dot operator or put it inside braces.  SMF follows this rule, although if you didn't understand the quote/no quote index you'd never know it.  Personally, I always enclose my indexes in quotes, so I always use the dot/comma operator.  I find that mixing how array indexes are formatted leads to hard to find programming errors, so I always recommend sticking with one or the other.  SMF uses both types, which is ok as long as all programmers agree to follow a certain coding convention to avoid some people from doing it one way and others doing it the other way.

You are correct about the use of braces.

[Rudolf]

I understand fully what you say, no problem there.

here are some examples
Since the forum can't do multi-highlight for strings i did a small screenshot of my program...

If you look at lines 5 and 6 I think it's clearly visible that the 5 is easier to read and takes less effort to write it. Anyhow, that's kind of an extreme situation.

My rule of thumb is this:
  if it's a query use double quotes. Inside double quoted strings I can use variables. So when I have a simple variable I use that way.
  if it's a echo to create html code I use simple quotes. Inside single quoted strings I can't use variables. So I escape them.
  if it's something else I use my common sense.

In either way you can produce an easily readable code.
But probably I talk so because of syntax highlighting. It sure helps a lot.