Help me clean up my code please. I have multiple SQLServer queries and would like to see a better way.

[kat35601]

I have 31 sql server queries that are all different but on the same server.Some of the queries are similar but I don't want to combine them I have included some of my code and it is very ugly to me. How can I shorten it? Can I use one connect to the server and run all the queries? what is the best way? at the moment all these queries are on different pages and the main page call's each one. I have included a snippet of that as well. for each one of these there is a corresponding detail report as you can see in the snippet I was hoping to do away with the 31 summary pages and combine those into one page. 

 

<?php
$name = 'removed';
$user = 'removed';
$password = 'removed';

function shipped()
{
    echo "Shipped" . "<br/>";
    global $name, $user, $password;
    $connect = odbc_connect($name, $user, $password);
    if (!$connect)
    {
        exit("Connection Failed: " . $connect);
    }

    $shipped = "select impPartClassID, sum(omlOrderQuantity) as Qty from m1_kf.dbo.SalesOrders
left outer join m1_kf.dbo.SalesOrderLines on omlSalesOrderID=ompSalesOrderID
left outer join m1_kf.dbo.parts on impPartID=omlPartID
where ompClosed !=-1 and omlPartID not like '%DC%' and impPartClassID!='EXP'

group by impPartClassID
order by impPartClassID
";

    $result = odbc_exec($connect, $shipped);
    if (!$result)
    {
        exit("Error in SQL");
    }
    while ($row = odbc_fetch_array($result))
    {
        echo "<tr><td>" . $row['impPartClassID'] . "</td>";
        echo "<td>" . number_format($row['Qty'], 0) . "<br></td></tr>";
    }

    odbc_close($connect);
};
/* Production */

function productionday()
{

    global $name, $user, $password;
    $grandTotal = 0;
    $num2 = 0;
    $num3 = 0;

    $connect = odbc_connect($name, $user, $password);
    if (!$connect)
    {
        exit("Connection Failed: " . $connect);
    }

    $productionday = "	SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, tdate)) AS DATE
	,ISNULL(trans, 'NON') AS trans
	,ISNULL(Transactions.item, Snumbers.item) AS item
	,count(serial) AS qty
	,tuser
	,sum(ISNULL(M1_KF.dbo.PartUnitSalePrices.imhUnitSalePrice,0)) as TotalPrice
	FROM Orbedata.dbo.SNumbers
  	LEFT OUTER JOIN OrbeData.dbo.Transactions ON snum = serial 
	left JOIN M1_KF.dbo.PartUnitSalePrices ON Orbedata.dbo.transactions.item = M1_KF.dbo.PartUnitSalePrices.imhPartID
	WHERE CONVERT(DATE, tdate) = CONVERT(DATE, Getdate())
		AND trans = 'fpr'
	GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, tdate))
	,ISNULL(trans, 'NON')
	,ISNULL(Transactions.item, Snumbers.item)
	,tuser
	,Orbedata.dbo.transactions.qty
	order by tuser,item


 ";

    $result = odbc_exec($connect, $productionday);
    if (!$result)
    {
        exit("Error in SQL");
    }

    while (odbc_fetch_row($result))
    {

        $scanner = odbc_result($result, "tuser");
        $Item = odbc_result($result, "item");
        $Qty = odbc_result($result, "qty");
        $Price = odbc_result($result, "TotalPrice");

        $num = number_format($Price, 2);
        $grandTotal += $Price;
        $num2 = number_format($grandTotal, 2);
        $num3 += $Qty;

    }
    odbc_close($connect);
    echo "Day" . "<br/>";
    echo "$num3";
    echo "<br>";
    echo "$num2";
    echo "<br>";

    #-------------------------------------------------
    

    function productionnight()
    {

        global $name, $user, $password;

        $grandTotal1 = 0;

        $num4 = 0;
        $num5 = 0;
        $num6 = 0;
        $productionnight = "SELECT


-- ISNULL(trans, 'NON') AS trans
 ISNULL(Transactions.item, Snumbers.item) AS item
 ,count(serial) AS qty
 ,tuser
 ,sum(ISNULL(M1_KF.dbo.PartUnitSalePrices.imhUnitSalePrice,0)) as TotalPrice
 FROM Orbedata.dbo.SNumbers
 LEFT OUTER JOIN OrbeData.dbo.Transactions ON snum = serial
 left JOIN M1_KF.dbo.PartUnitSalePrices ON Orbedata.dbo.transactions.item = M1_KF.dbo.PartUnitSalePrices.imhPartID
 WHERE  tdate >= DATEADD(minute, 1020, DATEADD(day, DATEDIFF(day, '', GETDATE())-1, '')) and  tdate <= DATEADD(HH, 5, CONVERT(DATETIME, CONVERT(date, GETDATE())))


     AND trans = 'fpr'
 GROUP BY

-- ISNULL(trans, 'NON')
 ISNULL(Transactions.item, Snumbers.item)
 ,tuser
 ,Orbedata.dbo.transactions.qty
 order by tuser,item";

        $connect = odbc_connect($name, $user, $password);

        $result = odbc_exec($connect, $productionnight);
        if (!$result)
        {
            exit("Error in SQL");
        }

        while (odbc_fetch_row($result))
        {

            $scanner = odbc_result($result, "tuser");
            $Item = odbc_result($result, "item");
            $Qty = odbc_result($result, "qty");
            $Price = odbc_result($result, "TotalPrice");

            $num4 = number_format($Price, 2);
            $grandTotal1 += $Price;
            $num5 = number_format($grandTotal1, 2);
            $num6 += $Qty;

        }
        odbc_close($connect);

        echo "NightShift" . "<br/>";
        echo "$num6";
        echo "<br>";
        echo "$num5";
    }

}
/* Call */
shipped();
productionday();
productionnight();
?>

