MYSQL Cross Table lookup

[shalkith]

Hey everyone. I just wanna start with this; I've never taken a php class or anything and im just getting my feet wet with all this. I'm kinda throwning things togethere to make a little cms for my website.

 

I've got member sign up and login working fine. I've also got a simple member profile page going but one thing im not sure how to tackle exactly.

 

I want the profile page to display each users rank.

 

So far I have two tables in my database.

members and rank

Members contains the users first and last name. their username password email and rank ID number

Rank contains the rank ID and the rank name

 

 

What i need to know is how do I have the profile page lookup the session rank id from the member table, reference the rank id and display the rank name from the rank table for the user?

 

I hope i didnt confuse anyone with this and please tell me if im going about this wrong.

[shalkith]

I did some research while waiting and it looks like what i might need is a join.... i dont know for sure but let me know if im headed in the right direction. Thanks

[BoarderLine]

You need the 2 tables to link together with a common id (I assume here the common in rankID).

 

Assuming also the usersID = rankID then try something like the following:

 

[code=php:0]
$id = $_SESSION['rankID'];

 

SELECT table1.rankname, table2.rankID(or whatever fields you want also joined from the second table with the rankname table) FROM (table1 JOIN table2 ON table1.rankID=table2.rankID) WHERE table2.rankID=$id

[/code]

 

Hopefully helps a bit.

[shalkith]

Thanks for the reply!! 

 

Do I need to get rid of the 2nd set of "( )"?

 

Other than that I've changed the code to what I think should work on my page. I havent tested this as I'm not currently able to upload to my host

 

Does this look right?

Table1 / rank is the table with the rank names and the rank ID is the primary key.

Table2 / members is the table with the member information in it and a column named r_id that will be used to assign each user to their groups.

 

SELECT rank.rank, members.r_id FROM (rank JOIN members ON rank.r_id=members.r_id) WHERE members.r_id=$id 

 

Does this look right? Does case matter in this situation?

[BoarderLine]

Yeah that looks right (second set of brackets are needed), Case does matter for tables and fields

[shalkith]

Cool thanks! I ended up getting it uploaded but I think im doing something wrong cause that code kills my page. Where do I insert that code? should there be any other tags around it?

 

and where should i put:

$id = $_SESSION['r_id'];

 

should I put that in with the other session stuff? (username, name, email ect ect)

[BoarderLine]

what is your error saying?

[shalkith]

well, I've been poking around with it and now im just seeing the code on the page itself.

 

Basicaly im getting:

 

Name: John Doe

Email: email@email.email

Rank: SELECT rank.rank, members.r_id FROM (rank JOIN members ON rank.r_id=members.r_id) WHERE members.r_id=$id

[BoarderLine]

You need to add the code within php tags so it looks similar to this:

 

At the very top of your page if you havn't already done so you need add your db connection info

eg. (replace with your db name and password)

 

$conn = mysql_connect("localhost", "admin", "1admin") or die(mysql_error());

 

Then add query (within php tags) where needed on page.

 

$id = $_SESSION['r_id']; 
$sql = "SELECT rank.rank, members.r_id FROM (rank JOIN members ON rank.r_id=members.r_id) WHERE members.r_id=$id";
$sql_result = mysql_query ($sql, $conn ) or die ('MySQL query error: '.$sql);
$row = mysql_fetch_assoc($sql_result);
$rank = $row['rank'];
echo $rank;

 

Post back if you have any errors.

[shalkith]

hmm...

 

Now I'm getting

MySQL query error: SELECT rank.rank, members.r_id FROM (rank JOIN members ON rank.r_id=members.r_id) WHERE members.r_id=

 

in the same place i was getting the code before.

[BoarderLine]

Ok just looking back at what you need I think I put you wrong ($id = $_SESSION['r_id']; will only return the rank of the logged in user).  You want a list of ALL users and there ranks correct?  Apart from the rank not displaying are you able to display the list of users ok?  If so can you post the query code for your list of users you currently have?

[shalkith]

I do not currently have a list of all current users. That was one of the other items I was going to start working on soon.

 

I would like it to post only the rank of the current logged in user.

[BoarderLine]

Ok then,

 

From your last error it shows that $_SESSION['r_id'] is not set as it is empty in the query error message.

 

Is r_id the SESSION name of the logged in users id?  If not change $_SESSION['r_id'] to what ever it should be.  If it is, is the user actually logged in?  What happens when you place the following code in the <body> tag.

 

