Page 1 of 1

question on querying with aliasing -- limitation not working

Posted: Sun Apr 11, 2004 8:44 pm
by m3rajk
i want to search by membername. it's the only thing i cannot synch to an id number in this sql query.
this is the line is does not like (first appearence, it appears twice up below... well i only modified one appearence from what it was before)

Code: Select all

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???
i build the query in this section of php code (incase it will help to have it)

Code: Select all

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

yes, i did just ask even though it's against my better judgement to even ask here since people seem to skip over what's asked and i've noticed that not just in my posts, but in others such as the person i tried to help before posting this, and point out other things they dislike... i'm going to give the db section here one last chance to actually answer something before writing it off as being a php/client side site that just knit-picks on db questions insstead of answering them. dont' get me wrong, for php and client side this place have proven great in both giving me help and helping me vary things i know and learn to adapt them to suit other people which is why i used to be here, but since asking db qs i've felt like all people do is knit-pick.

Posted: Mon Apr 12, 2004 7:11 am
by Weirdan
As your question involves database would you mind to post your db schema and the queries you ran against it?

Please strip out all that php staff. Echo the $sql variable before passing it to mysql_query function to be sure that your query is correct.

Posted: Mon Apr 12, 2004 9:37 am
by m3rajk
it keeps telling me that it doesn't know the feild when i try to specify a member name
it has changed depending on how i tried to do that.
as long as i dont try to search by member it works perfectly

maybe i wasn't clear: how can i modify that line so that the restriction can work?

Posted: Mon Apr 12, 2004 1:22 pm
by JAM
I imagine that you tried Wierdans tip, echo'ing the $sql as plaintext to debug.

Can you post error-msg and pin-point the line out that it says is ill (if any)? I can't judge by your posts if it just isn't working or if it actually gives you an error but you dont mention it.
If no error is given, bump the error level up abit if not allready done.

Sidenotes, there are a few ways to rewrite your code (even move parts of it) and if interested, pm me for a discussion. It's off topic so I wont bring it up here. (And yes, I think it might be interesting, and no, I'm wont flame you for your spaces ;))

Posted: Mon Apr 12, 2004 3:37 pm
by McGruff
A side note: you should always validate user input and properly escape strings before using them in db queries.