I'm using php and mysql.  Have the results displaying correctly, but
now i need to be able to let people run the reports based on criteria.
I'm trying to sort by some criterias using sql.  Here's what I have so
far.  Nothing is working.  Only the 3rd case is working correctly.

here's my query so far:
sort = (isset($_POST['sort'])) ? $_POST['sort'] : ""; 
switch ($sort) {
 case 1:
      $var = " ORDER BY numbers.lid ASC,  gcar DESC ";     
     break;
 case 2:
      $var = " ORDER BY numbers.lid, training, IF(goals.goal =
'',0,goals.goal);";
     break;
 case 3:
      $var = " AND numbers.dolAvg >= goals.goal ORDER BY numbers.lid,
goals.goal DESC";
     break;
 default:
     $var = " ORDER BY numbers.lid, gcar DESC ";
     break;
}

$query="select numbers.lid, employees.empNo,
concat(employees.lastName, ', ', employees.firstName) as name,
                numbers.cars, numbers.dolAvg, goals.goal, (dolAvg -
IF(goal IS NULL, 0, goal)) * cars as gcar,
             (numbers.onlineSales + numbers.offlineSales) % cars as locAvg, 
                (dolAvg - ((numbers.onlineSales +
numbers.offlineSales) % cars)) * cars as locCars,
                (dolAvg * cars) as earn, training.training, training.trainDate
             from employees
             INNER JOIN numbers
             ON employees.id= numbers.eid
             LEFT JOIN goals
             ON numbers.eid = goals.eid
             LEFT JOIN training
             ON employees.id = training.eid
                AND training = 'level1'
             where numbers.startDate >= '$sqlDate' 
            AND numbers.endDate <= '$sqlDate1'";
               $query .= $var;

$queryResult = mysql_query($query);

sub queries: this get run after the main query is stored in the $queryResult

  $sql = "SELECT t2.trainDate as t2trainDate FROM employees, training
t2 WHERE t2.training = 'level2'  AND t2.eid = employees.id AND
employees.empNo= " . $dbRow['empNo'];
    $dateResult = mysql_query($sql) or die(mysql_error());
    $dateRow = mysql_fetch_assoc($dateResult);
     
     $sql1 = "select DISTINCT t3.trainDate as remedial from employees,
training t3 where t3.training = 'level3'  AND
                t3.eid = employees.id AND employees.empNo= " . $dbRow['empNo'];
    $dateResult1 = mysql_query($sql1) or die(mysql_error());
    $dateRow1= mysql_fetch_assoc($dateResult1);
     
     $sql2 = "select DISTINCT t4.trainDate as eight from employees,
training t4 where t4.training = 'level4'
                 AND t4.eid = employees.id  AND employees.empNo= " .
$dbRow['empNo'];
    $dateResult2 = mysql_query($sql2) or die(mysql_error());
    $dateRow2= mysql_fetch_assoc($dateResult2);

1) sort by: numbers.lid, then gcar (these could also contain negative
numbers and decimals.)

2) sort by: numbers.lid, then by First Training (this is level1 and
level2, 2 day course) is not null, then where goal is null.

3) sort by: numbers.lid, then those whose "First Training" is null

4) sort by: numbers.lid, then by those who only have 1 date for First Training.

---

Request for Question Clarification by passive-ga on 13 Oct 2004 08:34 PDT

By not working, what precisely is happening? Is it failing to sort,
but still giving you results, or are you getting an error?
What is the environment this is being run in?

---

Clarification of Question by wakk0-ga on 13 Oct 2004 09:21 PDT

okay...for the first case it is order by numbers.lid, but it is not
ordering by the 2nd criteria --> gcar

and for all the other cases I can't seem to figure out how to do it.

It does still display the results