Optimising multiple queries into a single query

<?php
// Importing configurations
require_once '../../config.php';
require_once '../../define.php';

$date = $_GET['selected'];

$todayStartdate = mktime(0, 0, 0, date('m'), date('d'), date('Y'));
$todayStartdate = date('Y-m-d H:i:s',$todayStartdate);
$todayEnddate = mktime(23, 59, 59, date('m'), date('d'), date('Y'));
$todayEnddate = date('Y-m-d H:i:s',$todayEnddate);
//$myesterday = strtotime('-1 day', $today);
//$yesdate = date('Y-m-d',$myesterday);
//$yesterdate = date('d.m.Y', strtotime($yesdate));

$yesStartdate = mktime(0, 0, 0, date('m'), date('d')-1, date('Y'));
$yesStartdate = date('Y-m-d H:i:s',$yesStartdate);
$yesEnddate = mktime(23, 59, 59, date('m'), date('d')-1, date('Y'));
$yesEnddate = date('Y-m-d H:i:s',$yesEnddate);



$last7daysStartdate = mktime(0, 0, 0, date('m'), date('d')-6, date('Y'));
$last7daysStartdate = date('Y-m-d H:i:s',$last7daysStartdate);
$last7daysEnddate = mktime(23, 59, 59, date('m'), date('d'), date('Y'));
$last7daysEnddate = date('Y-m-d H:i:s',$last7daysEnddate);


$thismonthStartdate = mktime(0, 0, 0, date('m'), 1, date('Y'));
$thismonthStartdate = date('Y-m-d H:i:s',$thismonthStartdate);
$thismonthEnddate = mktime(23, 59, 59, date('m'), date('t'), date('Y'));
$thismonthEnddate = date('Y-m-d H:i:s',$thismonthEnddate);


echo "----------Yesterday StartTime--------";
echo "<br/>";
echo $yesStartdate;
echo "<br/>";
echo "----------Yesterday EndTime---------";
echo "<br/>";
echo $yesEnddate;
echo "<br/>";

echo "----------last 7 days StartTime--------";
echo "<br/>";
echo $last7daysStartdate;
echo "<br/>";
echo "----------last 7 days EndTime---------";
echo "<br/>";
echo $last7daysEnddate;
echo "<br/>";


echo "----------this month StartTime--------";
echo "<br/>";
echo $thismonthStartdate;
echo "<br/>";
echo "----------this month EndTime---------";
echo "<br/>";
echo $thismonthEnddate;
echo "<br/>";


if($date == '')
{
    init();
}
else
{
    if($date!="")
    {
        //echo $date;

        getYesterday($date,$yesStartdate,$yesEnddate,$last7daysStartdate,$todate,$last7daysEnddate,$weekdate);


    }

}

