Help with query

[karatekid36]

Hey Everyone,

I have this query that I am using to display some links. Basically I want the query to say select the info IF current = 1 and admin = 1, but in this table I am querying, I have 20 columns that are for users' id number and if the logged in user's number is in one of these columns, I should display too.  I can not get the right AND / OR mix to get it to do what I need.  Is it clear what I am looking for and does anyone have any suggestions?

 

SELECT * FROM projects WHERE current = '1' AND admin = '1' AND user1=$user_id OR user2=$user_id OR user3=$user_id OR user4=$user_id OR user5=$user_id OR user6=$user_id OR user7=$user_id OR user8=$user_id OR user9=$user_id OR user10=$user_id OR user11=$user_id OR user12=$user_id OR user13=$user_id OR user14=$user_id OR user15=$user_id OR user16=$user_id OR user17=$user_id OR user18=$user_id OR user19=$user_id OR user20=$user_id

[beebum]

Looks like bad database design to me. Hard to write good code in that case.

[Mirkules]

There are two problems with the query that you showed. I'll start with the easier issue.  Whenever you have AND and OR operations, the OR operations should ALWAYS be surrounded by parentheses () because of precedence.  So, in your query, I would do something like this:

 

SELECT * FROM projects WHERE current = '1' AND admin = '1' AND (user1=$user_id OR user2=$user_id OR user3=$user_id OR user4=$user_id OR user5=$user_id OR user6=$user_id OR user7=$user_id OR user8=$user_id OR user9=$user_id OR user10=$user_id OR user11=$user_id OR user12=$user_id OR user13=$user_id OR user14=$user_id OR user15=$user_id OR user16=$user_id OR user17=$user_id OR user18=$user_id OR user19=$user_id OR user20=$user_id)

 

If you omit the parens, the ADMIN='1' AND user1=$user_id would get evaluated first, and I'm pretty sure that's not what you want.  Remember, AND always takes precedence over OR.

 

The second problem is a design issue.  I'm not really sure why you have user1, user2, user3 columns.  If you have multiple users, what you should really have is a separate table that holds all the user IDs, like this:

 

USERS

user_id (primary key)

name

...

 

 

and in your PROJECTS table, have an foreign ID column that references the USERS user_id column, like this:

PROJECTS

current

admin

user_id (foreign key to USERS)

 

 

Then you query would look like this:

SELECT * FROM projects, users WHERE current='1' AND admin='1' and projects.user_id=users.user_id;

 

Hope this helps.

[karatekid36]

I do have a separate user table.  These columns are for all the users who are assigned to the project.  Is there a better way to place in a table the users who are assigned the project?

[Mirkules]

Yes, the way you do that is with yet another table.  Suppose we originally have a USER table, PROJECTS table, and a new table called USER_PROJ_REL table.  The USER_PROJ_REL table will tell us which users belong to which projects by assigning a project ID from the PROJECT table along with a user ID from the USERS table into a new row.

 

So, each project has a unique ID (unique relative to the PROJECT table), each USER has a unique ID, and the USER_PROJ_REL will contain a relation between the two:

 

USERS

user_id (primary key)

...

 

PROJECTS

project_id (primary key)

admin

content

 

 

USER_PROJ_REL

user_id (foreign key to USERS table)

project_id (foreign key to PROJECTS table)

 

 

Suppose you have users with IDs 1,2,3 belonging to a project with ID 999. The USERS_PROJ_REL table would look like this:

USER_ID, PROJECT_ID

1        999

2        999

3        999

 

So you query would then look like this:

 

SELECT projects.*, users.* FROM users, projects, user_proj_rel WHERE admin='1' AND content='1' AND projects.project_id = users_proj_rel.project_id AND users.user_id = user_proj_rel.user_id AND projects.project_id='999';

 

This will give you a list of all users belonging to project 999.

[karatekid36]

Okay I will give this a go and see where it gets me.

[Mirkules]

Let me know if you get stuck.

 

Also, read this link for reference: http://www.databasejournal.com/sqletc/article.php/26861_1474411_1

 

It talks about all the stuff I explained earlier, but much more eloquently. Good Luck!

[karatekid36]

Okay I 100% get how this works and see the reasoning behind it, but how can I insert data into two tables at once.  I have a form where users fill out the project info, and who is assigned to the project, but I do not want to have two forms where two seperate INSERT queries are made.  What is the structure for making two inserts at once?

[revraz]

What do you think you'll need to add into two tables at once?

 

[karatekid36]

The info about the project and then the users' id into the user_proj_rel table.

[revraz]

So you'll actually be inserting a new record into user_proj_rel and projects?

 

If so, this is different data and info, two queries would be the best solution.