Reduce/Optimize repetitive code

Question

I have 3 repetitive sections in my code, how do I refactor them?

The differences are only in SQL query and in the foreach loops.

<?php
$action   = mysql_real_escape_string($_POST["action"]);
$optional = array(
    "reportdate_from" => isset($_POST['reportdate_from']) ? mysql_real_escape_string($_POST['reportdate_from']) : '',
    "reportdate_to" => isset($_POST['reportdate_to']) ? mysql_real_escape_string($_POST['reportdate_to']) : '',
    "list" => isset($_POST['list']) ? mysql_real_escape_string($_POST['list']) : '',
    "customer" => isset($_POST['wystawione_na']) ? mysql_real_escape_string($_POST['wystawione_na']) : '',
    "typ_graph" => $action,
    "typ_listy" => isset($_POST['typ_listy']) ? mysql_real_escape_string($_POST['typ_listy']) : '',
    "chart_data" => isset($_POST['chart_data']) ? mysql_real_escape_string($_POST['chart_data']) : 'date'
);
if (strlen($action) == 0) {
    $final['error'] = "Wybierz z opcji dodatkowych rodzaj wykresu !";
    echo json_encode($final, true);
    die();
}
require_once("../config.php");
if (empty($_SESSION['user'])) {
    header("Location: ../index.php");
    die("Redirecting to ../index.php");
}
require_once('../lib/class_core.php');
$folder = realpath(__DIR__ . '/../' . '/logs') . '/';
$core   = New core();
define("_VALID_PHP", true);


$tags  = explode(',', $_POST['tags_letter']);
$tabelar_tags  = explode(',', $_POST['tabelar_tags']);
$final = array();

if (is_ajax()) {
    if (isset($action) && !empty($action)) {
        switch ($action) {
            case "pie":
            case "stock":
                if (empty($tags[0])) {
                    delivery($optional);
                    delivered($optional);
                    returned($optional);
                }
                if (in_array('W doręczeniu', $tags)) {
                    delivery($optional);
                }
                if (in_array('Zwrócone', $tags)) {
                    returned($optional);
                }
                if (in_array('Doręczone', $tags)) {
                    delivered($optional);
                }
                break;
                case "pdf" :
                    if (empty($tabelar_tags[0])) {

                    }
                    if (in_array('W doręczeniu', $tabelar_tags)) {
                        delivery($optional);
                    }
                    if (in_array('Zwrócone', $tabelar_tags)) {
                        returned($optional);
                    }
                    if (in_array('Doręczone', $tabelar_tags)) {
                        delivered($optional);
                    }
                break;
            default:
                //echo "d";
                exit();
                break;
        }
    }
}




function Check_Date_Time($date_time) {
    if (preg_match("/^(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})$/", $date_time)) {
        return true;
    } else {
        return false;
    }
}
if (strlen($optional["reportdate_from"]) > 0) {
    if (Check_Date_Time($optional["reportdate_from"])) {
    } else {
        echo '<div class="smallheadererror" role="alert"><p style="text-align:center;"><img src="images/delete2.png"> <b>Błąd - zła składnia daty!</b></p></div>';
        $core->writeLog('Próba wpisanie złej daty', $folder . 'analytics.log');
        die();
    }
}
if (strlen($optional["reportdate_to"]) > 0) {
    if (Check_Date_Time($optional["reportdate_to"])) {
    } else {
        echo '<div class="smallheadererror" role="alert"><p style="text-align:center;"><img src="images/delete2.png"> <b>Błąd - zła składnia daty!</b></p></div>';
        $core->writeLog('Próba wpisanie złej daty', $folder . 'analytics.log');
        die();
    }
}

function is_ajax() {
    return isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest';
}