function getYesterday($date,$yesStartdate,$yesEnddate,$last7daysStartdate,$last7daysEnddate,$todate,$weekdate)
{


    $arr = array();
    $orderArray = array();
    $orderArray1 = array();
    $orderArray2 = array();
    $arr2 = array();
    $arr3 = array();
    $users = array();
    $users1 = array();
    $wrkdate = array();
    $wrkdate1 = array();
    $arr4 = array();
    $wrkresults = array();
    //$currMonth = date('n');
    //$currYr = date('Y');

    $html1 = '';

    $workorders = mysql_query("select distinct customfieldoption.customvalue,cwd_user.display_name,worklog.timeworked,jiraissue.issuestatus,jiraissue.ID,worklog.STARTDATE from customfieldvalue,customfieldoption,worklog,cwd_user,jiraissue where customfieldvalue.STRINGVALUE=customfieldoption.ID and cwd_user.user_name = worklog.AUTHOR and worklog.issueid = jiraissue.ID and customfieldvalue.ISSUE = jiraissue.ID");

    while($workorderfetch = mysql_fetch_row($workorders))
    {

        $wrklogdate = $workorderfetch[5];
        $timestamp = strtotime($wrklogdate);
        $workdonedt = date('Y-m-d H:i:s',$timestamp);
        $workdonedt1 = date('d.m.Y',strtotime($workdonedt));


        $workdonedtmon = date('n',strtotime($workdonedt));
        $workdonedtyr = date('Y',strtotime($workdonedt));

        

        $workorderfetch[0] = strstr($workorderfetch[0],'AB');
        $workorderfetch[0] = substr($workorderfetch[0], 0, strpos($workorderfetch[0], '"'));


        if($date == yesterday)
        {
            //echo "--------inside yesterday--------------";
            
            if($workdonedt >= $yesStartdate && $workdonedt <= $yesEnddate)
            {
                
                $yes = true;
                
                /*echo "<br/>";
                echo "<br/>";
                echo "----------workdone date flag condition true yesterday---------";
                echo "<br/>";
                echo $workdonedt . "<br/>";
                echo "<br/>";*/
                
                
                

            }
            /*else 
            {
                echo "<br/>";
                echo "<br/>";
                echo "----------workdone date condition false not yesterday---------";
                echo "<br/>";
                echo $workdonedt . "<br/>";
            }*/
            
        }
        else if($date == today)
        {
            if($workdonedt >= $todayStartdate && $workdonedt <= $todayEnddate)
            {
                
                $yes = true;

                
            }
            


        }

        else if($date == last7days)
        {
            if(($workdonedt >= $last7daysStartdate) && ($workdonedt <= $last7daysEnddate))
            {
                
                
                $yes = true;


                
                    
            }
            
        }

        else if($date == thismonth)
        {
            if(($workdonedt >= $thismonthStartdate) && ($workdonedt <= $thismonthEnddate))
            {
                $yes = true;

            
            }
            
        }


        else
        {
            $date = date('Y-m-d H:i:s', strtotime($date));
            $currDate = date('Y-m-d H:i:s');
            if(($workdonedt >= $date) && ($workdonedt <= $currDate))
            {
                $yes = true;

                
            }
            


        }


    //print_r($wrkdate);
    
        
        $revworkorder = pg_query("select DISTINCT f_3,f_62syeafsajlualych,f_1,docid,f_8,f_10,f_67sxctfaajtclljya from l_261 where f_3 = '".$workorderfetch[0]."' and f_1<>1 and state=4 and trashed=FALSE and deactivated=FALSE");
        //echo "select DISTINCT f_3,f_62syeafsajlualych,f_1,docid,f_8,f_10,f_67sxctfaajtclljya from l_261 where f_3 = '".$workorderfetch[0]."' and f_1<>1 and state=4 and trashed=FALSE and deactivated=FALSE";
        if($revworkorderfetch = pg_fetch_row($revworkorder))
        {

            
            if($revworkorderfetch[6] == 'f')
            {
                $fixfetch = pg_query("select sum(f_8),sum(f_10) from l_261 where f_3 like 'RE%' and trashed='f' and deactivated='f' and f_64sytaamtlyytacph = ".$revworkorderfetch[3]);
                if($rdbfixed = pg_fetch_row($fixfetch)) {
                    $sumNetPrice = $rdbfixed[0];
                    $sumGrossPrice = $rdbfixed[1];
                }
                $diffPriceNet = ($revworkorderfetch[4] - $sumNetPrice);
                $diffPriceGross = ($revworkorderfetch[5] - $sumGrossPrice);
                    
                if($diffPriceNet <= 0 && $diffPriceGross <= 0)
                {
                    
                        if($yes)
                        {
                            $orderArray[] = $revworkorderfetch[0];
                            $wrkNotbillableresults1[$revworkorderfetch[0]] = getWorkorderDetails($revworkorderfetch[0],$revworkorderfetch[2],$revworkorderfetch[1]);
                            //$wrkIssues[$revworkorderfetch[0]] = getWrkissueDetails($revworkorderfetch[0]);
                        }
                        
                        $ordersArray[] =  $revworkorderfetch[0];
                        $wrkNotbillableresults[$revworkorderfetch[0]] = getWorkorderDetails($revworkorderfetch[0],$revworkorderfetch[2],$revworkorderfetch[1]);
                        
                        
                    
                    
                }
                    
                if($diffPriceNet > 0 && $diffPriceGross > 0)
                {

                    
                        if($yes)
                        {
                        $orderArray1[] = $revworkorderfetch[0];
                        $wrkFixedresults1[$revworkorderfetch[0]] = getWorkorderDetails($revworkorderfetch[0],$revworkorderfetch[2],$revworkorderfetch[1]);
                        $wrkIssuesFixed[$revworkorderfetch[0]] = getWrkissueDetails($revworkorderfetch[0]);
                        }
                        $ordersArray1[] =  $revworkorderfetch[0];
                        $wrkFixedresults[$revworkorderfetch[0]] = getWorkorderDetails($revworkorderfetch[0],$revworkorderfetch[2],$revworkorderfetch[1]);
                    
                        
                }
                    
            }
            if($revworkorderfetch[6] == 't')
            {
                
                    if($yes)
                    {
                    $orderArray2[] = $revworkorderfetch[0];
                    $wrkByhourresults1[$revworkorderfetch[0]] = getWorkorderDetails($revworkorderfetch[0],$revworkorderfetch[2],$revworkorderfetch[1]);
                    $wrkIssuesByHour[$revworkorderfetch[0]] = getWrkissueDetails($revworkorderfetch[0]);
                    }
                    $ordersArray2[] = $revworkorderfetch[0];
                    $wrkByhourresults[$revworkorderfetch[0]] = getWorkorderDetails($revworkorderfetch[0],$revworkorderfetch[2],$revworkorderfetch[1]);
                   
                        
                    
                
            }


            
        }
            

    }
    
    
    
    $html1 .='<div class="outer red" id="hour">';
    $html1 .='<div class="box-title">';
    $html1 .='<h2 class="red">By Hour</h2>';
    $html1 .='</div>';
    
    
    $div ='<div class="container-outer">';
    $html1 .= getOngoingWorkDetails($wrkByhourresults1,$div,$wrkIssuesByHour);
    
    
    $graywrkByhour = array_diff_assoc($wrkByhourresults,$wrkByhourresults1);
    $div1 ='<div class="container-outer gray">';
    $html1 .= getOngoingWorkDetails($graywrkByhour,$div1,$wrkIssuesByHour=0);
    
    $html1 .='</div>';
    $html1 .='<div class="outer orange" id="fixed">';
    $html1 .='<div class="box-title">';
    $html1 .='<h2 class="orange">Fixed</h2>';
    $html1 .='</div>';
    
    $div ='<div class="container-outer">';
    $html1 .= getOngoingWorkDetails($wrkFixedresults1,$div,$wrkIssuesFixed);
    
    $graywrkFixed = array_diff_assoc($wrkFixedresults,$wrkFixedresults1);
    $div1 ='<div class="container-outer gray">';
    $html1 .= getOngoingWorkDetails($graywrkFixed,$div1,$wrkIssuesFixed=0);
    $html1 .='</div>';
    $html1 .='<div class="outer green" id="notbillable">';
    $html1 .='<div class="box-title">';
    $html1 .='<h2 class="green">Not Billable</h2>';
    $html1 .='</div>';
    
        $div ='<div class="container-outer">';
        $html1 .= getOngoingWorkDetails($wrkNotbillableresults1,$div,$wrk=0);
    
        $graywrkNotbillable = array_diff_assoc($wrkNotbillableresults,$wrkNotbillableresults1);
        $div1 ='<div class="container-outer gray">';
        $html1 .= getOngoingWorkDetails($graywrkNotbillable,$div1,$wrk=0);
        $html1 .='</div>';
    
    echo $html1;
    


}



