Database searching problems

[NickyDee]

I run a website about medical school admissions and am constructing a database of admissions statistics (just basic stuff like how many applications each school got, how many people they interviewed and how many places they actually had).

I've got the basics working at www.study-medicine.co.uk/viewdatabase2.php

The results can be searched by a number of things, firstly basic stuff like ascending and descending and secondly more advanced like for example they can search only the schools that accept students who've had to resit exams. There are 5 such search checkboxes. These are working to... sort of.

... The problem occurs when a user selects two checkboxes, say for example clearing and resits. It should show only those universities which accept people BOTH with resits and by something called clearing. Instead though it shows universities which accept people by EITHER resits or clearing.

Heres the code i'm using:

$query="SELECT Name, Applied, Interviewed, Offered, Accepted, Male, Female, Biology, Chemistry, UKCAT, Tests, Resits, Clearing FROM medical_school";
  if ($SortOrder == 1) { // Order by Applied
  $query .= " ORDER BY Applied DESC";
} elseif ($SortOrder == 2) { // Order by Interviewed
  $query .= " ORDER BY Interviewed DESC";
} elseif ($SortOrder == 3) { // Order by Offered
  $query .= " ORDER BY Offered DESC";
} elseif ($SortOrder == 4) { // Order by Accepted
  $query .= " ORDER BY Accepted DESC";  

} elseif ($SortOrder == 5) { // Order by Interviewed
  $query .= " ORDER BY Applied ASC";
} elseif ($SortOrder == 6) { // Order by Interviewed
  $query .= " ORDER BY Interviewed ASC";
} elseif ($SortOrder == 7) { // Order by Interviewed
  $query .= " ORDER BY Offered ASC";
} elseif ($SortOrder == 8) { // Order by Interviewed
  $query .= " ORDER BY Accepted ASC";
} elseif ($SortOrder == 9) { // Order by Interviewed
  $query .= " ORDER BY Name ASC";
} elseif ($ch1 == checked) { // Order by resits only
  $query .= " WHERE Resits = 'Yes' ";
} elseif ($ch2 == checked) { // Order by clearing only
  $query .= " WHERE Clearing = 'Yes' ";
} elseif ($ch3 == checked) { // Order by biology only
  $query .= " WHERE Biology = '-' ";
} elseif ($ch4 == checked) { // Order by chemistry only
  $query .= " WHERE Chemistry = '-' ";
} elseif ($ch5 == checked) { // Order by ukcat only
  $query .= " WHERE UKCAT = '-' ";
} else { // Default, alphabetical
  $query .= " ORDER BY Name DESC ";
}

Ignore the sortorder entries, those are for the asc/descending bits. The problem is with the $ch1-5

Just as a side note the way it works is the 5 checkboxes named ch1-5 return a value of a-e respectively (e.g. ch1=a, ch2=b, ch3=c etc.) if they are ticked. If the page detects the a-e then it ticks the box so the user knows its selected and it activates the query append shown above.

I'm really grateful for any help anyone can give me, i'm absolutely stumped! Thanks

Nicky

[bpat1434]

First thing I notice is some wrong SQL syntax. The WHERE clauses come before any ORDER BY clause.

Secondly, since you're working with two completely different item, you need two different if() sets: one for ordering, the other for the WHERE clause.

So this entire chunk:

elseif ($ch1 == checked) { // Order by resits only
  $query .= " WHERE Resits = 'Yes' ";
} elseif ($ch2 == checked) { // Order by clearing only
  $query .= " WHERE Clearing = 'Yes' ";
} elseif ($ch3 == checked) { // Order by biology only
  $query .= " WHERE Biology = '-' ";
} elseif ($ch4 == checked) { // Order by chemistry only
  $query .= " WHERE Chemistry = '-' ";
} elseif ($ch5 == checked) { // Order by ukcat only
  $query .= " WHERE UKCAT = '-' ";
}

needs to be moved up above the ORDER BY if()elseif{} statements.

Now, to deal with multiple checkboxes, just make each one an if() statement. Here's how I'd do it:

$where = ' WHERE ';
$hasClause = false;

if($ch1 == checked) { // Order by resits only
  if(empty($hasClause)) { $where .= "AND "; }
  $where .= "Resits = 'Yes' ";
  $hasClause = true;
}

if ($ch2 == checked) { // Order by clearing only
  if(empty($hasClause)) { $where .= "AND "; }
  $where .= "Clearing = 'Yes' ";
  $hasClause = true;
}

if ($ch3 == checked) { // Order by biology only
  if(empty($hasClause)) { $where .= "AND "; }
  $where .= "Biology = '-' ";
  $hasClause = true;
}

if ($ch4 == checked) { // Order by chemistry only
  if(empty($hasClause)) { $where .= "AND "; }
  $where .= "Chemistry = '-' ";
  $hasClause = true;
}

if ($ch5 == checked) { // Order by ukcat only
  if(empty($hasClause)) { $where .= "AND "; }
  $where .= "UKCAT = '-' ";
  $hasClause = true;
}

/* Order by stuff.... */
if ($SortOrder == 1) { // Order by Applied

Hope that helps.

[Weedpacket]

The main problem is that "elseif" is being used - which means that once one of the conditions is satisfied, none of the rest will be checked.

if(foo)
{
    //wibble
}
elseif(bar)
{
   //womble
}

is equivalent to

if(foo)
{
    // wibble
}
else
{
    if(bar)
    {
          //womble
    }
}

"bar" will only be tested if "foo" is false.