seeking guidance to alias correctly

m3rajk posted this at 16:27 — 10th April 2004.

Joined: Jul 2003

i want to search by membername. it'stheonly thing i cannot synch to an id number in this sql query. i build the query in this section of php code

<?php
  else{ # we're authorized to review approvals

    if(isset($_POST['disprev'])){ # we're displaying a review

      $sql=\"SELECT app.username AS 'approver',mem.username AS 'member',mem.uid,hist.appdate,hist.item,hist.ok,hist.reason FROM users AS `mem` RIGHT JOIN apphist AS `hist` ON mem.uid=hist.uid LEFT JOIN users AS `app` ON app.uid=hist.aid WHERE \"; # start the sql statement

      if(isset($_POST['approver'])&&($_POST['approver']!='-')){ # go on from here constructing the statement

        $sql.=\"app.uid='{$_POST['approver']}'\"; # start with approver and add what is wanted

        if(isset($_POST['member'])&&($_POST['member']!='')){ $sql.=\" and mem.username='{$_POST['member']}'\"; }

        if(isset($_POST['month'])&&($_POST['month']!='')&&(isset($_POST['day']))&&($_POST['day']!='')
           &&(isset($_POST['year']))&&($_POST['year']!='')){ # there is a month, day & year

          $date1=\"{$_POST['year']}-{$_POST['month']}-{$_POST['day']} 00:00:00\"; # get day start
          $date2=\"{$_POST['year']}-{$_POST['month']}-{$_POST['day']} 23:59:59\"; # get day end
          $sql.=\" and hist.appdate>'$date1' and hist.appdate<'$date2'\"; } # add the date restriction

        if(isset($_POST['item'])&&($_POST['item']!='-')){ $sql.=\"and hist.item='{$_POST['item']}'\"; } # add item

        if(isset($_POST['ok'])&&($_POST['ok']!='-')){ $sql.=\"and hist.ok='{$_POST['ok']}'\"; } # add approval state

      }elseif(isset($_POST['member'])&&($_POST['member']!='')){ # go on from here constructing sql statment

        $sql.=\" member='{$_POST['member']}'\"; # start with member and add what's wanted

        if(isset($_POST['month'])&&($_POST['month']!='')&&(isset($_POST['day']))&&($_POST['day']!='')
           &&(isset($_POST['year']))&&($_POST['year']!='')){ # there is a month, day & year

          $date1=\"{$_POST['year']}-{$_POST['month']}-{$_POST['day']} 00:00:00\"; # get day start
          $date2=\"{$_POST['year']}-{$_POST['month']}-{$_POST['day']} 23:59:59\"; # get day end
          $sql.=\" and hist.appdate>'$date1' and hist.appdate<'$date2'\"; } # add the date restriction

        if(isset($_POST['item'])&&($_POST['item']!='-')){ $sql.=\"and hist.item='{$_POST['item']}'\"; } # add item

        if(isset($_POST['ok'])&&($_POST['ok']!='-')){ $sql.=\"and hist.ok='{$_POST['ok']}'\"; } # add approval state

      }elseif(isset($_POST['month'])&&($_POST['month']!='')&&(isset($_POST['day']))&&($_POST['day']!='')
           &&(isset($_POST['year']))&&($_POST['year']!='')){ # there is a month, day & year used to start

          $date1=\"{$_POST['year']}-{$_POST['month']}-{$_POST['day']} 00:00:00\"; # get day start
          $date2=\"{$_POST['year']}-{$_POST['month']}-{$_POST['day']} 23:59:59\"; # get day end
          $sql.=\" hist.appdate>'$date1' and hist.appdate<'$date2'\"; # add the date restriction

        if(isset($_POST['item'])&&($_POST['item']!='-')){ $sql.=\"and hist.item='{$_POST['item']}'\"; } # add item

        if(isset($_POST['ok'])&&($_POST['ok']!='-')){ $sql.=\"and hist.ok='{$_POST['ok']}'\"; } # add approval state

      }elseif(isset($_POST['item'])&&($_POST['item']!='-')){ # there is an item selelcted and wanted

        $sql.=\"hist.item='{$_POST['item']}'\"; # so start with the item & optionally add in the approval

        if(isset($_POST['ok'])&&($_POST['ok']!='-')){ $sql.=\"and hist.ok='{$_POST['ok']}'\"; } # status of the item

      }elseif(isset($_POST['ok'])&&($_POST['ok']!='-')){ # just want the approval status

        $sql.=\" hist.ok='{$_POST['ok']}'\"; # so set it to just the approval status

      }else{ # we want to see EVERYTHING

        $sql=\"SELECT app.username AS 'approver', mem.username AS 'member', hist.appdate, hist.item,  hist.ok, hist.reason FROM users AS `mem` RIGHT JOIN apphist AS `hist` ON mem.uid = hist.uid LEFT JOIN users AS `app` ON app.uid = hist.aid;\"; # this is the sql statement
      }

      $apphist=mysql_query($sql, $db); # query the database
?>

this is the line is does not like (first appearence, it appears twice up above)

<?php
        if(isset($_POST['member'])&&($_POST['member']!='')){ $sql.=\" and mem.username='{$_POST['member']}'\"; }
?>

it does not like everything i have tried to use the username from the users table for matching. is that possible? can anyone point me to figuring out how to testing aginst the username???

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.