function getWorkorderDetails($wrkorderid,$coid,$btitle)
{
    global $yesStartdate,$yesEnddate,$last7daysStartdate,$last7daysEnddate,$todayStartdate,$todayEnddate,$thismonthStartdate,$thismonthEnddate;
    $arr1 = array();
    $coname = pg_query("select f_1 from l_256 where docid=".$coid);
    
    if($conamefetch = pg_fetch_row($coname))
    {
        $arr1[$wrkorderid]['compname'] = $conamefetch[0];
    
    }
    $arr1[$wrkorderid]['billtitle']= $btitle;
    
    $sqlIssue = mysql_query("select max(worklog.STARTDATE) from customfieldvalue,customfieldoption,jiraissue,worklog where customfieldoption.customvalue LIKE '%\\"1\\":\\"" . $wrkorderid . "\\",%' and customfieldvalue.STRINGVALUE=customfieldoption.ID and jiraissue.ID = customfieldvalue.ISSUE and worklog.issueid = jiraissue.ID group by customfieldoption.customvalue");    
    if($wrklogdates = mysql_fetch_row($sqlIssue))
    {
        
        if($wrklogdates[0] >= $yesStartdate && $wrklogdates[0] <= $yesEnddate)
        {
            $arr1[$wrkorderid]['wdate'] = 'Yesterday';
        }
        else if($wrklogdates[0] >= $todayStartdate && $wrklogdates[0] <= $todayEnddate)
        {
            $arr1[$wrkorderid]['wdate'] = 'Today';
        }
        else 
        {    
        
            $arr1[$wrkorderid]['wdate'] = $wrklogdates[0];
        
        
        }
        
    }
    
    return $arr1;
}


