Dealing with multiple tables

[Negai]

Hi, I apologise if this has already been covered, but I couldn't find anything that helped me when I did a search.

Anyway, I have four tables:

users
id
name
email
group_id

group
id
name

categories
id
name

user_categories
user_id
category_id

What I want to do is display users of a certain category (users belong to multiple categories) and group (Can only belong to one group) on a page. Unfortunately dealing with related information spread across multiple tables is slightly beyond me. Any help that can be provided would be greatly appreciated.

[Jhorra]

You want what's called a join to link the tables together. You would end up with something like this:

$sql = "SELECT 
u.name c.name 
FROM
users AS u
INNER JOIN user_categories AS uc ON
u.id = uc.user_id
INNER JOIN categories AS c ON
uc.category_id = c.id
WHERE c.id = 'some category'";

[Negai]

Thanks! It's printing out the correct number of records but doesn't seem to be displaying the users name. All I see is:

Any ideas? This is the code I'm using.

<?php
            
$userlist = @mysql_query(
"SELECT u.name FROM users AS u INNER JOIN user_category AS uc ON u.id = uc.user_id INNER JOIN category AS c ON uc.category_id = c.id WHERE c.id='3' ORDER BY u.name ASC");
if (!$userlist) {
exit ('<p>Error performing query: ' . mysql_error() . '</p>');
}
            
while ($user = mysql_fetch_array($userlist)) {
$user_name = $user['u.name'];
            
echo "<p><span class='bullet'>&curren;</span> $user_name<br /></p>";
            
}
            
?>

[Raju Gautam]

Change this line:

$user_name = $user['u.name']; 

To

$user_name = $user['name']; 

[Jhorra]

Yeah, you are basically giving the table an alias when you do users AS u

when you give a table an alias, you have to specify which table you want the field from in your select statement. The results you get from your query only carry the field name though, not the table alias.

If you are pulling two fields that have the same name, but different tables you have to alias the field like this

SELECT c.name AS user_name