Can someone help me with the following code? I'm accessing a vendors database and don't really have a map of it. But using the following code I get what I need. But I am having a problem. Here is my SQL statement.

SELECT rudDescrip ,Supplier=ISNULL((SELECT MAX(smName)
 
 FROM
 
 TagMaster LEFT JOIN ReceivingHeader ON rehID=tagSourceID AND tagSourceType='Receiving' LEFT JOIN ProductionHeader ON ruhID=tagSourceID  AND tagSourceType='Production' INNER JOIN SupplierMaster ON smID=rehSuppID
 
 WHERE
 
 (tagDestID='$Kiln' or tagDestID in (select a.ruhID from ProductionHeader a where a.ruhFileNo='<ruhFileNo>')) AND tagProductID=rudProductID AND tagLocationID=rudLocationID),'') 
,rudPieces,rudVolume,Case When pvmValue is not null Then pvmValue Else 0 End AS Market,rudCost,rudTally,rudPcsPerPkg,pamDescrip=isnull(pamDescrip,'') ,TotalCost=(SELECT SUM(rd1.rudTotal) FROM ProductionDetail rd1 
WHERE rd1.rudCategory='Consumed' AND (rd1.rudID='$Kiln' or rd1.rudID in (select a.ruhID from ProductionHeader a where a.ruhFileNo='<ruhFileNo>'))) ,TotalVol=isnull((select sum(a.rudVolume) from ProductionDetail a where 
(a.rudID='$Kiln' or a.rudID in (select b.ruhID from ProductionHeader b where b.ruhFileNo='<ruhFileNo>')) and a.rudCategory='Consumed'),0) FROM ProductionDetail  LEFT JOIN ProductValueMaster ON 
rudProductID = pvmProductID and pvmDocType='GENERAL' LEFT JOIN ProductMaster on prdID=rudProductID left join ProductAttributeMaster on pamDocID=prdGradeID and pamDocType='Grade' WHERE rudCategory='Consumed'  
and (rudID='$Kiln' or rudID in (select a.ruhID from ProductionHeader a where a.ruhFileNo='<ruhFileNo>')) order by rudDescrip";

The problem I am having is I can't use any of the variables. I can get it to output if I block it, but if I try to access $TotalCost I get nothing.

I need to do some maths on TotalCost so this is problematic! Can anyone show me where I'm going wrong? I will post the rest of my code if needed.

Thanks!

That's not an SQL statement, that's an SQL novel.

Give us the code where you're trying to get those variables. What are you doing with the SQL result?

Here is the entire code:

<?php
 
//Set info from submit
$Kiln = $_POST[Kiln];
$Charge = $_POST[Charge];
 
 
//set up the connection to the ISIS Database on our SQL Server
$myServer = "sql2008";
$myUser = "username";
$myPass = "password";
$myDB = "ISISWood"; 
 
//create an instance of the  ADO connection object
$conn = new COM ("ADODB.Connection")
  or die("Cannot start ADO");
 
//define connection string, specify database driver
$connStr = "PROVIDER=SQLOLEDB;SERVER=".$myServer.";UID=".$myUser.";PWD=".$myPass.";DATABASE=".$myDB; 
  $conn->open($connStr); //Open the connection to the database
 
 
//declare the SQL statement that will query the database
 
$query = "SELECT rudDescrip ,Supplier=ISNULL((SELECT MAX(smName)
 
 FROM
 
 TagMaster LEFT JOIN ReceivingHeader ON rehID=tagSourceID AND tagSourceType='Receiving' LEFT JOIN ProductionHeader ON ruhID=tagSourceID  AND tagSourceType='Production' INNER JOIN SupplierMaster ON smID=rehSuppID
 
 WHERE
 
 (tagDestID='$Kiln' or tagDestID in (select a.ruhID from ProductionHeader a where a.ruhFileNo='<ruhFileNo>')) AND tagProductID=rudProductID AND tagLocationID=rudLocationID),'') 