function getWrkissueDetails($wrkordid)
{
    
    $userArray = array();
    
    $sqluserdetails = mysql_query("select customfieldoption.customvalue,worklog.issueid,cwd_user.display_name,jiraissue.pkey,worklog.timeworked from customfieldvalue, customfieldoption, worklog,cwd_user,jiraissue
where customfieldoption.customvalue LIKE '%\\"1\\":\\"" . $wrkordid . "\\",%' and customfieldvalue.STRINGVALUE = customfieldoption.ID and worklog.issueid = customfieldvalue.ISSUE and worklog.issueid = jiraissue.ID
and worklog.AUTHOR = cwd_user.user_name and customfieldvalue.ISSUE = worklog.issueid");
    
    
    
    if($userfetchDetails = mysql_fetch_row($sqluserdetails))
    {
        $userfetchDetails[4] = $userfetchDetails[4]/3600;
        $userArray[$userfetchDetails[2]] += $userfetchDetails[4];
    }
    
    return $userArray;
}






function getOngoingWorkDetails($wrkDetails,$divs,$userDetails)
{
    
    
      $html2 = '';
    
    foreach($wrkDetails as $orderid=>$wrkValues)
    {
        foreach($wrkValues as $wrkValues1)
        {    
        $html2 .= $divs;
        $html2 .='<span class="f_3">';
        $html2 .= $orderid . '</span>';
        $html2 .='<span class="date">' . $wrkValues1['wdate'] . '<br/>(Last Worklog)</span><br/><br/>';
        $html2 .='<div class="title">' . $wrkValues1['billtitle'] . '</div>';
        $html2 .='<div class="address">' . $wrkValues1['compname'] . '</div>';
        $html2 .='</div>';
        
        
    }
    }
    
    return $html2;
}
function init()
{


    ?>
<style type="text/css">
#time a:link {
    text-decoration: none;
    color: #003366;
    background: transparent;
}

#time a:visited {
    text-decoration: none;
    color: #003366;
    background: transparent;
}

#time a:hover {
    text-decoration: none;
    color: #F80687;
    background: transparent;
}

