can someone please tell me why this query does not work properlY ? it is supposed to run a query based on 3 selections. if 1 of 3 choices is made then it searches based on what results contain the 1 value, if 2 are chosen - then only those with BOTH are to be queried, and if all 3 are chosen then only those with ALL 3 values are to be returned. this here only runs the first "if" clause and will only work if all 3 are chosen. please help.
<?php
$db = @pg_connect("","","","","funds");
if (!$db) {echo "Could not connect to the Funding database"; exit;}
$query = "Select * from scholarship";
if (!empty($level) AND !empty($field) AND !empty($agency_name))
{
$query.=" where level='$level' and field='$field' and agency_name='$agency_name'";
}
elseif (!empty($level) AND !empty($field) AND empty($agency_name))
{
$query.=" where level='$level' and field='$field'";
}
elseif (!empty($level) AND empty($field) AND !empty($agency_name))
{
$query.=" where level='$level' and agency_name='$agency_name'";
}
elseif (empty($level) AND !empty($field) AND !empty($agency_name))
{
$query.=" where field='$field' and agency_name='$agency_name'";
}
elseif (!empty($level) AND empty($field) AND empty($agency_name))
{
$query.=" where level='$level'";
}
elseif (empty($level) AND !empty($field) AND empty($agency_name))
{
$query.=" where field='$field'";
}
elseif (empty($level) AND empty($field) AND !empty($agency_name))
{
$query.=" where agency_name='$agency_name'";
}
$result = @pg_exec($db, $query);
$color1 = "#EEEEF8";
$color2 = "#ffffff";
$numrows = @pg_numrows($result);
if (!$numrows) {echo"<b>No results matched your query, try another set of selections.</b>"; exit;}
$row=0;
$row_num=1;
do
{
$color = ($row_num%2) ? $color2 : $color1;
$myrow = @pg_fetch_row ($result,$row);
if (!$myrow) {echo"<b>No results matched your query, try another set of selections.</b>"; exit;}
print ("<tr><td width=\"10\" bgcolor=".$color." valign=\"top\">
<FONT COLOR=\"#003399\">$row_num</font></td>
<td bgcolor=".$color." width=\"400\" valign=\"top\">
<a href=\"scholview.php?schol_id=".$myrow[0]."\">$myrow[1]</a></td>
<td bgcolor=".$color." width=\"120\" valign=\"top\">$myrow[2]</td >
<td bgcolor=".$color." width=\"175\" valign=\"top\">$myrow[3]</td >
</tr>\n");
$row++;
$row_num++;}
while ($row < $numrows);
print ("</table><br>\n");
pg_close($db);
?>
i also tried this sql query to perform the same task but it seems to be running the OR case - as if i just said, "select * from scholarship where level='$level' OR field='$field' OR agency_name='$agency_name'"
$query = "Select * from scholarship where (level='$level' and field='$field' and agency_name='$agency_name')
OR (level='$level' and field='$field') OR (level='$level' and agency_name='$agency_name') OR (field='$field' and agency_name='$agency_name')
OR (level='$level') OR (field='$field') OR (agency_name='$agency_name')";
in this case i thought that maybe i had to define that the values not chosen were nnull or default but that didnt make any difference.
