PHP MySQL Query Build

help needed in building query between tables

[scankified]

Hi all,

I'm pretty new to web development. Anyway here is my situation;

- 3 tables: Users, Projects and UsersProjects (forming the one-manys)

So basically, a user creates a project, then in UsersProjects, the user ID and project ID is stored, showing which projects the users are related to...

Under my 'Manage Projects' page, I want to run a query that displays the projects related (assigned) to the current user logged in. Each project that is created is assigned to them automatically via posting the session variable of user_id... This is what I have:

$sql = "SELECT user_id, project_id, projectname, projectdeadline, projectdetails FROM projects_tb JOIN usersprojects_tb on projects_tb.project_id WHERE user_id = '".$_SESSION['SESS_USER_ID']."'";

Can anyone help or even guide me in the right direction, I'm really new to SQL usage and don't really know what syntax to be using or what operations to be using to manipulate the data! Any help is much appreciated, thanks.

[Readie]

If you are selecting from multiple tables you need to specify the tablename everytime you reference the column name (not in mysql_result though)

user_id, project_id, projectname, projectdeadline, projectdetails

All need their relevent table prefix, like this:

tableName.columnName

[WesleyC]

$sql = "SELECT user_id, project_id, projectname, projectdeadline, projectdetails
FROM projects_tb
INNER JOIN usersprojects_tb on projects_tb.project_id = usersprojects_tb.project
INNER JOIN users_tb ON usersprojects_tb.user = users_tb.user_id
WHERE user_id = '".intval( $_SESSION['SESS_USER_ID'] )."'";

This might do the trick. INNER JOIN will almost always work for situations like this--rarely is any other join method necessary for most business applications. Also note that I wrapped the user ID in a call to intval( $var )--I'm assuming the contents of that variable are an integer. If this is the case, the intval() call will help prevent SQL injection attacks.

Technically you could get away without any type of JOIN keyword, but in most cases it's cleaner to use an INNER JOIN.

[scankified]

Thanks you both for the help... WesleyC, I have tried what you put but I am getting this error:

Couldn't perform query SELECT user_id, project_id, projectname, projectdeadline, projectdetails FROM projects_tb INNER JOIN usersprojects_tb on projects_tb.project_id = usersprojects_tb.project INNER JOIN users_tb ON usersprojects_tb.user = users_tb.user_id
Column 'user_id' in field list is ambiguous

Am I right in saying that this is because user_id exists in both users table and usersprojects and its asking me which one to use?

[rocknbil]

Column 'user_id' in field list is ambiguous

Per Readie's comment . . . it's ambiguous because you need to specify the table to which you are referring, there are two field names with user_id present (example below.)

The thing about joins is they will return results whether or not the values of the joined tables are null, which is sometimes what you want. Sometimes it's not. :-)

For example, if there are no entries in the joined table relating to the first table, a join will give you "field 1 field 2..." and NULL NULL NULL for the non-existent corresponding entry.


You can add a "not null" to the where, or,

$sql = "select projects_tb.user_id, projects_tb.project_id, projects_tb.projectname, projects_tb.projectdeadline, projects_tb.projectdetails
FROM projects_tb,usersprojects_tb,users_tb where projects_tb.user_id=users_tb.user_id and usersprojects_tb.project_id=projects_tb.project_id and projects_tb.user_id = '".intval( $_SESSION['SESS_USER_ID']);

May have some of the specific fields incorrect, but that's the concept . . . this will not return any results if any of the values in the joined tables are null.

[edited by: rocknbil at 9:10 pm (utc) on Mar 15, 2010]

[Readie]

It's saying that because when using join, you NEED to declare the table name for every column name.

You also have the wrong syntax for your inner join. This is what you should have:

Select `tableOne`.`columnOne`, `tableOne`.`columnTwo`, `tableTwo`.`columnOne`
FROM `tableOne`
INNER JOIN `tableTwo`
ON `tableOne`.`ID_Column` = `tableTwo`.`ID_Column`
WHERE `tableOne`.`columnOne = "some criteria"
ORDER BY `tableOne`.`columnOne` ASC

EDIT: Rock types faster than me :(

[topr8]

All need their relevent table prefix, like this:

tableName.columnName

sorry slightly OT, but that is not required, the only time it is required is if column names in two tables are the same.
[it might make the query marginally faster, but it isn't required]

[scankified]

So far I am using this:

$sql = "SELECT users_tb.user_id, projects_tb.project_id, projects_tb.projectname, projects_tb.projectdeadline, projects_tb.projectdetails
FROM projects_tb INNER JOIN usersprojects_tb on projects_tb.project_id = usersprojects_tb.project_id INNER JOIN users_tb ON usersprojects_tb.user_id = users_tb.user_id
WHERE user_id = '".intval( $_SESSION['SESS_USER_ID'] )."'";

Its still saying the ambiguous error for user_ID, even though I am stating that it belongs to users table (right at the start). I've also tried removing users_tb.user_id at the start, but I still get the same error. The only aspect here I am trying to link is the user associated with the project, and displaying the project information for this particular user. Is this something is relatively straight forward? Thinking about quickly going through some MySQL tutorials :)

[scankified]

Hey I managed to fix this... basically I just needed to specify the last user_id from the users table... as many of you already stated :D haha, sorry about that!