1

I've been trying to figure this out for a few days now, but haven't been able to find a solution. Most likely due to the fact that I don't think I'm asking the right question.

Here is goes...

I am trying to create a search on my website of a list of attorneys.

Table 1
ID (primary)
Name
Category1 (fkey)
Category2 (fkey) 
Category3 (fkey)
Category4 (fkey)
Category5 (fkey)
Location1 (fkey)
Location2 (fkey)
Location3 (fkey)
Location4 (fkey)
Location5 (fkey)

Table 2 - Locations
ID (primary)
Name 

Table 3 - Categories
ID (primary)
Name

So Attorneys have multiple categories and multiple locations -> one to Many Relationship with both Table 2 and Table 3

First Question: Do I have Table 1 set up correctly? Do I need to have multiple location and category columns (ie location1, location2, location3, etc...) Or am I complicating this?

Next...

I want a checkbox style search on my site. A user can search by Location and/or by Category. And with checkbox they can choose multiple locations and/or categories. The checkbox values are the IDs of the locations and categories (not the names)

So three ways this can happen.

  1. Search by locations ONLY
  2. Search by categories ONLY
  3. Search by categories within locations

I have two problems.

  1. I can get scenarios 1 & 2 to work, but only if ONE checkbox is selected.
  2. I have no idea how to even begin to get scenario 3 to work.

Here is what I have for scenario 1 & 2

$AttorneyLocation = $_POST['AttorneyLocation'];

for ($i="0"; $i<count($AttorneyLocation); $i++) {
    if (!is_numeric($AttorneyLocation[$i])) {
        $AttorneyLocation[$i]="";
    }
    if (empty($AttorneyLocation[$i])) {
        unset($AttorneyLocation[$i]);
    }
}

$AttorneyLocation = implode (" OR ", $AttorneyLocation);

$sqlCommand = "SELECT att_id, att_name, att_logo, att_addy, att_town, att_profile_url FROM attorneys WHERE att_location1='$AttorneyLocation' OR att_location2='$AttorneyLocation' OR att_location3='$AttorneyLocation' OR att_location4='$AttorneyLocation' OR att_location5='$AttorneyLocation'";

Again, this works but only when ONE checkbox is selected, it fails when two or more are selected. Basically it seems to only search the LAST checkbox that has been selected, ignoring the ones before it.

For scenario 3 - Again I'm just not sure where to start, how do I join together the category search within the location search?

If someone can point me in the right direction that would be great, thanks so much! This is my first try creating something like this!

Here is my form code if necessary - all created dynamically

 <input type='checkbox' name='AttorneyCategory[]' value='$cat_id'> $category<br />
 <input type='checkbox' name='AttorneyLocation[]' value='$loc_id'> $location<br />
Improve this question
1
  • A quick note - when using for loops you should not use $i<count($AttorneyLocation) as the second expression as the count will be evaluated on each iteration. You should assign the value of count to a variable first and use that in your for loop. It is not significant in this case but it is a bad habit to get into. Commented Mar 10, 2012 at 19:39

3 Answers 3

Reset to default
4

As pointed out by @JonathanLeffler your structure for Table 1 is a perfect example of what not to do. Your Category* and Location* fields should be removed from Table 1 and replaced with two many-to-many tables - attorney_locations(attorney_id, location_id) and attorney_categories(attorney_id, category_id).

Start by amending your db structure and then we can address the other issues.

Next round -

<?php

foreach ($_POST['AttorneyLocation'] AS $key => $val)    {
    if (is_numeric($val)) {
        $_POST['AttorneyLocation'][$key] = intval($val);
    } else {
        unset($_POST['AttorneyLocation'][$key]);
    }
}

$AttorneyLocations = implode (',', $_POST['AttorneyLocation']);

foreach ($_POST['AttorneyCategory'] AS $key => $val)    {
    if (is_numeric($val)) {
        $_POST['AttorneyCategory'][$key] = intval($val);
    } else {
        unset($_POST['AttorneyCategory'][$key]);
    }
}

