[RESOLVED] SQL query based on drop down menus

[Maharg105]

Hey guys,

You've all helped me a lot with my current project, so thanks for that. But I've hit a stumbling block and hoped that you kind people might be able to lend me another hand.

I am currently trying to put together a query that searches a table of job vacancies in my database for any records that meet five criteria selected by the user from drop down menus.

Certain jobs can be located in multiple locations and so in the table structure I have fields for location like this;

location_London
location_SouthEast
location_Northwest

and a value of 1 or 0 is assigned to each field depending on whether it is true or false for that job.

Now the code allowing the user to make a location choice looks like this:

<select name="location">
    <option selected value="location_All" class="all">All Locations</option>
    <option value="location_London">London</option>
    <option value="location_SouthEast">South East</option>
    <option value="location_SouthWest">South West</option>
    <option value="location_Midlands">Midlands</option>
    <option value="location_NorthWest">North West</option>
    <option value="location_NorthEast">North East</option>
    <option value="location_EastAnglia">East Anglia</option>
    <option value="location_Yorkshire">Yorkshire</option>
    <option value="location_Scotland">Scotland</option>
    <option value="location_Wales">Wales</option>
    </select>

My problem is that I can't for the life of me figure out how to translate the choice selected by the user into an SQL query that would give me the data I needed. Perhaps I need to rethink my database structure, I'm not sure.

But, any help you could give would be more than gratefully received. If any of that is unclear, let me know I will try to clarify.

Thanks again,

[Bjom]

may I suggest that you change your DB so that you have one field only for the location? In that field store integers: the locationID. Add a new table called location that has at least two fields like so:

locationID | locationName
1 | London
2 | South East

With that setup the whole thing becomes much easier...

[Maharg105]

Thanks for replying so quick Bjom. So with the new table structure in place. If I have a job with, say, four location codes. How would I go about storing them all in the same coloumn so that they are still easily searchable?

When the record is initially input, it is done using a series of checkboxes and my understanding is that if I were to implode this array in order to store it in one coloumn, then I would not be able to query that data without exploding it for every record?

Thanks again, I'm sure I'm missing something quite simple here,

[djjjozsi]

multiple data in a single field is not a good idea.

You have the users table, that field which stores the comma delimitered list is locations_comma, and other fields.

You have another table, location, these table's primary IDs stored in the users table.

How should you build a better design ?

You should make a new table, called

connector_users_location

add two fields into this table (integer types):
users_id And location_id

Means users_id will be equal with users's user_id field, and location_id will be equal with the location table's loc_id

How to select from these 3 tables all connected fields?

SELECT users.user_id,location.loc_id,users.un,users.pass,users.details,location.city
FROM location INNER JOIN
(users INNER JOIN connector_users_location ON users.user_id = connector_users_location.users_id)
ON location.loc_id = connector_users_location.location_id
GROUP BY users.user_id , users.un,users.pass,users.details , location.loc_id , location.city;

Then you can create checkboxes with array variable name to manage user's locations.

[Maharg105]

Thanks for your reply. It's possible that I am not understanding your advice. But my problem is that a single job could have up to 10 location codes applied to it.

My solution to this was to have a field for every location with a 1 or 0 to imply true or false. If I am to have a single location field for each job, then surely I would still need to be storing multiple values within this field?

Sorry if I'm simply being dim, this is the first major barrier to my understanding of PHP / SQL that I've encountered and it's confusing the hell out of me.

Thanks,

[johanafm]

Reread djjjozsi's last post. He explains how you deal with one to many relations in a relational database. More info on the topic can be found at database normalization

[Maharg105]

Thanks for all the advice guys. I've decided to go with a simpler search function this time. But have noted all this down and will try out your suggestions in my next project.