Jump to content
xisto Community
Sign in to follow this  
Custergrant

Selection Problem

Recommended Posts

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 by Custergrant (see edit history)

Share this post


Link to post
Share on other sites

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>";

Share this post


Link to post
Share on other sites

Try this

WHERE user_id = $userid AND building_id !== NULL

or this

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.