Govnokodu.Net

Больше одного знания и дублирование реализации при получении списка обоев

Исходный код

if ( $_GET['extra'] == "a" )
		{
		$result = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$myresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$countresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' ORDER BY `ID` DESC");
		}
	elseif ( $_GET['extra'] == 4 )
		{
		$result = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `four_three` = '1' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$myresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `four_three` = '1' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$countresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `four_three` = '1' ORDER BY `ID` DESC");
		}
	elseif ( $_GET['extra'] == 16 )
		{
		$result = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `sixteen_ten` = '1' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$myresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `sixteen_ten` = '1' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$countresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `sixteen_ten` = '1' ORDER BY `ID` DESC");
		}
	elseif ( $_GET['extra'] == "n" )
		{
		$result = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `status_NEW` = '1' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$myresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `status_NEW` = '1' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$countresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `status_NEW` = '1' ORDER BY `ID` DESC");
		}
	elseif ( $_GET['extra'] == "m" )
		{
		$result = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `poster` = '".$_SESSION['userid']."' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$myresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `poster` = '".$_SESSION['userid']."' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$countresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `poster` = '".$_SESSION['userid']."' ORDER BY `ID` DESC");
		}
	elseif ( $_GET['extra'] == "m_in_cat" )
		{
		$result = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `poster` = '".$_SESSION['userid']."' AND Category = '".$result_needed['id']."' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$myresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `poster` = '".$_SESSION['userid']."' AND Category = '".$result_needed['id']."' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$countresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `poster` = '".$_SESSION['userid']."' AND Category = '".$result_needed['id']."' ORDER BY `ID` DESC");
		}
	else
		{
		$result = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `Category` = '".$id."' OR `Category` IN (SELECT `id` FROM `Categories` WHERE `Parent` = '".$id."') ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");   <em>///-------------</em>$myresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `Category` = '".$id."' OR `Category` IN (SELECT `id` FROM `Categories` WHERE `Parent` = '".$id."') ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$countresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `approved` = '0' AND `Category` = '".$id."' OR `Category` IN (SELECT `id` FROM `Categories` WHERE `Parent` = '".$id."') ORDER BY `ID` DESC");
		}

	if ( $_GET['extra'] == "a" && $_SESSION['usergroupid'] > 3 )
		{
		$result = mysql_query("SELECT * FROM `Wallpapers` ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$myresult = mysql_query("SELECT * FROM `Wallpapers` ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$countresult = mysql_query("SELECT * FROM `Wallpapers` ORDER BY `ID` DESC");
		}
	elseif ( $_GET['extra'] == 4 && $_SESSION['usergroupid'] > 3 )
		{
		$result = mysql_query("SELECT * FROM `Wallpapers` WHERE `four_three` = '1' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$myresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `four_three` = '1' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$countresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `four_three` = '1' ORDER BY `ID` DESC");
		}
	elseif ( $_GET['extra'] == 16 && $_SESSION['usergroupid'] > 3 )
		{
		$result = mysql_query("SELECT * FROM `Wallpapers` WHERE `sixteen_ten` = '1' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$myresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `sixteen_ten` = '1' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$countresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `sixteen_ten` = '1' ORDER BY `ID` DESC");
		}
	elseif ( $_GET['extra'] == "n" && $_SESSION['usergroupid'] > 3 )
		{
		$result = mysql_query("SELECT * FROM `Wallpapers` WHERE `status_NEW` = '1' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$myresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `status_NEW` = '1' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$countresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `status_NEW` = '1' ORDER BY `ID` DESC");
		}
	elseif ( $_GET['extra'] == "m" && $_SESSION['usergroupid'] > 3 )
		{
		$result = mysql_query("SELECT * FROM `Wallpapers` WHERE `poster` = '".$_SESSION['userid']."' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$myresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `poster` = '".$_SESSION['userid']."' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$countresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `poster` = '".$_SESSION['userid']."' ORDER BY `ID` DESC");
		}
	elseif ( $_GET['extra'] == "m_in_cat" && $_SESSION['usergroupid'] > 3 )
		{
		$result = mysql_query("SELECT * FROM `Wallpapers` WHERE `poster` = '".$_SESSION['userid']."' AND Category = '".$result_needed['id']."' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$myresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `poster` = '".$_SESSION['userid']."' AND Category = '".$result_needed['id']."' ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$countresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `poster` = '".$_SESSION['userid']."' AND Category = '".$result_needed['id']."' ORDER BY `ID` DESC");
		}
	elseif ( $_SESSION['usergroupid'] > 3 )
		{
		$result = mysql_query("SELECT * FROM `Wallpapers` WHERE `Category` = '".$id."' OR `Category` IN (SELECT `id` FROM `Categories` WHERE `Parent` = '".$id."') ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");   <em>///-------------</em>$myresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `Category` = '".$id."' OR `Category` IN (SELECT `id` FROM `Categories` WHERE `Parent` = '".$id."') ORDER BY `ID` DESC LIMIT ".$page*$picsperpage.",$picsperpage");
		$countresult = mysql_query("SELECT * FROM `Wallpapers` WHERE `Category` = '".$id."' OR `Category` IN (SELECT `id` FROM `Categories` WHERE `Parent` = '".$id."') ORDER BY `ID` DESC");
	}

Что не так в исходном коде

Здесь смешаны в одну кучу две принципиально разные ответственности: формирование условия для выборки обоев и формирование трёх видов запросов для выборки трёх видов информации.

С другой стороны, реализация формирования трёх видов запросов повторяется многократно, что затрудняет внесение изменений в эту реализацию.

Нетрудно заметить, что условие для всех трёх видов запросов полностью совпадают, значит, формирование условия нужно спрятать в отдельную функцию. В виде бонуса это позволит избавиться от дублирования реализации запросов.

Также необходимо отвязать источник параметров, от которых зависит формирование условия: тип выборки $_GET['extra'] и группа текущего пользователя $_SESSION['usergroupid']. Отвязывание реализуется с помощью параметров функции.

Из мелких недочётов:

  • проверку принадлежности пользователя к группе стоит реализовать в одном месте
  • подсчёт общего количества результатов запроса необходимо делать через select count(*)
  • при подсчёте общего количества нет необходимости применять сортировку — она не влияет на количество
  • так как реализация $result и $myresult в настоящее время полностью совпадает, можно избавиться от дублирующегося запроса

Вариант рефакторинга

function wallpaperListConditions($list_type, $user_group_id, $user_id, $category_id, $default_category_id)
{
    switch ( $list_type ) {
        case 'a':
            $condition1 = null;
        case 4:
            $condition1 = "`four_three` = '1'";
        case 16:
            $condition1 = "`sixteen_ten` = '1'";
        case 'n':
            $condition1 = "`status_NEW` = '1'";
        case 'm':
            $condition1 = "`poster` = '$user_id'";
        case 'm_in_cat':
            $condition1 = "`poster` = '$user_id' AND Category = '$category_id'";
        default:
            return "`Category` = '$default_category_id' OR `Category` IN (SELECT `id` FROM `Categories` WHERE `Parent` = '$default_category_id')";
    }
    $condition2 = $user_group_id > 3 ? null : "`approved` = '0'";
    $conditions = array_filter([$condition1, $condition2]);
    return implode(' and ', $conditions);
}

Обрати внимание, что параметрам функции розданы более точные названия, отражающие существо значений, в них содержащихся.

$wallpaper_conditions = wallpaperListConditions($_GET['extra'], $_SESSION['usergroupid'], $_SESSION['userid'], $result_needed['id'], $id);
$wallpaper_offset = $page * $picsperpage;

$wallpaper_query = "SELECT * FROM `Wallpapers` WHERE $wallpaper_conditions ORDER BY `ID` DESC LIMIT $wallpaper_offset, $picsperpage";
$wallpaper_count_query = "SELECT COUNT(*) FROM `Wallpapers` WHERE $wallpaper_conditions";

$result = mysql_query($wallpaper_query);
$countresult = mysql_query($wallpaper_count_query);

Теория

Сергей Галанин