Forum Moderators: coopster
I have a page that displays my "Contacts" in a table that I would like to be able to filter by selecting from drop down boxes on the page. There are 3 of them: Main Category, Status and State. I would like to be able to filter with only one or any combination of the 3.
Here is a link to the page:
<snip>
The drop down boxes are populating successfully, but I would like to know how to create an IF statement near my query that appends the "WHERE" statement based on the choices of the user. I have done a lot of googling and tried a few, but I keep running into roadblocks. Any advice would be appreciated.
Here is the code I used for the boxes:
$main_cat_result = @mysql_query("select * from main_category");
if (mysql_num_rows($main_cat_result) > 0) {
print "<select id= \"main_category_select\" name=\"main_category_select\">";
while ($row = mysql_fetch_array($main_cat_result)) {
print "<option value=\"" . $row['main_category_name'] . "\">" . $row['main_category_name'] . "</option>\n";
}
print "</select>";
}
mysql_free_result($main_cat_result);
$status_result = @mysql_query("select * from status");
if (mysql_num_rows($status_result) > 0) {
print "<select id= \"status_select\" name=\"status_select\">";
while ($row = mysql_fetch_array($status_result)) {
print "<option value=\"" . $row['status_name'] . "\">" . $row['status_name'] . "</option>\n";
}
print "</select>";
}
mysql_free_result($status_result);
$state_result = @mysql_query("select * from state");
if (mysql_num_rows($state_result) > 0) {
print "<select id= \"state_select\" name=\"state_select\">";
while ($row = mysql_fetch_array($state_result)) {
print "<option value=\"" . $row['state_name'] . "\">" . $row['state_name'] . "</option>\n";
}
print "</select>";
}
mysql_free_result($state_result); [/PHP]
Here is the code I have for the query:
$query = ("SELECT *
FROM contacts
LEFT JOIN main_category ON contacts.main_category_id = main_category.main_category_id
LEFT JOIN sub_category ON contacts.sub_category_id = sub_category.sub_category_id
LEFT JOIN contact_info ON contacts.contact_id = contact_info.contact_id
LEFT JOIN status ON contacts.status_id = status.status_id
LEFT JOIN state ON contact_info.state_id = state.state_id
ORDER BY contact_name
");
$result = mysql_query($query)
or print "<p id=\"errorMessage\">Couldn't execute query because: <b>". mysql_error() . "</b>. The query was $query.</p>";
$number_of_rows = mysql_num_rows($result);
[edited by: eelixduppy at 12:40 am (utc) on Oct. 19, 2009]
[edit reason] no URLs, please [/edit]
I`m not 100% certain what you are trying to do. You say to append a WHERE statement, but I don`t see any in your code. Can you give us more information?
dc
When you get into complex form input, you're going to have to revise your thinking a little bit. You want to build the where first, but only if there's something to build.
Whaaaaaat? Hopefully the below will clarify.
The first step is get that statement out of the function and into a variable. You've already done that, but most coders tend to leave them inline. Instead of
$result = mysql_query(select * from table);
the eventual goal is
$result = mysql_query($query);
Second, to execute the most efficient query, you only want what you need. Joins, where statements, order, and limit should only be added as necessary. Without this post getting too long, suffice it to say that in the sample below, the function get_limit_string() (which is hand coded) returns a variety of possible values. Depending on what's in post/get, will return the entire order by and limit string: order by [field name] [direction] sort by [sort field]. So if the "per page" value is 10 and you only have 5 results, there's no need to add a limit to this string. If there's no values that require order or limit, this is a blank or null screen, and has no effect by always including it in the query.
Third, separate the select from the where. Only add joins and a where statement if there is input that requires it.
So you would do something like
var $where=NULL;
var $in = Array('main_category_select','status_select','state_select');
foreach ($in as $input) {
if (isset($_POST[$input]) and ($_POST['input'] != '')) {
// first time through you don't need an AND
if ($where) { $where .= " and"; }
$where .= " $input='" . $_POST[$input] . "'";
// Do not use same names as field names, example only
}
}
Apply the same concepts to joins. If there's no $where, you don't need joins.
$join_string=NULL;
if ($where) {
$joins = Array (
'main_category_select' => ' left join main_category on
contacts.main_category_id = main_category.main_category_id',
// note, previous all on one line
'state_select' => ' left join states_info on states.state_id = contacts.state_id',
'status_select' => ' left join status on status.status_id = contacts.status_id'
);
foreach ($in as $input) {
if (isset($_POST[$input]) and ($_POST['input'] != '')) {
if ($where) { $join_string .= " $joins[$in],"; }
}
}
}
Now assemble it all. If there's no input, you have a simple select * from contacts with the order and limit string which may or may not be blank. If there's a $where, it will contain the where info AND any required joins, otherwise these variables are null and will have no effect on the select.
$limit_string = get_limit_string();
$query = "select * from contacts $join_string";
if ($where) { $query .= " where $where"; }
$query .= " $limit_string";
The resulting $query can be anything now, from a simple select * from table to complex wheres and joins.
A note, always build in () around "or" conditionals, and the above example is indeed complicated by them, but that's a topic for a different thread.
In a complex form with many fields and options (exact match, user-selected per page and order by, etc.), this will be a life saver and a spaghetti-code stopper. Your task is a little different, requiring some joins regardless of input, which you'd append to the string - but you want it as open ended and flexible as possible.