function statistic($optional = null) {
    if ($optional['typ_graph'] == "stock" or $optional['typ_graph'] == "pdf") {
        global $final;
        global $db;
        global $core;
        $clause1   = '';
        $fromi     = $optional['reportdate_from'];
        $toi       = $optional['reportdate_to'];
        $subclause = '';
        $listype   = '';
        $inselect = '';
        if ($optional['typ_listy']) {
            $inselect = 'SELECT id FROM listy WHERE `type`= "'. $optional['typ_listy'] .'"';
        }
        if ($optional['customer']) {
            $inselect = 'SELECT id FROM listy WHERE forwho="'.$optional['customer'].'"';
        }
        if ($optional['list']) {
            $inselect = 'SELECT id from listy where listy.number = "'.$optional['list'].'"';
        }

        if (strlen($fromi) > 0 && strlen($toi) == 0) {
            $clause1 = 'AND numbers.date >  "' . $optional['reportdate_from'] . '"';
        }
        if (strlen($toi) > 0 && strlen($fromi) == 0) {
            $clause1 = 'AND numbers.date <  "' . $optional['reportdate_to'] . '"';
        }
        if (strlen($toi) > 0 && strlen($fromi) > 0) {
            $clause1 = 'AND numbers.date between "' . $optional['reportdate_from'] . '" AND "' . $optional['reportdate_to'] . '"';
        }

        $sth = $db->prepare("SELECT AVG(totalCount) as Avarage, MAX(totalCount) as Maximum FROM (
                             SELECT DATE(date) Date, COUNT(number) totalCOunt FROM  `numbers` WHERE list_id IN ($inselect) and `return`= 0 $clause1 GROUP BY  DATE(date)
                             ) TBL1");
        $sth->execute();
        $result         = $sth->fetch();
        $sth = $db->prepare("SELECT AVG(totalCount) as Avarage, MAX(totalCount) as Maximum FROM (
                             SELECT DATE(date) Date, COUNT(number) totalCOunt FROM  `numbers` WHERE list_id IN ($inselect) and `return`= 1 $clause1 GROUP BY  DATE(date)
                             ) TBL1");
        $sth->execute();
        $result1         = $sth->fetch();
        $sth = $db->prepare("SELECT AVG(totalCount) as Avarage, MAX(totalCount) as Maximum FROM (
                             SELECT DATE(date) Date, COUNT(number) totalCOunt FROM  `numbers` WHERE list_id IN ($inselect) and `return`= 2 $clause1 GROUP BY  DATE(date)
                             ) TBL1");
        $sth->execute();
        $result2         = $sth->fetch();

        $sth1 = $db->prepare("SELECT COUNT( numbers.number ) as Total FROM  `numbers` WHERE list_id IN ($inselect) and `return`= 0 " . $clause1 . "");

        $sth1->execute();
        $sum1    = $sth1->fetch();

        $sth1 = $db->prepare("SELECT COUNT( numbers.number ) as Total FROM  `numbers` WHERE list_id IN ($inselect) and `return`= 1 " . $clause1 . "");

        $sth1->execute();
        $sum2    = $sth1->fetch();

        $sth1 = $db->prepare("SELECT COUNT( numbers.number ) as Total FROM  `numbers` WHERE list_id IN ($inselect) and `return`= 2 " . $clause1 . "");

        $sth1->execute();
        $sum3    = $sth1->fetch();

        $return3['avgInDel'] = $result["Avarage"];
        $return3['maxInDel'] = $result["Maximum"];
        $return3['MINInDel'] = $sum1["Total"];

        $return3['avgDel'] = $result1["Avarage"];
        $return3['maxDel'] = $result1["Maximum"];
        $return3['MINDel'] = $sum2["Total"];

        $return3['avgRet'] = $result2["Avarage"];
        $return3['maxRet'] = $result2["Maximum"];
        $return3['MINRet'] = $sum3["Total"];
        return array_push($final, $return3);
    }

}



statistic($optional);

//SELECT DATE(date) Date, COUNT(number) totalCOunt FROM  `numbers` WHERE list_id IN (SELECT id FROM listy WHERE forwho="6")  GROUP BY  DATE(date)

