Mysql 재 질문
본문
public function selectList($search = null, array $addWhereQuery = null, $offset = 0, $limit = 10, $arrInclude = null,$orderByField = null)
{
if ($search) {
list($strWhere, $arrBind) = self::getQueryWhere($search);
$strWhere = (!$strWhere) ? "" : " AND " . $strWhere;
}
if(!$orderByField) {
if($search['sort']) {
$orderByField = $search['sort'];
} else {
$orderByField = 'b.groupNo asc';
//해줘도 모르니 원복 ㅡㅡ $orderByField = 'b.regDt desc';
}
}
$joinGoods = false;
$joinGoodsImage = false;
if ($arrInclude) {
foreach ($arrInclude as $_field) {
switch (substr($_field, 0, 2)) {
case 'gi.' :
if (self::$_cfg['bdGoodsFl'] == 'y') {
$joinGoods = true;
$joinGoodsImage = true;
$gField[] = $_field;
}
break;
case 'g.':
if (self::$_cfg['bdGoodsFl'] == 'y') {
$joinGoods = true;
$gField[] = $_field;
}
break;
case 'm.' :
$mField[] = $_field;
case 'b.' :
$bField[] = $_field;
break;
default :
$bField[] = 'b.' . $_field;
}
}
$boardFields = implode(',', $bField);
if($gField) {
$goodsFields = ',' . implode(',', $gField);
}
} else {
if(self::$_bdId == 'TNBreview' || self::$_bdId == 'TNBqna'){
$boardFields = implode(',', DBTableField::setTableField('reviewTableBd', null, ['apiExtraData','contents'], 'b'));
}else{
$boardFields = implode(',', DBTableField::setTableField('tableBd', null, ['apiExtraData','contents'], 'b'));
};
//$boardFields.=',SUBSTRING(b.contents,1,1000) as contents';
$boardFields .= ', b.contents as contents';
if (self::$_cfg['bdGoodsFl'] == 'y') {
$joinGoods = true;
$joinGoodsImage = true;
$arrGoodsField = ['scmNo','goodsNm','goodsPrice','brandCd','makerNm','originNm','imagePath','imageStorage','onlyAdultFl','onlyAdultImageFl','fixedPrice'];
$goodsFields = ','.implode(',', DBTableField::setTableField('tableGoods', $arrGoodsField, null, 'g'));
}
if (self::$_cfg['goodsType'] == 'order') {
$joinExtra = true;
$arrExtraField = ',goodsNoText,orderGoodsNoText';
}
}
$boardField = 'b.sno,b.regDt,b.modDt,' . $boardFields . $goodsFields . $arrExtraField;
$strSQL = " SELECT " . $boardField . " FROM " . DB_BD_ . self::$_bdId . " as b ";
if ($joinGoods) {
$strSQL .= " LEFT OUTER JOIN " . DB_GOODS . " as g ON b.goodsNo = g.goodsNo ";
if (\Request::getSubdomainDirectory() !== 'admin') {
//접근권한 체크
if (gd_check_login()) {
$strGoodsWhere = ' (g.goodsAccess !=\'group\' OR (g.goodsAccess=\'group\' AND FIND_IN_SET(\''.\Session::get('member.groupSno').'\', REPLACE(g.goodsAccessGroup,"'.INT_DIVISION.'",","))) OR (g.goodsAccess=\'group\' AND !FIND_IN_SET(\''.\Session::get('member.groupSno').'\', REPLACE(g.goodsAccessGroup,"'.INT_DIVISION.'",",")) AND g.goodsAccessDisplayFl =\'y\'))';
} else {
$strGoodsWhere = ' (g.goodsAccess=\'all\' OR (g.goodsAccess !=\'all\' AND g.goodsAccessDisplayFl =\'y\'))';
}
//성인인증안된경우 노출체크 상품은 노출함
if (gd_check_adult() === false) {
$strGoodsWhere .= ' AND (onlyAdultFl = \'n\' OR (onlyAdultFl = \'y\' AND onlyAdultDisplayFl = \'y\'))';
}
$strWhere .=" AND (b.goodsNo = '0' OR (b.goodsNo > 0 AND " .$strGoodsWhere."))";
}
}
if ($joinExtra) {
$strSQL .= " LEFT OUTER JOIN " . DB_BOARD_EXTRA_DATA . " as bet ON bet.bdId = '".self::$_bdId."' AND b.sno = bet.bdSno ";
}
$strSQL .= " WHERE 1 " . $strWhere;
if ($addWhereQuery) {
foreach($addWhereQuery as $key=>$val) {
if(!$val){
unset($addWhereQuery[$key]);
}
}
$strSQL .= ' AND ' . implode(' AND ', $addWhereQuery);
}
$limit = $limit ?? 10;
$strSQL .= " ORDER BY ".$orderByField." , groupThread "."LIMIT {$offset},{$limit}";
$result = self::$_db->slave()->query_fetch($strSQL, $arrBind);
if ($joinGoodsImage) {
foreach ($result as $row) {
if ($row['goodsNo']) {
$goodsNos[] = $row['goodsNo'];
}
}
if ($goodsNos) {
$sql = "SELECT gi.goodsNo,gi.imageSize,gi.imageNo,gi.imageName FROM " . DB_GOODS_IMAGE . " as gi WHERE gi.goodsNo in (" . implode(",", $goodsNos) . ") AND gi.imageKind='main' "; //리스트이미지로
$goodsImageData = self::$_db->query_fetch($sql);
foreach ($goodsImageData as $_goodsData) {
$arrGoodsJoinData[$_goodsData['goodsNo']] = $_goodsData;
}
foreach ($result as &$row) {
foreach ($gField as $_key=>$val) {
$row[$val] = $arrGoodsJoinData[$row['goodsNo']][$val];
}
$row['imageSize'] = $arrGoodsJoinData[$row['goodsNo']]['imageSize'];
$row['imageNo'] = $arrGoodsJoinData[$row['goodsNo']]['imageNo'];
$row['imageName'] = $arrGoodsJoinData[$row['goodsNo']]['imageName'];
$row['cateCd'] = $arrGoodsJoinData[$row['goodsNo']]['cateCd'];
}
}
}
return $result;
}
order by ((g.fixedPrice-g.goodsPrice)/fixedPrice)*100 asc
추가하고 싶은데 어디에 추가해야 할 까요?
!-->
답변 2
retrun $result;
를 하니
$result 가 생성되기 전에 해야죠
그런데 위 문장을 보면
$strSQL .= " ORDER BY ".$orderByField." , groupThread "."LIMIT {$offset},{$limit}";
$result = self::$_db->slave()->query_fetch($strSQL, $arrBind);
// 이런게 이미 있네요
// $result 바로 위의 문장을 대치한후에 Limit를 추가해야 할것같아보입니다.
order by ((g.fixedPrice-g.goodsPrice)/fixedPrice)*100 asc
추가하고 싶은데 어디에 추가해야 할 까요?
==
order by g.fixedPrice asc
만 해도 충분하지 않을까요?
계산 하던 안 한던 순서는 같을 테니까요.
답변을 작성하시기 전에 로그인 해주세요.