,rudPieces,rudVolume,Case When pvmValue is not null Then pvmValue Else 0 End AS Market,rudCost,rudTally,rudPcsPerPkg,pamDescrip=isnull(pamDescrip,'') ,TotalCost=(SELECT SUM(rd1.rudTotal) FROM ProductionDetail rd1 
WHERE rd1.rudCategory='Consumed' AND (rd1.rudID='$Kiln' or rd1.rudID in (select a.ruhID from ProductionHeader a where a.ruhFileNo='<ruhFileNo>'))) ,TotalVol=isnull((select sum(a.rudVolume) from ProductionDetail a where 
(a.rudID='$Kiln' or a.rudID in (select b.ruhID from ProductionHeader b where b.ruhFileNo='<ruhFileNo>')) and a.rudCategory='Consumed'),0) FROM ProductionDetail  LEFT JOIN ProductValueMaster ON 
rudProductID = pvmProductID and pvmDocType='GENERAL' LEFT JOIN ProductMaster on prdID=rudProductID left join ProductAttributeMaster on pamDocID=prdGradeID and pamDocType='Grade' WHERE rudCategory='Consumed'  
and (rudID='$Kiln' or rudID in (select a.ruhID from ProductionHeader a where a.ruhFileNo='<ruhFileNo>')) order by rudDescrip";
 
//execute the SQL statement and return records - Input
?>
<h1>Kiln Input</h1>
<?php
 
$rs = $conn->execute($query);
$num_columns = $rs->Fields->Count();
echo $num_columns . "<br>";  
 
for ($i=0; $i < $num_columns; $i++) {
    $fld[$i] = $rs->Fields($i);
}
echo "<table>";
while (!$rs->EOF)  //carry on looping through while there are records
{
    echo "<tr>";
    for ($i=0; $i < $num_columns; $i++) {
        echo "<td>" . $fld[$i]->value . "</td>";
    }
    echo "</tr>";
    $rs->MoveNext(); //move on to the next record
 
}
 
echo "</table>";
?>
 

Yeah it's a monster statement, but somehow it works :)

Here is a screenshot of what it produces.

 

As you can see I could really use a map for this DB.

 

I only got that select statement by capturing it on the SQL Server as I queried it from the software.

 

Actually that's not the code that produces this screenshot, there are two select statements to get the screen shot.

Whoever wrote that SQL statement like that needs to be shot.

Here's the same thing but re-formatted.

It's still too much effort trying to figure out what it's doing though.

If you want totalcost, run the sub-select for just totalcost on its own and see if it works.

SELECT ruddescrip,
 
        supplier = Isnull
            (
                (
                    SELECT Max(smname)
                    FROM   tagmaster
                    LEFT JOIN receivingheader ON rehid = tagsourceid AND tagsourcetype = 'Receiving'
                    LEFT JOIN productionheader ON ruhid = tagsourceid AND tagsourcetype = 'Production'
                    INNER JOIN suppliermaster ON smid = rehsuppid
                    WHERE
                        (
                            tagdestid = '$Kiln'
                        OR
                            tagdestid IN
                                (
                                    SELECT a.ruhid
                                    FROM   productionheader a
                                    WHERE  a.ruhfileno = ''
                                )
                        )
                    AND tagproductid = rudproductid
                    AND taglocationid = rudlocationid
                )
            , ''
            ), /* /supplier */
 
        rudpieces,
        rudvolume,
 
        CASE
            WHEN pvmvalue IS NOT NULL THEN pvmvalue
            ELSE 0
        END AS market,
 
        rudcost,
        rudtally,
        rudpcsperpkg,
        pamdescrip = Isnull(pamdescrip, ''),
 
        totalcost = 
            (
                SELECT SUM(rd1.rudtotal)
                FROM   productiondetail rd1
                WHERE  rd1.rudcategory = 'Consumed'
                AND
                    (
                        rd1.rudid = '$Kiln'
                    OR
                        rd1.rudid IN
                            (
                                SELECT a.ruhid
                                FROM   productionheader a
                                WHERE a.ruhfileno = ''
                            )
                    )
            ), /* /totalcost */
 
        totalvol = Isnull
            (
                (
                    SELECT SUM(a.rudvolume)
                    FROM   productiondetail a
                    WHERE
                        (
                            a.rudid = '$Kiln'
                        OR
                            a.rudid IN
                                (
                                    SELECT b.ruhid
                                    FROM   productionheader b
                                    WHERE
                                    b.ruhfileno = ''
                                )
                        )
                    AND a.rudcategory = 'Consumed'
                )
            , 0
            ) /* /totalvol */
 
 