$AttorneyCategories = implode (',', $_POST['AttorneyCategory']);

$sqlCommand = 'SELECT DISTINCT att_id, att_name, att_logo, att_addy, att_town, att_profile_url FROM attorneys ';

if ($AttorneyLocations) {
    $sqlCommand .= 'INNER JOIN attorney_locations ON attorneys.att_id = attorney_locations.attorney_id ';
}
if ($AttorneyCategories) {
    $sqlCommand .= 'INNER JOIN attorney_categories ON attorneys.att_id = attorney_categories.attorney_id ';
}

$sqlCommand .= 'WHERE 1=1 ';

if ($AttorneyLocations) {
    $sqlCommand .= "AND attorney_locations.location_id IN ($AttorneyLocations) ";
}
if ($AttorneyCategories) {
    $sqlCommand .= "AND attorney_categories.category_id IN ($AttorneyCategories)";
}
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

With the new table setup, searching Locations and/or Categories works perfectly, thanks. But, when I search both Location and Category at the same time, it fails. I'm guessing I need to have a if ($AttorneyLocations && $AttorneyCategories) statement, but I don't know how to write the sqlcommand to query my db.
What do you get when you print the query output when searching by both categories and locations? It worked for me on my test db yesterday.
I have two questions about your code, mainly syntax. Why, in your sqlcommand, do you use single quotes (') and in the other you use double quotes ("). And what is the Where 1=1 statement for. Thanks,
I have used double quotes in the last two statements that concatenate to $sqlCommand because the $AttorneyLocations and $AttorneyCategories variables need to be expanded. If I used single quotes they would be string literals - PHP Manual. The use of WHERE 1=1 is a simple way of knowing that the following conditions can be ANDed to the WHERE clause instead of using an array and implode.
2

You should print out what you get from your implode():

$AttorneyLocation = implode (" OR ", $AttorneyLocation);

It won't be valid SQL syntax, because you need it to read something like:

location = 'location1' OR location = 'location2' OR ...

So, you are not generating valid SQL, as you would surely see if you printed the SQL.

Your Table1 is a relational disaster. CategoryN or LocationN columns are SQL 'code smells'. It isn't clear what that table means; is it documenting 'AND' or 'OR' connections between the categories and locations, and is Category5 associated only with Location5 or is it also relevant to Location1? Without knowing what the data is supposed to mean, we can't reliably provide the correct design.

Given that each attorney may practice up to 5 categories of law, and may practice in up to 5 locations, then a better design for the tables is probably:

Attorney
ID           (pkey)
Name
...other details...

AttorneyCategory
AttorneyID   (fkey - references Attorney)
Category     (fkey - references Category)
(AttorneyID, Category) (pkey)

AttorneyLocation 
AttorneyID   (fkey - references Attorney)
Location     (fkey - references Location)
(AttorneyID, Category) (pkey)

This will actually be significantly easier to query, too, because you won't have to look up the same location name in each of five different columns, nor look up the same category in each of five different columns.

Improve this answer

3 Comments

Each Category can be associated with ANY location. An attorney can have 5 different types of law that they practice in 5 different locations.
I've changed my SQL command to include "IN" method and removed the OR from my implode and just inserted a comma. Scenario 1 & 2 are working correctly now. But I'm still working on Scenario 3 - How do you suggest I "clean" up my tables to make this work and look better?
Thank you, I didn't realize there was a many to many relationship.
-1

Well, since you are using PHP i just recommend you do an IF statement for each of the filters you want, maybe a few to make 2 or 3 filters work together. your database is set correctly, just create PHP if's with different queries and it should work.

Improve this answer

3 Comments

could you give me an example of using IF statements to make multiple filters work together?
Well, it should go something like: if(statment_one&&statment_two){ //here goes your query that two filters apply }
The database design is not correct by any stretch of the imagination. This is an obvious violation of 1NF (first or minimal normal form). I am not a normalisation zealot but this is a very poor design.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.