[SOLVED] JOIN question

[Custer]

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 userid = that of the $_SESSION's, and in user_resources, there are three fields, userid, resourcetype, and amount, and I wish to print all of this. But this hitch to it is, resourcetype is an integer. In another table called resources, are the fields id, name, and then the market buys and sells. The resourcetype = resources.id, and I want to print the name associated with that id...

 

So how do I join it so that it would print the resource name and amount?

[wsantos]

look if you could use this...just remove the fields that you dont need.

$query = "SELECT user_resources.userid,user_resources.resourcetype,user_resources.amount,resources.id,resources.name,resources.buy,resources.sell from user_resources,resources where userid =" . $yoursessionvariable . " and resources.id = user_resources.resourcetype";

[Custer]

Okay, that's the same setup I had, but wasn't sure on it...so this is the code that I have (am testing now):

 

//Begin Resources Table
$query = ("SELECT 

user_resources.userid,user_resources.resourcetype,user_resources.amount,resources.id,resources.name 

FROM user_resources,resources WHERE userid =" . $_SESSION['userid'] . " AND resources.id = 

user_resources.resourcetype") or die(mysql_error()); 
echo("<br><br><br><center><b>Resources</b><table border='1' bordercolor='black'>");
while($info = mysql_fetch_array( $query )) 
{ 
echo "<tr>"; 
echo "<th>Name:</th> <td>".$info['amount'] . "</td> "; 
echo "<th>Amount:</th> <td>".$info['name'] . " </td>"; 
} 
echo "</font>";
echo "</table>"; 

[Custer]

Now I keep getting errors referring to the syntax of the AND.

[wsantos]

let's try to use one where condition first

[Custer]

Okay, here's the updated code, and I keep getting dern errors:

 

$query = mysql_query("SELECT user_resources.user_id, user_resources.resource_id, user_resources.amount, resources.ID, resources.name FROM user_resources, resources WHERE ".$_SESSION['userid']." = user_resources.user_id, resources.ID = user_resources.resource_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>"; 
} 

 

The error I'm getting is this:

 

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 '= user_resources.user_id, resources.ID = user_resources.resource_id' at line 1

[Custer]

I've edited it to a join, and still getting errors ...

 

$query = mysql_query(SELECT * FROM user_resources JOIN resources ON user_resources.resource_id = resources.ID AND user_resources.user_id = ". $_SESSION['userid'] .") or die(mysql_error());

[Barand]

try it this way so if it fails you can inspect the offending query string.

<?php
$sql = "SELECT * 
        FROM user_resources 
        JOIN resources ON user_resources.resource_id = resources.ID 
        WHERE user_resources.user_id = '{$_SESSION['userid']}'";
         
$query = mysql_query($sql) or die(mysql_error() . "<pre>$sql</pre>");
?>

[Custer]

Okay, I tried that code and it didn't display anything, so I put in my while loop, and am not getting any data...

 

$sql = "SELECT * 
        FROM user_resources 
        JOIN resources ON user_resources.resource_id = resources.ID 
        WHERE user_resources.user_id = '{$_SESSION['userid']}'";
         
while($info = mysql_fetch_array( $sql ))
{ 
echo "<tr>"; 
echo "<th>Name:</th> <td>".$info['amount'] . "</td> "; 
echo "<th>Amount:</th> <td>".$info['name'] . " </td>"; 
} 

[Barand]

You seem to missing the "mysql_query" bit

[Custer]

Heh, yah, I had it on the next test though, and still, no data...

 

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

[Barand]

Any error message if you put the " or die(mysql_error())" bit in?

[Custer]

Nope, all I get showing is "Resources" in the actual HTML...no data whatsoever.

[Barand]

What does

 

echo $sql;

 

show;

[Custer]

SELECT * FROM user_resources JOIN resources ON user_resources.resource_id = resources.ID WHERE user_resources.user_id = ''

 

That's what I get there, which means it's stopping at my session variable.

[Barand]

That was my guess. Have you called session_start() at the top of the script?

[Custer]

Well, I'm requiring my session.php which has the session_start() in it, unless I need to define that in each page as well...

 

EDIT: And I know it's working, just because further up in the code, I had to use the $_SESSION['userid'] and that part is working just fine.

[Barand]

I just did quick test and got

 

[pre]

SELECT *

        FROM user_resources

        JOIN resources ON user_resources.resource_id = resources.ID

        WHERE user_resources.user_id = '123'

[/pre]

 

so it must be getting reset somewhere.

[Custer]

Well, if you don't mind looking at the entire page's code (not much), here it is:

 

<?php
require ('../session.php');
?>
<html>
<title>Custopoly-Members Page</title>
<head>
<link rel="stylesheet" type="text/css" href="/members/memberstyle.css">
</head>
<body>
<div id="header">
<!--Logo--!>
<img src="/images/logo2.gif" height='150' width='100%'>
<!--End Logo--!>
</div>
<div id="leftbox">   
<?php
include ('membernav.html');
?>
</div>
<div id="rightbox">    
<script src="http://www.coolwebtoys.com/Toy.ashx?ut=9424cbe0" type="text/javascript"></script>
<noscript><a href="http://www.coolwebtoys.com/" target="_blank">CoolWebToys</a></noscript>
<div class="cwt_9424cbe0_footer"><a href="http://www.coolwebchat.com" target="_blank"><img 

src="http://www.coolwebchat.com/_Images/cwc_brand.gif" alt="Cool Web Chat" /></a><a 

href="http://www.coolwebchat.com/" target="_blank">CoolWebChat Chat Rooms</a></div>
</div>
<div id="middlebox">
<?php
//connection
require ("../session.php");
require ("../configure.php");
mysql_connect ("$dbhost", "$dbuser", "$dbpass")or die("Could not connect: ".mysql_error());
mysql_select_db("$dbname") or die(mysql_error());
//Number of Game Players
$q = "Select userid FROM users";
$r = mysql_query($q) or die(mysql_error());
$num_users = mysql_num_rows($r);


echo("<center><h1 style='font-size:150%'>Welcome ".$_SESSION['username']."</h1><br><table 

border='1' bordercolor='black' width='300'><tr><td width=275>Number of Players:</td><td 

width=25>$num_users</td></tr><tr><td>Game Hour:</td></tr></table></center>");

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

?>
</div>
<div id="footer">
<center>Content Copyright © 2007, Custergrant<br>Mwuahahaha</center>
</div>
</body>
</html>

 

If this can't be done in one query, perhaps I'll just need to do several...

[Barand]

The same query in that page has a syntax error where you concatenate the session variable. Also there is no prior reference to $_SESSION['userid'], only $_SESSION['username']

[Custer]

My bad, I meant username, but the username session variable works just fine. When I login, it says Welcome and the name of the account you logged in as. There is no problem there...

[Barand]

Because $_SESSION['username'] has a value it doesn't guarantee $_SESSION['userid'] has.

 

Does

 

echo $_SESSION['userid'];

 

give you any output?

[Custer]

Yes and no. I was working on another PHP script that requires the userid, when I realized that $_SESSION['userid'] was never defined in the session.php, so I just added the $userid = $row['userid'], but on that page, keep ending up with an empty query...but here's the session.php:

 

<?PHP
//include in all files to check session and login
session_start();

require("configure.php");


//check session status
if($_SESSION['logged'] != "1"){
//bad session, kill it
session_destroy();


} else { // SESSION GATE
//check login details against table
$user = $_SESSION['username'];
$pass = $_SESSION['password'];


//connect 
$link = mysql_connect($dbhost, $dbuser, $dbpass)  or die('Could not connect: ' . mysql_error($link));
//select database
mysql_select_db($dbname, $link) or die(mysql_error($link));

//check details in the DB
$result = mysql_query("SELECT * FROM users WHERE username='$user' AND password='$pass'",$link) or die (mysql_error());

$row = mysql_fetch_array($result,MYSQL_ASSOC);

$userid = $row['userid'];

//check details from session and DB

if($user == $row['name'] && $pass == $row['pass'] && $row['userlevel'] == 1){
  //if user is correct then login must be true
  $_SESSION['logged'] = "1";
}
else{
  //if user is not correct send error message to main page
  $_SESSION['error'] = "1";
  $_SESSION['message'] = "Sorry there was an error with your login details, please <a href=login.php>try again</a>";
};

}; // SESSION GATE ELSE


//if we get this far then they are logged in and can see the page below! Yay!
?>

[Custer]

YES!!!!!!!!!!!!!!

 

I got it to work! Thanks so much guys, especially Barand (think he was the only one..)..