[RESOLVED] Need a query....Cant seem to get it myself....

trehy2006

Basically I have two tables,

One for users
and for friends.

What I need to do, is run a query like

"SELECT `fid` FROM friends WHERE `MID` = ".$user['id']."";

That would then use the members ID to select all of his/her friends IDs
What I then need to do is use those friends ID to query the users table to get its info.

What I cant seem to work out is how to use each of the fid fields returned from that query (it could return loads) to run another query to get that members information.

TIA
Robert

Piranha

The answer is that you should use only one query with a join. Something like this:

SELECT users.column1, users.column2, users.columns3...
FROM friends
INNER JOIN users ON friends.fid = users.id
WHERE friends.fid = $userid

Then you will get the users that are friends right away. Much better than doing multiple queries.

Just make sure that your variables are safe from sql injection. I don't know if that is done or not in this case, but always worth to mention.

trehy2006

Ok so I put it into the other bit of code I have....

But I get two errors:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/mppco/public_html/cc/member-functions.php on line 269

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/mppco/public_html/cc/member-functions.php on line 274

Here is the function that is being called

//Create the table that displays all of the users friends
function display_friends ($id, $show)
{	
	$i = 1; 
	echo "<table border=\"1\"><tr>"; 
	$result = mysql_query( "SELECT users.username, users.avatar FROM friends INNER JOIN users ON friends.fid = users.id WHERE freinds.mid = '".$user['id']."'" ); 

/*Line 269*/	while ( $row = mysql_fetch_assoc( $result ) ) { 
   		echo "<td>$row[name]</td>"; 
   		if ( $i % 3 == 0 ) { echo "</tr><tr>"; } 
    	$i++; 
												  }
/*Line 274*/	mysql_free_result( $result ); 
	if ( $i % 3 == 2 ) { 
    	echo "<td>&nbsp;</td><td>&nbsp;</td>\n"; 
	} elseif ( $i % 3 == 0 ) { 
    	echo "<td>&nbsp;</td>\n"; 
	} 
echo "</tr></table>";
}

nemonoman

The errors are telling you that the query did not run properly...instead of producing a fetchable resource, the query produced nothing. So mysql_fetch fails, trying to fetch what isn't there.

You by this point probably no how to get the mysql error info. Time to get that info and fix your query. It's probably something simple, like a misidentified column name...

Piranha

Or a misspelled table name...

trehy2006

Or a misspelled table name...

Found it!

freinds instead of friends (where clause)

trehy2006

But now its not showing anything...

All it shows is a table 1 x 1

$row['username']

correct....or should it be?

$row['users.username']

I cannot seem to find it either way

(the same funtion as previous ... only with spelling corrected)

nemonoman

What happens with:

while ( $row = mysql_fetch_array( $result ) ) {
print_r($row);
}

trehy2006

I get the same cell appearing but with approx. 3 blank lines above it

nemonoman

You could not possibly get that result if you use my code. Please use my code and display the exact results.

nemonoman

Perhaps you misunderstood: Run this code, post the result.

$result = mysql_query( "SELECT users.username, users.avatar FROM friends INNER JOIN users ON friends.fid = users.id WHERE freinds.mid = '".$user['id']."'" ); 
while ( $row = mysql_fetch_array( $result ) ) {
print_r($row);
}

trehy2006

Then I just get a blank page

nemonoman

This means that your query is not returning any results at all. It's checking the data and returning an array of zero elements. No records match the criteria in the select statement. Since this is (probably) test data at this point, either your test data is not set up to produce the results you expect, or your query is traversing the data in a way you different than you want.

If you are running my example code above, did you set some value for $user['id'] before running it?
$user['id']='1234ABCXYZ';
$result = mysql_query( "SELECT users.username....

If you are having problems:

There are several likely places to look:
Echo out your query before you run it: has the variable been properly set here?
WHERE freinds.mid = '".$user['id']."'"

Then make sure that you have a record in users and a record in friends that will match up with your test. Make sure the users.id of the users recordd EXACTLY matches the friends.fid element. Of course you want to be sure that the friends.mid elemet EXACTLY matches the $user['id']

trehy2006

Ok, so the $users['id'] wasn't being sent properly (echoed query)

But now that it is corrected, I am still geting the blank page

...All the things in the db are correct too!?!

nemonoman

Sorry, friend, obviously something is NOT correct.

Suggest you create 2 test records in friends and users, CAREFULLY entering the id information in the proper fields. Clearly your existing data is NOT reliable.

trehy2006

$user['id'] = 1

therefore friends.mid needs to be 1

friends.fid = 2 (in the table where mid = 1)

so there needs to be a user with ID of 2

So I dont think my database is incorrect!?

also see here

nemonoman

When all else fails, get explicit:

$selectme=$users['id'] ;
$selectme=1; //override to 1 for testing...
$query="
     SELECT users.username, users.avatar    

     FROM users, friends 
     WHERE friends.fid = users.id 
     friends.mid = $selectme";
echo "<PRE>$query\n<BR>"; //for testing
$result=mysql_query($query);
while ( $row = mysql_fetch_array( $result ) ) { 
     print_r($row); //for testing
echo "</PRE>"; //for testing
}

trehy2006

heres the output

SELECT users.username, users.avatar FROM users, friends WHERE friends.fid = users.id friends.mid = 1 

Warning:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/mppco/public_html/cc/member-functions.php on line 297

nemonoman

Try again with corrected newly edited code in previous message. I left out the mysql_query line.

trehy2006

same error, only it corrects the fetch_array line to 298