function delivery($optional = null) {
    global $final;
    global $db;
    global $core;
    $clause1   = '';
    $fromi     = $optional['reportdate_from'];
    $toi       = $optional['reportdate_to'];
    $subclause = '';
    $listype   = '';
    $inselect = '';
    $suborder = '';
    $countern ='';
    $stringe ='';
    if ($optional['chart_data'] == "detailed"){
        $countern ='number totalCOunt';
        $suborder = '';
        $stringe = 'Numer wpisu';
    }
    if ($optional['chart_data'] == "date"){
        $countern ='COUNT(number) totalCOunt';
        $suborder = 'GROUP BY  DATE(date)';
        $stringe = 'Ilosc wpisow';
    }
    if ($optional['typ_listy']) {
        $inselect = 'SELECT id FROM listy WHERE `type`= "'. $optional['typ_listy'] .'"';
    }
    if ($optional['customer']) {
        $inselect = 'SELECT id FROM listy WHERE forwho="'.$optional['customer'].'"';
    }
    if ($optional['list']) {
        $inselect = 'SELECT id from listy where listy.number = "'.$optional['list'].'"';
    }

    if (strlen($fromi) > 0 && strlen($toi) == 0) {
        $clause1 = 'AND numbers.date >  "' . $optional['reportdate_from'] . '"';
    }
    if (strlen($toi) > 0 && strlen($fromi) == 0) {
        $clause1 = 'AND numbers.date <  "' . $optional['reportdate_to'] . '"';
    }
    if (strlen($toi) > 0 && strlen($fromi) > 0) {
        $clause1 = 'AND numbers.date between "' . $optional['reportdate_from'] . '" AND "' . $optional['reportdate_to'] . '"';
    }


    if ($optional['typ_graph'] == "stock") {

        $sth = $db->prepare("SELECT DATE(date) Date, COUNT(number) totalCOunt FROM  `numbers` WHERE list_id IN ($inselect) and `return`= 0 $clause1 GROUP BY  DATE(date)");
        $sth->execute();
        // echo "SELECT DATE(date) Date, COUNT(number) totalCOunt FROM  `numbers` WHERE list_id IN ($inselect) and `return`= 0 $clause1 GROUP BY  DATE(date)";
        $result           = $sth->fetchAll();
        $return1['type']  = 'spline';
        $return1['name']  = 'W doręczeniu';
        $return1['color'] = 'rgba(128,179,236,1)';

        foreach ($result as $row1) {
            $date1             = strtotime($row1['Date']) * 1000;
            $return1['data'][] = array(
                $date1,
                 $row1['totalCOunt']
            );
        }
        return array_push($final, $return1);
    }
    if ($optional['typ_graph'] == "pdf"){
        $sth = $db->prepare("SELECT DATE(date) Date, $countern FROM  `numbers` WHERE list_id IN ($inselect) and `return`= 0 $clause1 $suborder");
         $sth->execute();
        $result           = $sth->fetchAll();
        $return3[] = array('Data',$stringe,'EAN');
        foreach ($result as $row1) {
            $date1             = $row1['Date'];
            $return3[] = array($date1, $row1['totalCOunt'],'<img src="parse/BarGen/generator.php?text='.$row1['totalCOunt'].'" style="width:280px;height:50px;">');
        }
        return array_push($final, $return3);
        // echo json_encode($return3, JSON_NUMERIC_CHECK);
    }


    if ($optional['typ_graph'] == "pie") {
        $sth = $db->prepare("SELECT COUNT( numbers.number ) as inpr FROM  `numbers` WHERE list_id IN ($inselect) and `return`= 0 " . $clause1 . "");
        // echo "SELECT COUNT( numbers.number ) as inpr FROM  `numbers` WHERE list_id IN ($inselect) " . $clause1 . " " . $subclause . "";
        $sth->execute();
        $result    = $sth->fetch();
        $return1[] = 'W doręczeniu';
        $return1[] = $result['inpr'];

        return array_push($final, $return1);
    }
}
function delivered($optional) {
    global $final;
    global $db;
    global $core;
    $clause1   = '';
    $fromi     = $optional['reportdate_from'];
    $toi       = $optional['reportdate_to'];
    $subclause = '';
    $listype   = '';
    $suborder = '';
    $countern ='';
    $stringe ='';
    if ($optional['chart_data'] == "detailed"){
        $countern ='number totalCOunt';
        $suborder = '';
        $stringe = 'Numer wpisu';
    }
    if ($optional['chart_data'] == "date"){
        $countern ='COUNT(number) totalCOunt';
        $suborder = 'GROUP BY  DATE(date)';
        $stringe = 'Ilosc wpisow';
    }
    if ($optional['typ_listy']) {
        $inselect = 'SELECT id FROM listy WHERE `type`= "'. $optional['typ_listy'] .'"';
    }
    if ($optional['customer']) {
        $inselect = 'SELECT id FROM listy WHERE forwho="'.$optional['customer'].'"';
    }
    if ($optional['list']) {
        $inselect = 'SELECT id from listy where listy.number = "'.$optional['list'].'"';
    }
    if (strlen($fromi) > 0 && strlen($toi) == 0) {
        $clause1 = 'AND numbers.date >  "' . $optional['reportdate_from'] . '"';
    }
    if (strlen($toi) > 0 && strlen($fromi) == 0) {
        $clause1 = 'AND numbers.date <  "' . $optional['reportdate_to'] . '"';
    }
    if (strlen($toi) > 0 && strlen($fromi) > 0) {
        $clause1 = 'AND numbers.date between "' . $optional['reportdate_from'] . '" AND "' . $optional['reportdate_to'] . '"';
    }
    if ($optional['typ_graph'] == "stock") {


        $sth = $db->prepare("SELECT DATE(date) Date, COUNT(number) totalCOunt FROM  `numbers` WHERE list_id IN ($inselect) AND `return` = 1  $clause1 GROUP BY  DATE(date)");
        $sth->execute();
        $result           = $sth->fetchAll();
        $return2['type']  = 'column';
        $return2['color'] = '#00FF00';
        $return2['name']  = 'Doręczone';
        foreach ($result as $row2) {
            $date2             = strtotime($row2['Date']) * 1000;
            $return2['data'][] = array(
                $date2,
                 $row2['totalCOunt']
            );
        }

        return array_push($final, $return2);
    }
        if ($optional['typ_graph'] == "pdf"){
        $sth = $db->prepare("SELECT DATE(date) Date, $countern FROM  `numbers` WHERE list_id IN ($inselect) AND `return` = 1  $clause1 $suborder");
        $sth->execute();
        $result           = $sth->fetchAll();
        $return3[] = array('Data',$stringe,'EAN');
        foreach ($result as $row1) {
            $date1             = $row1['Date'];
            $return3[] = array($date1, $row1['totalCOunt'],'<img src="parse/BarGen/generator.php?text='.$row1['totalCOunt'].'" style="width:280px;height:50px;">');
        }
        return array_push($final, $return3);
        // echo json_encode($return3, JSON_NUMERIC_CHECK);
    }

    if ($optional['typ_graph'] == "pie") {
        $sth = $db->prepare("SELECT COUNT( numbers.number ) as deli FROM  `numbers` WHERE list_id IN ($inselect) AND `return`='1' " . $clause1 . "");
        $sth->execute();
        $result2   = $sth->fetch();
        $return2[] = 'Doręczone';
        $return2[] = $result2['deli'];

        return array_push($final, $return2);
    }
}
function returned($optional) {
    global $final;
    global $db;
    global $core;
    $clause1   = '';
    $fromi     = $optional['reportdate_from'];
    $toi       = $optional['reportdate_to'];
    $subclause = '';
    $listype   = '';
    $suborder = '';
    $countern ='';
    $stringe = '';
    if ($optional['chart_data'] == "detailed"){
        $countern ='number totalCOunt';
        $suborder = '';
        $stringe = 'Numer wpisu';
    }
    if ($optional['chart_data'] == "date"){
        $countern ='COUNT(number) totalCOunt';
        $suborder = 'GROUP BY  DATE(date)';
        $stringe = 'Ilosc wpisow';
    }
    if ($optional['typ_listy']) {
        $inselect = 'SELECT id FROM listy WHERE `type`= "'. $optional['typ_listy'] .'"';
    }
    if ($optional['customer']) {
        $inselect = 'SELECT id FROM listy WHERE forwho="'.$optional['customer'].'"';
    }
    if ($optional['list']) {
        $inselect = 'SELECT id from listy where listy.number = "'.$optional['list'].'"';
    }
    if (strlen($fromi) > 0 && strlen($toi) == 0) {
        $clause1 = 'AND numbers.date >  "' . $optional['reportdate_from'] . '"';
    }
    if (strlen($toi) > 0 && strlen($fromi) == 0) {
        $clause1 = 'AND numbers.date <  "' . $optional['reportdate_to'] . '"';
    }
    if (strlen($toi) > 0 && strlen($fromi) > 0) {
        $clause1 = 'AND numbers.date between "' . $optional['reportdate_from'] . '" AND "' . $optional['reportdate_to'] . '"';
    }
    if ($optional['typ_graph'] == "stock") {

        $sth = $db->prepare("SELECT DATE(date) Date, COUNT(number) totalCOunt FROM  `numbers` WHERE list_id IN ($inselect) AND `return` = 2  $clause1 GROUP BY  DATE(date)");
        $sth->execute();
        $result           = $sth->fetchAll();
        $return3['type']  = 'column';
        $return3['color'] = '#FF0000';
        $return3['name']  = 'Zwrócone';
        foreach ($result as $row1) {
            $date1             = strtotime($row1['Date']) * 1000;
            $return3['data'][] = array(
                $date1,
                 $row1['totalCOunt']
            );
        }
        return array_push($final, $return3);
    }

    if ($optional['typ_graph'] == "pdf"){
        $sth = $db->prepare("SELECT DATE(date) Date, $countern FROM  `numbers` WHERE list_id IN ($inselect) AND `return` = 2  $clause1 $suborder");
        $sth->execute();
        $result           = $sth->fetchAll();
        $return3[] = array('Data',$stringe,'EAN');
        foreach ($result as $row1) {
            $date1             = $row1['Date'];
            $return3[] = array($date1, $row1['totalCOunt'],'<img src="parse/BarGen/generator.php?text='.$row1['totalCOunt'].'" style="width:280px;height:50px;">');
        }
        return array_push($final, $return3);
        // echo json_encode($return3, JSON_NUMERIC_CHECK);
    }

    if ($optional['typ_graph'] == "pie") {
        $sth = $db->prepare("SELECT COUNT( numbers.number ) as ret FROM  `numbers` WHERE list_id IN ($inselect) AND `return`='2' " . $clause1 . "");
        $sth->execute();
        $result3   = $sth->fetch();
        $return3[] = 'Zwrócone';
        $return3[] = $result3['ret'];

        return array_push($final, $return3);
    }
}
// if ($optional['typ_graph'] == "stock" or $optional['typ_graph'] == "pie") {
echo json_encode($final, JSON_NUMERIC_CHECK);
// }
?>