echo $_SESSION['r_id'];

[BoarderLine]

within php tags that is:

 

<?php
echo $_SESSION['r_id'];
?>

 

[shalkith]

r_id is the rank ID

 

the member ID is member_id

 

when I sign in the session ID is SESS_MEMBER_ID

 

I'm sure I'm signed in.

 

 

<?php echo $_SESSION['r_id']; ?>

in the body tags does nothing. I also tried it with $_SESSION['SESS_MEMBER_ID'] and $_SESSION['member_id'] because I wasnt sure.

 

 

This is what i currently have

 

 

 

$id = $_SESSION['SESS_MEMBER_ID']; 
$sql = "SELECT rank.rank, members.r_id FROM (rank JOIN members ON rank.r_id=members.r_id) WHERE members.r_id=$id";
$sql_result = mysql_query ($sql, $conn ) or die ('MySQL query error: '.$sql);
$row = mysql_fetch_assoc($sql_result);$rank = $row['rank'];
echo $rank;


<?php echo $_SESSION['r_id']; ?>

 

[BoarderLine]

Use this to display what sessions are set and what the values are?  Check to see that your users SESSION id is set as you expect.  It appears that it is not.

 

<?php
print_r($_SESSION);
?>

[shalkith]

<?php print_r($_SESSION); ?> is giving me:

 

 

Array ( [sESS_EMAIL_ADDRESS] => my email address [sESS_MEMBER_RANK] => 1 [sESS_MEMBER_ID] => 2 [sESS_FIRST_NAME] => firstname [sESS_LAST_NAME] => lastname )

 

I also tried <?php echo $_SESSION['SESS_MEMBER_ID']; ?> again and it gave me my user member_id primary key number.

 

(i had to delete the query that was causing the error before those worked)

 

[BoarderLine]

Ok may have messed up the query a bit try this:

 

$id = $_SESSION['SESS_MEMBER_ID'];
$sql = "SELECT rank.rank, members.r_id FROM (members JOIN rank ON rank.r_id=members.r_id) WHERE members.r_id='$id'";
$sql_result = mysql_query ($sql, $conn ) or die ('MySQL query error: '.$sql);
$row = mysql_fetch_assoc($sql_result);
$rank = $row['rank'];
echo $rank;

[shalkith]

Now I am getting this:

 

MySQL query error: SELECT rank.rank, members.r_id FROM (members JOIN rank ON rank.r_id=members.r_id) WHERE members.r_id='2'

 

Lol

[BoarderLine]

Ok try this just for different error info (might supply better info):

 

$id = $_SESSION['SESS_MEMBER_ID'];
$sql = "SELECT rank.rank, members.r_id FROM (members JOIN rank ON rank.r_id=members.r_id) WHERE members.r_id='$id'";
$sql_result = mysql_query ($sql, $conn ) or die(mysql_error());
$row = mysql_fetch_assoc($sql_result);
$rank = $row['rank'];
echo $rank;

[shalkith]

ok, now i get

 

No database selected

[BoarderLine]

Cool there's your problem, try:

 

Break it down again.

 

At top of page (change your values again):

 

<?php
$myServer = "localhost";
$myUser = "your_name";
$myPass = "your_password";
$myDB = "examples"; 

//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer"); 
?>

 

Then later where you want your rank displayed:

 

<?php
//select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
  or die("Couldn't open database $myDB"); 

$id = $_SESSION['SESS_MEMBER_ID'];
$sql = "SELECT rank.rank, members.r_id FROM (members JOIN rank ON rank.r_id=members.r_id) WHERE members.r_id='$id'";
$sql_result = mysql_query ($sql, $conn ) or die(mysql_error());
$row = mysql_fetch_assoc($sql_result);
$rank = $row['rank'];
echo $rank;
?>

[shalkith]

now im getting an internal server error 500 on that page. 

 

Sorry for making so much work for this lol and thanks for all your help so far!!

[BoarderLine]

oh dear! that;s a server error:

 

Fixing 500 errors - general

 

This error can only be resolved by fixes to the Web server software. It is not a client-side problem. It is up to the operators of the Web server site to locate and analyse the logs which should give further information about the error.

 

Sorry mate, good luck with it

[shalkith]

I think its just a generic error with this one. If I remove that last bit of code it comes back without issues. All other pages still work fine.