Help with creating query

[lunac]

I've created a form that allows my user to create their own custom reports from the database. Most of the information they want comes from one table. However there is a second table that deals with member payment. I've constructed a query below which will search both tables if the user selects "paid". If they did not select paid, then the second query just searches the one table. As it is, it works perfectly. BUT now I need to add another query if the user selects "unpaid". Basically, if the directory_id is [i]not[/i] found in the payments table, then that's the field I want to return. Of course it is also complicated by the other search items i.e. if they're a board_member and unpaid.

is there any way to do this?

I've attached a screen shot of my form to make it easier to see what I'm doing.

Here's my search code...

$return is the fields they've selected to show in their report

$where and $sort is (obviously) my where clause which is created by which search areas they selected.



[code=php:0]
if(isset($_POST['paid'])) {
$return = "";
foreach($m as $n){
if(isset($_POST[$n])){
$return .= "D." . substr($n, 0, -4) . ",";
}
}
$return .= "P.year,P.date_paid,P.checknum";
$q = lib_executeQuery("SELECT " . $return ." FROM " . $_tables['directory'] . " as D JOIN " . $_tables['member_dues'] . " as P ON P.directory_id = D.directory_id " . $where . " " . $sort);
} else {
$return = "";
foreach($m as $n){
if(isset($_POST[$n])){
$return .=  substr($n, 0, -4) . ",";
}
}
$returnfields = substr($return, 0, -1); // get rid of last comma
$q = mysqlSelect($_tables['directory'], $returnfields , $where . " " . $sort);
}
$numfields = mysql_num_fields($q);
$count = @mysql_num_rows($q);
$x = "";
if($count > 0){
$report .= '<table style="font-size: 10px;"><tr>';
for ($i=0; $i < $numfields; $i++) // Header
{ $report .= '<th>'.mysql_field_name($q, $i).'</th>'; }
$report .="</tr>";
while ($i = @mysql_fetch_assoc($q)){
if(mysql_field_name($q, $i) != ""){
if($x % 2){ $report .= '<tr class="even">'; } else { $report .= '<tr>';}
$report .= '<td>'.implode($i,'</td><td>')."</td></tr>\n";
}
$x ++;
}
$report .= "</table>";
$searchquery = $returnfields . "::" . $where . "::" . $sort;
[/code]

[attachment deleted by admin]

[lunac]

AHA! I figured it out myself :D

If I use a left join I can return all rows whether the directory_id field matches or not. Since I'm looking for payments for this year I can limit the return by payment not equal to this year. Thus my elseif clause is:

[code=php:0]
elseif(isset($_POST['unpaid'])) {
$return = "";
foreach($m as $n){
if(isset($_POST[$n])){
$return .= "D." . substr($n, 0, -4) . ",";
}
}
$where .= " and year != " . date("Y");
$return .= "D.directory_id";
$q = lib_executeQuery("SELECT " . $return ." FROM " . $_tables['directory'] .
                        " as D LEFT JOIN " . $_tables['member_dues'] .
                        " as P ON P.directory_id = D.directory_id " . $where . " " . $sort);
                        // left join to catch all fields
}
[/code]

Thanks all of you who looked, it was a stumper!