Selection Problem

[Custergrant 0]

I need help on a join, I think I have it mostly done, but I know my syntax isn't right.

First off, to give you an idea, I have two tables, plus my session variables. I'm first selecting from the table: user_resources where the user_id = that of the $_SESSION's, and in user_resources, there are three fields, user_id, resource_id, and amount, and I wish to print all of this. But the hitch to it is, resource_id is an integer. In another table called resources, are the fields id and name. The resource_id = resources.id, and I want to print the name associated with that id...

So this is the current code that I have now (I've tried thousands of combinations it seems like):

//Begin Resources Table$query = mysql_query("SELECT * FROM user_resources, resources WHERE ". $_SESSION['userid'] ." = user_resources.user_id user_resources JOIN resources ON user_resources.id = resources.id") or die(mysql_error()); echo("<br><br><br><center><b>Resources</b><table border='1' bordercolor='black'>");while($info = mysql_fetch_assoc( $query )) { echo "<tr>"; echo "<th>Name:</th> <td>".$info['amount'] . "</td> "; echo "<th>Amount:</th> <td>".$info['name'] . " </td>"; } echo "</font>";echo "</table>";

As you can see, all I wish to show is the amount of resources by its name. But I'm not sure how to get all of that in one query. As a review on the tables:

user_resource.user_id = $_SESSION['userid']
user_resource.resource_id = resource.id

And then I want the name and amount from user_resources.

I know, not hte best list in the world, but it gives a good idea of what I need. If anyone could help me out on this code snippet, it would be very much appreciated. I've spent over 3 hours on this so far and about to lose it if I can't figure this out.
Edited July 22, 2007 by Custergrant (see edit history)

[Custergrant 0]

Sorry for the double post. I got the problem above fixed, finally, and it's working just smashingly. I used the join below to fix it:

$sql = ("SELECT * FROM user_resources JOIN resources ON user_resources.resource_id = resources.ID WHERE user_resources.user_id =  $userid ");$result = mysql_query($sql) or die(mysql_error());

And then simply fetched an assoc array on it and printed the corresponding data that I wanted. Now that was for my resources table, but I've moved onto a couple more tables, and I'm having a problem with my buildings. I coded it perfectly I thought, and when I load the page, it says the data it should, but it has even more now. I'm creating a table with the building name and the amount of that type of building. And right now, in my test account, I just have one building. So it should be just "1 Papermill", which I get, but then I get additional columns with just ones in it... And I don't understand. I just now put an additional condition for the selection for where the building name cannot be null, but I'm not sure why it is doing this.

//Begin Buildings Table$ubuildings = ("SELECT * FROM user_buildings WHERE user_id = $userid AND building_id != NULL ");$resultb = mysql_query( $ubuildings ) or die(mysql_error());$infob = mysql_fetch_assoc( $resultb );$buildingid = $infob['building_id'];$buildings = ("SELECT * FROM buildings WHERE building_id = $buildingid ");$resultc = mysql_query( $buildings ) or die(mysql_error());echo ("<center><h1 style='font-size:150%'>Buildings</h1><table></center>"); while($infoc = mysql_fetch_assoc( $resultc )){ echo "<tr>"; echo "<td>".$infob['amount'] . " ".$infoc['name'] . " </td> "; } echo "</font>";echo "</table>";

[matak 2]

Try this

WHERE user_id = $userid AND building_id !== NULL

or this

WHERE user_id = ".$userid AND building_id !== NULL."

[Custergrant 0]

I thought that would work too, but I had already tried it and nothing. I still get the extra rows. It is completely boggling my mind as to why this is happening. I've printed both arrays and they're handling just fine, but I can't understand where these 1's are coming from.

[matak 2]

What happens when you var dump $ubuildings, and $resultc