The snippet

      <div class="row">
            <div class="col-md-2 ">
                <button type ="button" class="btn btn-info btn-lg btn-block ">
                    <a href='/dashboard/kf_orders_shipped_detail.php'>
                        <font font color="black">Shipped!
                <br>
                <?php include 'bs_kf_orders_shipped_sum_tom.php';?>
              </font>
                    </a>
                </button>
            </div>
            <div class="col-md-2 ">
                <button type="button" class="btn-danger btn-lg btn-block ">
                    <a href='/dashboard/kf_production_price1.php'>
                        <font font color="black">Production!
                <br>
                <?php include 'bs_kf_production_price_both.php';?>
              </font>
                    </a>
                </button>
            </div>
            <div class="col-md-2 ">
                <button type= "button" class="btn btn-primary btn-lg btn-block ">
                    <a href='/dashboard/kf_orders_entered_by.php'>
                        <font font color="black">Today's Orders!
                <br>
                <?php include 'bs_kf_orders_entered_by.php';?>
              </font>
                    </a>
                </button>
            </div>
            <div class="col-md-2 ">
                <button type ="button" class="btn btn-success btn-lg btn-block ">
                    <a href='/dashboard/kf_orders_open_detail.php'>
                        <font font color="black">Open!
                <br>
                <?php include 'bs_kf_orders_open_sum_tom.php';?>
              </font>
                    </a>
                </button>
            </div>
            <div class="col-md-2 ">
                <button type= "button" class="btn btn-warning btn-lg btn-block">
                    <a href='/dashboard/kf_employee_totalsshipp.php'>
                        <font font color="black">ON Hold!
                <br>
                <?php include 'bs_kf_employee_totals.php';?>
              </font>
                    </a>
                </button>
            </div>
        </div>
        <!-- Row 2 -->
        <div class="row">
            <div class="col-md-2 ">
                <button type ="button" class="btn btn-info btn-lg btn-block ">
                    <a href='/dashboard/kf_orders_shipped_week_detail.php'>
                        <font font color="black">Week'sShipped!
                <br>
                <?php include 'bs_kf_orders_shipped_week_sum_tom.php';?>
              </font>
                    </a>
                </button>
            </div>
            <div class="col-md-2 ">
                <button type= "button" class="btn-danger btn-lg btn-block  ">
                    <a href='/dashboard/kf_production_price_week.php'>
                        <font font color="black">Week'sProduction!
                <br>
                <?php include 'bs_kf_production_price_week.php';?>
              </font>
                    </a>
                </button>
            </div>
            <div class="col-md-2 ">
                <button type= "button" class="btn btn-primary btn-lg btn-block ">
                    <a href='/dashboard/kf_orders_entered_detail_by_week.php'>
                        <font font color="black">Week's Orders!
                <br>
                <?php include 'bs_kf_orders_entered_by_week.php';?>
              </font>

 

[ginerjm]

If you have so many queries that need to be run and you already feel that your code needs cleaning up, perhaps you might want to re-think your approach to whatever problem you are trying to find a solution for.  IFyou don't like your code already, then why should we help you clean it up when we don't know how it began?

[kat35601]

well it began as 31 individual reports  ,  all day they run this detailed reports to glean a few numbers from so I created (5 or 6 years ago) a page that gathered all of the these numbers and summed them up to one report that they can run that has all the needed information.   I would like to have a file that had all the queries and just call them to the page when it's run. That would make it easier to add or adjust queries as things change. I also think I would like to connect to the server one time run all the queries instead of connection at each query. who knows maybe I can learn something new.

What has sparked this is my old apache server has come to the end of it's life and as I move things to the new server I wanted to clean them up.

[mac_gyver]

yes, you should only make one database connection. your main code would do this in an 'initialization' section, then pass it as a call-time parameter into any function that needs it. this will eliminate the repeated connection code. Don't Repeat Yourself (DRY.) next, there's generally no need to close a database connection in your code since php will close it when your script ends.

you should separate the database specific code, that knows how to query for and fetch data, from the presentation code, that knows how to produce the output from that data. to do this just fetch the data from any query into a php variable. functions should return their result to the calling code, so that the result can be used in any context.

to produce the output, both for the main layout and for the dynamic sections within a page, you should use a template system. either just using simple php code or use one of the 3rd party template systems. you should use 'require' for things your page must have for it to work.

since odbc_fetch_array is available (which is why you were asked in a previous thread what database server type you were using), you should use that everywhere, instead of using multiple odbc_result statements. always use the simplest logic that accomplishes a task. Keep It Simple (KISS.) 

[kat35601]

I am connecting to Microsoft SqlServer. Wow mac_giver thank you.I agree with the DRY and KISS methods. I new there was a better way. I will work on the initialization section first.