question on querying with aliasing -- limitation not working
Posted: Sun Apr 11, 2004 8:44 pm
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)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)
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.
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']}'"; }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 databaseyes, 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.