#time a:focus {
    text-decoration: none;
    color: #F80687;
    background: transparent;
}

#time a:active {
    text-decoration: none;
    color: #F80687;
    background: transparent;
}
</style>

<link
    type="text/css" rel="stylesheet"
    href="<?php echo HOOKS_BASE_URL; ?>/default.css" />


<link rel="stylesheet"
    href="http://reports/jquery.fancybox.css"
    type="text/css" media="screen" />


<div id="scroll-table-wrapper-full" class="scroll-table-wrapper-full">
    <div class="outer violet" id="time">
        <div class="box-title">
            <h2 class="violet">Total</h2>
        </div>
        <a href="Javascript:void(0);" onclick="contentloader('yesterday');">Yesterday</a><br />
        <a href="Javascript:void(0);" onclick="contentloader('today');">Today</a><br />
        <a href="Javascript:void(0);" onclick="contentloader('last7days');">Last
            7 days</a><br /> <a href="Javascript:void(0);"
            onclick="contentloader('thismonth');">This month</a><br /> <input
            type="text" value="" id="since" name="since" /> <input type="button"
            value="post" name="post"
            onclick="contentloader(document.getElementById('since').value);" />
    </div>
    <div id="container"></div>

</div>

<script>
contentloader('yesterday');

function includeJs(jsFilePath) {
    var js = document.createElement("script");

    js.type = "text/javascript";
    js.src = jsFilePath;
    

    document.body.appendChild(js);
}

includeJs("http://reports/jquery-latest.min.js");
includeJs("http://reports/jquery.fancybox.pack.js");


            
function contentloader(x){
    
    var xhr = new XMLHttpRequest();
    var selected;
    //alert(x);
    
    xhr.onreadystatechange = function(){
      if ( xhr.readyState == 4 ) {
        if ( xhr.status == 200 ) {
            returnVal=xhr.responseText;
               
            document.getElementById('container').innerHTML=returnVal;

            jQuery(document).on('click', '.various', function(e) {
            e.preventDefault();
            var Href = 'http://hooks/reports/userdetails.php?workorderid=' + $(this).attr("href");
            jQuery.fancybox.open({
                href: Href,
                type: 'iframe',
            });
        });        
                                            
        }
        else {
            
            document.getElementById('container').innerHTML='Error'; 
            
        }
      }
      else
      {
          
          document.getElementById('container').innerHTML='<div id="loading-image" style="height:450px; text-align:center; padding-top:220px;"><img src="<?php echo HOOKS_BASE_URL; ?>/reports/images/loading.gif" alt="Loading..." /></div>';
      }
    }; 
    xhr.open("GET", '<?php echo HOOKS_BASE_URL; ?>'+'test.php?selected='+x, true);
    xhr.send();
    
    
}


</script>




<?php 


}
?>

What exactly is the problem you’re having with that code?

As suggested by:

Quote Originally Posted by StarLion View Post
Okay… I’m gonna narrow my response here to checking the date. There’s quite a few issues visible in that code (For starters, you might want the guys over in the Databases forum to take a look at those queries.)

$date is the input selected, and should be one of the following: today, yesterday, last7days, thismonth. End date is Today unless “yesterday” is chosen, in which case it’s yesterday.
$wrklogdate is the timestamp in your database row.

aaach!! mine eyes, zey are bleedink!! and ze goggles, zey do nossink!!

What queries? Could you post only the queries, and explain what you want to do with them and what problem you’ve encountered?

@kk0123; you should be aware that the mysql_* extension is deprectated as of version 5.5.0 of PHP and you should be migrating over to either the mysqli_extenstion or PDO.

Also in your code there is no sanitizing of the user submitted data (in the case of the code from $_GET), any data that does not come from the applications own database should be considered suspect until it has been sanitized and proven to be clean. Have a read of this page from the PHP manual which explains what SQL injection is.