Where Clause to search "ALL" from fields in multiple tables

whererclause search all multiple tables

[icon65]

Hello, this is my first post...

Here is what I need to do:
I have a search form and the fields are for multiple mysql tables. Many of these fields are dropdowns that are populating dynamically from the db. I need to write a where clause to allow the visitor to choose "all" in one or more dropdowns.

I can write the query for multiple tables but I am stuck on how to write a where clause that will both allow searching "all" and also for multiple tables.

What I have so far will let me do the "all" searching but I don't know how to build the query to use it on mutiple tables. Here is what I wrote for a where clause:

$whereclause;
while (list($key, $value) = each ($HTTP_POST_VARS)) {
if (($value != "all") && ($key != "Submit") && ($key != "results")) {
$whereclause .= $key . "='" . $value . "' AND ";
}
}
if ($whereclause != "") {
$whereclause = substr($whereclause, 0, -4);
}
$whereclause = "WHERE " . $whereclause;

And here is my query:

$result = mysql_query("SELECT r.ResortName, r.City, r.State, r.Country, l.UsageType, l.ListingType, l.Bedrooms, l.Bathrooms FROM tblResort r, tblListing l $whereclause") or die(mysql_error());

I need to have all of the above fields be searchable by choosing a value in the dropdown OR selecting "All" in any of them.

Any help would be greatly appreciated. I have been struggling with this for two days!

[icon65]

Okay, after trial and error, I have gotten it to work 95%. The only catch is if I do not choose something in the first dropdown (ResortID) then the where clause fails.

Here is my 95% working code:

$whereclause;
while (list($key, $value) = each ($HTTP_POST_VARS)) {
if (($value != "all") && ($key != "submit") && ($key != "results") && ($key != "searchtype")) {
$whereclause .= $key . "='" . $value . "' AND ";
}
}
if ($whereclause != "") {
$whereclause = substr($whereclause, 0, -4);
}
$whereclause = "WHERE tblResort." . $whereclause . " AND tblListing." . $whereclause;

$result = mysql_query("SELECT tblResort.ResortID, tblResort.Country, tblResort.State, tblResort.City, tblListing.Bedrooms, tblListing.Bathrooms, tblListing.UsageType, tblListing.ListingType FROM tblResort JOIN tblListing ON tblResort.ResortID=tblListing.ResortID $whereclause") or die(mysql_error());

[icon65]

Sorry, the error MySQL is giving is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND tblListing.' at line 1

[coopster]

Welcome to WebmasterWorld, icon65.

$HTTP_POST_VARS

This has been deprecated in PHP, you really should be using the latest $_POST superglobal array now.

One of the easiest ways to figure out where your query is failing is to dump the statement to the browser prior to executing it (or better yet, dump the statement to the browser upon failure of executing it, but only if you are running in a test environment, not LIVE)

[icon65]

Thanks coopster.
Yeah, I have changed it to $_POST, that was from an old statement I had written. I have tried dumping the statement to the browser but it doesn't help. I know what the problem is, I just can't figure out how to solve it. If you look at my second post above, the problem is that I am trying to include two tables into my where clause, which would be simple if I wasn't trying to allow "ALL" in my dropdown choices. But trying to write the where clause to include two tables AND allow the "all" choice is where the failure happens.

[icon65]

Nobody has a solution?
I'm not opposed to rewriting the where clause completely if there is a better solution to this issue. So any solutions are welcome!

[LifeinAsia]

It sounds like you'll need to add some more logic in building your WHERE statement depending on what is selected.

It might help a bit if you provided the options (or at least some of them) in your drop-down form selects.

[icon65]

All the options are dynamically populated from the various DB tables. One is Country (distinct list of 239 countries), on is states (distinct list of US states) one is Cities (distinct list of only cities in the db), bedrooms (distinct list of about 6 options), etc...

[LifeinAsia]

Then post a sample of the resulting HTML code. NOT all 239 countries- just a couple plus the line that includes the "all" select.

[icon65]

<form action="search.php" method="post">
<input type="hidden" name="searchtype" value="Advanced">
/////
<select name="ResortID" style="width:200px;">
<option value="all">All Resorts</option>
<option value="815">Alhambra @ Poinciana</option>
<option value="748">Allegro Resort</option>
<option value="623">Ameila Resort</option>
<option value="200">American Resorts International</option>
<option value="365">Americano Resort</option>
<option value="47">Apollo Park</option>
/////
<select name="Country" style="width: 200px;">
<option value="all">All Countries</option>
<option value="United States">United States</option>
<option value="">----------------------</option>
<option value="Antigua">Antigua</option>
<option value="Aruba">Aruba</option>
<option value="Austria">Austria</option>
<option value="Bahamas">Bahamas</option>
<option value="Barbados">Barbados</option>
/////
<select name="State" style="width: 200px;">
<option value="all">All States</option>
<option value="Arizona">Arizona</option>
<option value="Arkansas">Arkansas</option>
<option value="California">California</option>
<option value="Colorado">Colorado</option>
/////
<select name="City" style="width: 200px;">
<option value="all">All Cities</option>
<option value="Acapulco">Acapulco</option>
<option value="Alberta">Alberta</option>
<option value="Alexandria">Alexandria</option>
<option value="Altamonte Springs">Altamonte Springs</option>
/////
<select name="Bedrooms" style="width: 75px;">
<option value="all">All</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
/////
<select name="Bathrooms" style="width: 75px;">
<option value="all">All</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
/////
<select name="UsageType" style="width: 75px;">
<option value="all">All</option>
<option value="Annual">Annual</option>
<option value="Bi-Annual">Bi-Annual</option>
/////
<select name="ListingType" style="width: 75px;">
<option value="all">All</option>
<option value="Rental">Rental</option>
<option value="Sale">Sale</option>
<option value="Rental/Sale">Rental/Sale</option>