FROM   productiondetail
LEFT JOIN productvaluemaster     ON rudproductid = pvmproductid AND pvmdoctype = 'GENERAL'
LEFT JOIN productmaster          ON prdid = rudproductid
LEFT JOIN productattributemaster ON pamdocid = prdgradeid AND pamdoctype = 'Grade'
 
WHERE  rudcategory = 'Consumed'
AND
    (
        rudid = '$Kiln'
    OR
        rudid IN
            (
                SELECT a.ruhid
                FROM   productionheader a
                WHERE  a.ruhfileno = ''
            )
    )
 
ORDER  BY ruddescrip

You're doing PHP against MS SQL Server? :/

Bit of an odd mix.


Anyway...


Get Microsoft SQL Server Studio Management thing and it can generate one for you.
(Assuming the DB has relationships defined anyway.)

Yeah it is sorta.

 

But here's why I'm doing it. The company that provides the software are a bunch of ass hats. As you can see by that select statement.

 

When the people who work where I work want to do any kind of math on that database their answer is dumbing it into excel. Fuck that. All we get done doing is typing shit into excel. It's in the database, why the fuck should I have to do that?

 

So I am going to automate the processes, and really using PHP might be temporary, I just want to see what I am able to farm out of it and how hard it is going to be.

 

I'll have to take a look at the sql server. I can get a list of the tables (all 1000 or so of them) but there isn't any logical order to them and I don't know what goes where.

Hmmm, I wouldn't be surprised if there aren't any foreign keys setup then. :/

 

Anyway, this is a rather long-winded video that explains how to create a diagram, if you're able to connect via Management Studio.

 

http://www.youtube.com/watch?v=wMbPRHeYvMU#t=40s

Peter my ad was for a PHP wizard, not for going back to school to learn to be a php wizard! :-O

 

Just messin. I appreciate you looking at it. I can access the Management Studio.

 

Oh and speaking of that, sorta. Would you do what I'm trying in PHP or in the SSRS? I haven't been too fond of the reporting services but it could probably do what I wanted as well.

Well, I would do what you're trying to do in CFML, because it's way better than PHP. ;P

I've never used SSRS, but all similar report-generating software I've tried has been a bunch of crap, so ... *shrug*

I love ColdFusion and would prefer using it but the Developer version only allows 2 connections and if this works I'll need more than that.

 

You know what you should do. Develop a program that converts php to cf and vice versa. You could probably make some good money.

That's one of the reason I don't use ColdFusion any more, I use Railo which is way better. :)


I have very seriously considered writing a PHP -> CFML converter.
I'm not convinced it would actually make money though?

So the tags are the same? Is it free? That could be a sweet deal there!

 

I bet you could make a few dollars if you made a converter. I've needed to do it from time to time and a few dollars for software to do it over me would have been a no brainer.

Yep. LGPL Free Software and Open Source.

Anything from ACF8 and below will almost certainly work on the Railo 3.2, with a few deliberate exceptions.

You can even download a 50MB express version and give it a try without even sitting through a stupidly long install process.

Dude I'm all over that. Thanks for letting me know about it! I'm much more fluent in CF than PHP.

Thing is, if I made a converter, I couldn't not give it away, so any money would only come through donations, and I don't know if that would be enough to cover the effort spent on it.

Also, whilst a direct port is do-able, and might even be relatively straight-forward, it wouldn't necessarily produce good code.


Damn it, you're making that conundrum I have even more complicated! :'(

%-)

 

It's funny that you have considered it.

No problem, I've been a huge fan of Railo for ages. It's really awesome! (bounce)

If you do have any problems getting going, there's a mailing list where you'll find lots of friendly helpful people. :)

I can't seem to get it connected to IIS, which isn't a big deal. But can you tell me where I should put my cfm files so Tomcat can parse them? I can't seem to find a wwwroot or anything like that.