Welcome to Code Review! To make life easier for reviewers, please add sufficient context to your question. The more you tell us about what your code does and what the purpose of doing that is, the easier it will be for reviewers to help you. See also this meta question — Simon André Forsberg, Nov 4 at 12:54
Welcome to Code Review! As we all want to make our code more efficient or improve it in one way or another, try to write a title that summarizes what your code does, not what you want to get out of a review. — Simon André Forsberg, Nov 4 at 12:54
You do realize that you're using an unsafe, deprecated extension that is no longer being developed, right? Please stop using mysql_* and switch to its replacements: either PDO or mysqli_* (note the i, which stands for improved) — Elias Van Ootegem, Nov 5 at 12:02

Josay · Answer 1 · 2014-11-04 12:54:33Z

up vote 1 down vote

You have the same pattern isset($_POST['XXXX']) ? mysql_real_escape_string($_POST['XXXX']) : '' happening so many times it might be worth making it a function.

function Check_Date_Time($date_time) can be rewritten :

function Check_Date_Time($date_time) {
    return preg_match("/^(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})$/", $date_time);
}

answered Nov 4 at 12:54

Josay
11.9k1147

That's true about the check date.Is it worth having a function for the $_POST? Im quite new in php optimizing code, I just write and it works ;( – Kavvson Empcraft Nov 4 at 12:56

add a comment |

asked	10 days ago
viewed	46 times
active	10 days ago

current community

your communities

more stack exchange communities

Reduce/Optimize repetitive code

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged php sql or ask your own question.

Hot Network Questions

current community

your communities

more stack exchange communities

Reduce/Optimize repetitive code

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged php sql or ask your own question.

Related

Hot Network Questions