I have a query that I'm having problems with, and I believe the problem is in my syntax.

Here is the breakdown.

I have a file with 2 selectable options (city / type), which in turn does a search of the database.

1) If only the "city" is set, I need the search to only bring up results matching to the "city" option selected.

2) If only the "type" is set, I need the search to only bring up results matching to the "type" option selected.

3) If both are set, I need the search to bring up results matching both options.

I've never done a multi-option query like this before, but this is what I have right now is this.



When I try and bring up the results in a loop:



I get the following error.

referencing the line of code I included immediately above.

I've tried to echo the $search results of the mysql_query, and nothing is coming up, so I believe it's a problem with the syntax of my query itself.

I'm hoping someone can steer me in the right direction.

This post has been edited by Arhineus: 14 Jun, 2008 - 03:20 PM

Well first of all your query isn't going to work as you intend. Lets say I have a table which has type and city in it like so...



Your query will do this... lets say I chose type 1 and that is it. It will pull up Seattle and Denver. Fine. Lets say I chose just the city and specified Chicago. It will return 2. Fine there as well. The problem arises when I attempt to do both type 1 and specify Seattle.

Since you have type = $type, it will still select Seattle as well as Denver because this clause is satisfied in both cases. Even if you specified Seattle and not Denver. The use of ORs here make it that if one is true the whole thing is true. Since type equals 1 for Denver, it makes Denver a returned result.

So how do we do this for multiple selections? Well I typically like to make a "build as you go" type of query using the server-side language. So I detect if an option was set and if so, append a where clause onto the body of my query. In the end the query will be custom built according to the options they chose.

$querybody = "SELECT * FROM info";

// Both options were selected
if (isset($_POST["type"]) && isset($_POST["city"])) {
     // Error check these, I didn't for sake of brevity
     $type = $_POST["type"];
     $city = $_POST["city"];
     $querybody .= " WHERE type = $type and city ='$city'";
}
// If only type was specified
else if (isset($_POST["type"])) {
     $type = $_POST["type"];
     $querybody .= " WHERE type = $type";
}
// Only if city was specified
else if (isset($_POST["city"])) {
     $city = $_POST["city"];
     $querybody .= " WHERE city = '$city'";
}
else {
     // Do something if nothing was set
}

The result is that $querybody will contain a query where the WHERE clause will be specific to the choices they made. Now again make sure you error check the data supplied by the user for validity before using in these dynamic queries, but you get the idea I hope.

Enjoy!

"At DIC we be dynamic query creating code ninjas... some say we created the earth and the heavens, but never mind that, we just prefer just to be called gods"

Thanks! That helped me understand it a bit better.

I've tried migrating that for my usage, and am testing it like the following.



Regardless of if only one option is selected, the first if statement is always executed, which has me puzzled. I must be missing something very basic, but I've been looking at this file for a couple days now, and I think I'm starting to go cross-eyed...lol

Side Note: In your post, you seam to have PHP-type colouring, etc. How do you do that with BBC Code (I've never seen it before)?

This post has been edited by Arhineus: 14 Jun, 2008 - 05:26 PM

Sorry I missed that you were using drop downs, so both will always be set. What you will need to do is instead of testing if they are just set, test if they are not selecting anything. So for instance if you have a drop down that has a blank option (I assume you do so they can choose NOT to use that option) then you test for the blank instead of just if it is set or not

As for the coloring, when you specify your color code tags you would use [ code=php ]code here[ /code ]

Hopefully I am making sense...



Sorry for the minor mistake. My solution would have worked for other controls besides drop downs.

This post has been edited by Martyr2: 14 Jun, 2008 - 06:12 PM

Oh, ok...now I'm starting to see the light a bit.

Allow me to see if I understand where this is taking me.

When I run this code:



I get the following results (as an example)



If I follow this right, the variable $queryBody, become the actual mysql query (I hope I'm right). Took me a while to get to that point...lol.

So when it comes time to submit the MySQL query, is my call made something like the following?

Yup, just execute the query string at the end of all the options.

Excellent, that works...and I understand it much better now, thank you very much.

1 last question if you don't mind, as I have 1 thing I'm trying to work out.

As an example, I have the following types.

House
Apartment
Condo / Townhouse

For some reason, it seams to include database entries that are entered as "Condo / Townhouse" when you select "House", and visa versa. I'm sure it has something to do with the quotes being used or something, I've always had a tough time keeping them straight.