[LifeinAsia]

So it looks like the resulting SQL WHERE clause will be something like:

WHERE tblResort.ResortID=ALL

This will obviously throw an error since "ALL" is not an integer (I assume that tblResort.ResortID is an INT field).

So you need to change your logic in PHP when you build your SQL statement.

For example, if "ALL" is selected, then don't include that constraint in your WHERE clause.

One last bit- if all of your selected options are "ALL" then you may end up with an incorrect WHERE statement, which will also throw an error. So you either have to not include a WHERE clause in that case, or you can include a catchall (like "1=1").

[icon65]

I think I get most of what you are saying and by itself, it makes sense. Part of where things get screwy is that since there are multiple tables, the common field between tblResort and tblListing is ResortID. So I have to have both: tblResort.ResortID='ResortID' and tblListing.ResortID='ResortID'. And yes, that is an INT

[LifeinAsia]

Well, that should be an INNER JOIN regardless:
SELECT r.ResortName, r.City, r.State, r.Country, l.UsageType, l.ListingType, l.Bedrooms, l.Bathrooms
FROM tblResort r INNER JOIN tblListing l ON r.ResortID=l.ResortID
$whereclause

[icon65]

Still haven't gotten this working. Part of the problem is that with any JOIN, you have to have a common field to join on and if the ResortID is not used because the user chooses "All" for Resorts, then the constructor for the join is missing.

Make sense? Any fresh ideas of how I can search multiple tables, with "All" as a choice in each dropdown?

[LifeinAsia]

It doesn't matter for the JOIN. The JOIN is what you use to show how 2 tables are related- nothing to do with any WHERE clauses outside the JOIN- see the example I posted above.
"FROM tblResort r INNER JOIN tblListing l ON r.ResortID=l.ResortID" will work regardless of whether or not 1 or more (or all) ResortID is picked.

[edited by: LifeinAsia at 4:25 pm (utc) on Oct. 22, 2008]

[icon65]

Thanks for all your feedback on this Lifeinasia!
I completely changed the code and here is what I got working perfectly:

$mysqlSelect = "SELECT tblListing.ListingID, tblListing.ResortID, tblListing.Bedrooms, tblListing.Bathrooms, tblListing.isFloat, tblListing.WeeksAvail, tblListing.UsageType, tblListing.ListingType, tblResort.City, tblResort.State FROM tblListing LEFT JOIN tblResort ON tblListing.ResortID=tblResort.ResortID ";
$mysqlSelect .= "WHERE 1=1";
if($ResortID) $mysqlSelect .= "tblResort.ResortID='" . $ResortID . "'";
if($Country) $mysqlSelect .= " AND tblResort.Country='" . $Country . "'";
if($State) $mysqlSelect .= " AND tblResort.State='" . $State . "'";
if($City) $mysqlSelect .= " AND tblResort.City='" . $City . "'";
if($Bedrooms) $mysqlSelect .= " AND tblListing.Bedrooms='" . $Bedrooms . "'";
if($Bathrooms) $mysqlSelect .= " AND tblListing.Bathrooms='" . $Bathrooms . "'";
if($UsageType) $mysqlSelect .= " AND tblListing.UsageType='" . $UsageType . "'";
if($ListingType) $mysqlSelect .= " AND tblListing.ListingType='" . $ListingType . "'";
if($WeekColor) $mysqlSelect .= " AND tblListing.WeekColor='" . $WeekColor . "'";
if($WeeksAvail) $mysqlSelect .= " AND tblListing.WeeksAvail='" . $WeeksAvail . "'";
if($isFloat) $mysqlSelect .= " AND tblListing.isFloat='" . $isFloat . "'";
if($isLocked) $mysqlSelect .= " AND tblListing.isLocked='" . $isLocked . "'";
$mysqlSelect .= "ORDER BY tblResort.City, tblResort.State ASC ";

[LifeinAsia]

if($ResortID) $mysqlSelect .= "tblResort.ResortID='" . $ResortID . "'";

I think that should be:
if($ResortID) $mysqlSelect .= " AND tblResort.ResortID='" . $ResortID . "'";

Also, if tblResort.ResortID is an INT field, you can get rid of the single quotes on around . $ResortID .

[icon65]

ResortID is the first field so it would not need to have the "AND" in front of it. As far as the single quotes around the integer, you are correct, thanks!

[LifeinAsia]

But you have $mysqlSelect .= "WHERE 1=1"; right before it.
So if ResortID 100 is selected, the resulting part of the string would be:
"WHERE 1=1tblResort.ResortID=100"
(note that you don't have any space after "WHERE 1=1" or before the "tblResort.ResortID='" substrings) and it should trow an error when you try to run the statement against the database.

[icon65]

Ahhhh, you're correct! Thanks for catching that! You rock LifeinAsia!

[LifeinAsia]

One more item. In the form selects you previously posted, you have "ALL" as a choice for most of them. How are you accounting for that if someone selects "ALL" in one or more of them?

[icon65]

Sorry, I meant to put that in my post with the solution.
I changed it from "all" to "" (empty). That way the "if($key)" only executes if something is chosen.