include inside sql statement

[kat35601]

I have a sql statement that has partsthat are the same for all my pages so I was wondring if you could use and include inside a sql statement? The case statements is what I would like to be able to have one one page that I could include so I could make changes easier.

[php]<?php
$itemtype=$_GET[“id”];
$connect =odbc_connect(“removed”);
if(!$connect) {
exit("Connection Failed: " . $connect);
}

$sql="SELECT OrbeData_KithKitchens.dbo.WIP_master.uompschedulenumber
,CASE
WHEN OrbeData_KithKitchens.dbo.WIP_master.uompscheduleColor = ‘SHORT’
THEN ‘White’
ELSE OrbeData_KithKitchens.dbo.WIP_master.uompscheduleColor
END AS uompscheduleColor
,jmaPartID
,OrbeData_KithKitchens.dbo.WIP_master.ompSalesOrderID
,uomlSorP
,serial
,rail
,panel
,stile
,upsdescription
,itemtype
,jmaPartShortDescription
,uomlStyleGroup
,STATUS
,convert(VARCHAR(19), createdate, 0) AS createdate
,convert(VARCHAR(19), lastupdate, 0) AS lastupdate
,CASE
WHEN STATUS = 10
THEN ‘Created’
WHEN STATUS = 3
THEN ‘PreSanding’
WHEN STATUS = 4
THEN ‘PrePaint’
WHEN STATUS = 5
THEN ‘Paint’
WHEN STATUS = 6
THEN ‘PostPaint’
ELSE ‘’
END AS Status1
,m1_DC.dbo.SalesOrders.UOMPTRUCKNUMBER
,CASE
WHEN convert(VARCHAR(10), utrdatedepart, 101) = convert(VARCHAR(10), getdate(), 101)
AND m1_DC.dbo.UTRUCK.UTRTRUCKNUMBER = m1_DC.dbo.SalesOrders.UOMPTRUCKNUMBER
THEN ‘red’
ELSE ‘white’
END AS truck
FROM WIP_master
LEFT JOIN m1_DC.dbo.SalesOrders ON m1_DC.dbo.SalesOrders.ompSalesOrderID = OrbeData_KithKitchens.dbo.WIP_master.ompSalesOrderID
LEFT JOIN m1_DC.dbo.UTRUCK ON m1_DC.dbo.UTRUCK.UTRTRUCKNUMBER = m1_DC.dbo.SalesOrders.UOMPTRUCKNUMBER
WHERE redtag = ‘Y’
AND redtagclosed != ‘Y’
AND itemtype = ‘$itemtype’
ORDER BY OrbeData_KithKitchens.dbo.WIP_master.uompschedulenumber
,uomlSorP
,upsdescription
,jmaPartID
";

$result =odbc_exec($connect,$sql);
if(!$result){
exit(“Error in SQL”);
}[/php]

[benanamen]

Simple, just create a variable in a main config file that is used on all your pages. At the least, it would be your DB connection file. Then just concatenate the variable where you want to use it.

In db.php or config.php

$sql_include = " re-usable_sql_here ";// Notice the spaces at start and end

Query page

[php]$sql=“SELECT column from table”;

$sql .= $sql_include;

$sql .=“WHERE column = value”;[/php]

Another way, you could use DEFINE instead of a variable.

[astonecipher]

I would do a model, you have your database and sql statements in a single class, then include that model and call the class from multiple pages. You could add a parameter to the method that added where clauses when needed.

[kat35601]

Works perfect Thanks