MySQL Select All WHERE Statement - Need Help

I need to put together some coding to enable foreman to find the bricklayer they need to use for jobs in different towns and depending on the type of bricklaying to be done.

I setup a MySQL Database Table called USERS which has all the details for each user (which include the people looking for the bricklayers and the bricklayers themselves), such as userid, first_name, last_name etc. There are 7 main types of bricklayers being: traditional, architectural, pave, slab, routing, plank, decorative and thins. I may have to add more later on.

I have another table (or plan on setting it up) called POSTCODES. This has about 1000 town postcodes, a column for each type of bricklayer. The userid of each bricklayer to be used for each postcode is shown in each of the bricklaying types. Only 1 bricklayer for each bricklayer type is to be shown. 开发者_如何转开发

For example the Postcodes table columns and data looks like this (or will):

Postcode, traditional, architectural, pave, slab, routing, plank, decorative, thins
1003, 21, 34, 78, 98, 34, 77, 21
1004, 56, 45, 35, 98, 78, 23, 12
etc

The numbers 21, 34, 78, 98, 34, 77, 21 are the userid's for the bricklayers that are the ones to be used for the different postcodes. For example if I need a thins bricklayer in postcode 1003, the userid for the bricklayer we need would be 21. If I need a pave bricklayer in postcode 1003 then I would need 78. If I need a slab bricklayer in postcode 1004 then I would need 98.

To pull the records of the bricklayer they need to use I was going to use a Select All from Users WHERE userid.users = ???? This is where I got stuck? Basically I need to be able to pull the data for the relevant bricklayer user. In the above examples I would need to pull the data for either user 21, 78 or 98.

The form the people will fill in to find the right bricklayer is as follows:

What type of bricklayer do you need? Radio button or drop down menu with options: traditional, architectural, pave, slab, routing, plank, decorative, thins

What postcode is the job in? Then they enter the postcode.

The next page will show (hopefully :) the details of the person they need to contact and use.

---

From the sounds of it you do not have much experience with PHP OR MySQL. This is SQL 101. It also sounds like your Schema is a complete mess. This is what you should have (basic pseudo code):

TABLE users (
id PK,
name,
...
);

TABLE types (
id PK,
name
);

TABLE postcodes (
code PK,
location_name,
...
);

TABLE bricklayers (
user_id FK to users,
type_id FK to types,
code FK to postcodes
) PK (type_id, code);

Now, The PK in bricklayers will limit the number of types of bricklayers to each postalcode to 1. This means you will only get one of each type per postalcode.

To get a list of users who are bricklayers in a specific postalcode, you run:

SELECT * FROM bricklayers WHERE code = {POSTALCODE};

This will give you the Type_id and the User_id of all bricklayers. You can couple the select with a join so you pull the information from each table instead of just the IDs. But this should get you where you are going.

---

I suggest to use cdburgess's solution. But if you still wanted to use your scheme, you can use query like this

Select * from Users WHERE userid.users IN ( 
    SELECT concat_WS(',', traditional, pave, decorative) as uid FROM `postcodes` WHERE postcode=1003
)

add GROUP BY uid if you need to search over more then one postcode at a time