Listing "members" - supplied argument is not a valid MySQL result resource

[suthen_cowgirl]

Hey All,

Well I'm looking for help here because I know I don't have to post page after page of code cause you know what most of it looks like.   

So, now that the whole registering and logining/out of my members system works, I want to add some functionality to what they can do once their logged in.  One of the basic things is to list all the members.  I had a project in a php class a couple years ago that basically did this so I copied that code and changed what I thought I needed to change, but (of course, in my case) it doesn't work. 

I get the error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ....... on line 22

I've looked and looked at the code and just don't see whats wrong.  Everything I saw when googling said check for spelling and that this error usually has to do with connecting to the database, but i know it connects, obviously i can login and such.  So unless I'm using the connectToDB function wrongly...I just don't get it...Hope you can help me! 

The code for this page is:

Code:

<?php

include_once("config.php");

// Check user logged in already:
checkLoggedIn("yes");

require("InnerHeader.php"); 

//Create a select all query string
$strSelectQuery = "SELECT email, last_name, first_name FROM users ORDER BY last_name";

//Aquire a result set with the function query
$rsMemberList = connectToDB($strSelectQuery);

//Display the list of records in the Result Set.

//Display a header name.
echo "<h2><b>List of all Members (by name):</b></h2>";

//Get the number of rows in the "users" table and store in $intNumRows
$intNumRows = mysql_num_rows($rsMemberList);
for( $i = 0; $i < $intNumRows; $i++)
{
echo "<br>";
$arrMembers = mysql_fetch_array($rsMemberList);
&#160; {
&#160; &#160; echo "<a href = \"MemberInfo.php?email=" . $arrMembers['email'] . "\">" . $arrMembers['last_name'] .", ". $arrMembers['first_name'] . "</a>";
&#160; }
echo "<br>";
}

require("../Footer.php");

?>

$intNumRows = mysql_num_rows($rsMemberList); is line 22.  I've looked above it, I've checked my query for typos...

It won't let me even get to the page if I'm not logged in so, check that works. 
Header shows up, check that works.
I get the echoed "header name", check that works.
Then I get the error.
Footer shows up, check that works.

THANKS in advance!!

[munk]

The connectToDB function is only used to create an initial connection to the DB.  Once you've used it (it's done in config.php, you only need to connect to the DB once per script), it will obtain a MySQL resource handle called $link which is globally scoped so you can use it anywhere in the scripts.  You can then use $link to run queries against the DB in your scripts.

Think of $link as a 'pointer' to the database, it's like a variable you feed to other database functions to let them know 'this is the database connection we've got, it's over there (pointing:))'.

To do what you're trying to do, have a look at the checkUsers function in functions.php:

global $link;

    $query="SELECT login, password FROM users WHERE login='$login' and password='$password'";
    $result=mysql_query($query, $link)
        or die("checkPass fatal error: ".mysql_error());

    // Check exactly one row is found:
    if(mysql_num_rows($result)==1) {
        $row=mysql_fetch_array($result);
        return $row;
    }

Here you see how $link is passed as an argument to the mysql_query function to tell mysql_query 'here, this is the db we've got a handle on already!', also you see the sql query is passed into the mysql_query function as the first arg.  If that line '$result=mysql_query($query, $link)' is successful, $result will contain a mysql result resource variable (try running var_dump() on it to see this).  From there you can then use the $result variable to access the results of the query - using mysql_fetch_array or mysql_num_rows etc.

In the above snippet, you store your query in $query, then pass $query and $link to the mysql_query() function to execute the query - if successful the result is stored in $result.  From there you can go on to use $result to obtain or 'extract' data from the result set.

[suthen_cowgirl]

Thank you!  I actually understood all that (amazing for me) and it did get the list to work.

But, now the page that that page links to is giving me the same error.  I changed the same thing in it that you just helped me with hoping it'd be that simple but its not working.  Grr. 

Here's a snippet of that page:

Code:

<?php
//store the 'email' passed from the previous page in a variable
$strRecordKey=$_GET['email'];

//get the data for the member with the given $strRecordKey
//create a select query string 
$strMemberQuery="SELECT email, last_name, first_name, city, state, phone, student_type, expertise, legally_blind FROM users WHERE email=$strRecordKey";

//execute the query and save the result set in $rsMember
$rsMember=mysql_query($strMemberQuery, $link);

//Display header for the member details
echo "<h3><b>Member Information:</b></h3>";

//show the member details
//get a row of data and store it in $arrMember
$arrMember=mysql_fetch_array($rsMember);
?>

All I can think of this time is a spelling error, but I don't see it..

EDIT:  I added the "or die ...." after the $rsMember= line and the mysql error said:
Died retrieving info from db. Error returned if any: 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 '@domain.com' at line 1
So, does it not like that I'm using email addresses as the...'key'...is the @ or something screwing it up?

I'm still working on it...I want to try to fix it on my own but i just suck at code, I'd much rather stick to UI design. 

[munk]

The email address in your query needs to be enclosed with ' marks (email='$strRecordKey')

I should have mentioned it before and it's probably too late now, but there is a member profile script knocking around on this forums somewhere, it's linked to in the FAQ section I think.  Probably too late though if you're already using your own

Cheers.

(here's the profile.php script just fyi: http://forums.munk.me.uk/index.php/topic,110.msg545.html#msg545 - can be used to allow users to modify their details also, see next faq in the link above ^^)

[suthen_cowgirl]

Thanks! 

Hopefully one of these days I'll figure out these stupid lil mistakes.

Thanks for the link also.  I have a modify script from my php class but I'll probably look at that link too and see if the combo of both I can get what I need done. 

Thanks again!

[munk]

No problem, you seem to know what you're doing pretty much   The best tip I can think of is to use var_dump() as much as possible whenever there are any errors that aren't to do with syntax so you can see what a variable contains and work out why things are going wrong (like with the last prob above if you could have dumped the query to the browser you could have run it in a mysql client and hopefully from there (maybe!) figured out what was wrong with the query).

Cheers