Using MSSQL STUFF in query with PHP

Question

So first let me explain what I'm trying to do, then how I'm doing it, and then what the problem is. I'm trying to create a mssql query to read invoices from a database. Each invoice is essentially a compilation of individual charges grouped together by an invoice ID. The catch is that different charges could come from different containers (charges are associated with containers). What I need to do is group the charges by invoice ID and concatenate the container names for all the charges in an invoice, with some delimiter like "|". I have created a temporary table @t that holds all the charges and container names needed to generate an invoice. After looking online for quite a while, I found the STUFF command which appears to do what I want (appears, and does). So here is the SQL for my query:

select invoice_id, [Total Paid] RefNumCount,PayCount, MIN([rowNumber]) as rowNumber,     Vendor, Due, [Invoice Number], Company, MAX(Location) as Location,
 SUM([Funded Amount]) as [Funded Amount], 
 [Company ID], [Invoice ID], provider_reference_number, reconcile_date, all_providers
, STUFF(
        (
        select '|' + t1.[Container Name]
        from @t t1
        where t1.[Invoice Number] = t.[Invoice Number]
        order by t1.[Container Name]
        for XML PATH(''), TYPE
        ).value('.', 'nvarchar(100)')
        ,1,1,''
     ) as [Container Name] 

from @t t
group by invoice_id, [Total Paid], RefNumCount, PayCount, Vendor, Due, [Invoice Number], Company, 
    [Company ID], [Invoice ID], provider_reference_number, reconcile_date, all_providers

When I run this is SQL Server Management Studio 2008, it runs just fine. However, when I use this code in my PHP application, the query does not work and ultimately I get a fatal error by trying to read an empty result set. Does anybody know why this problem is happening? Or if there is another way to concatenate strings?

The following is the PHP Code segment used to call the query

           try{
                $q = cms_database::instance();
                 $result = $q->query($sql_statement);        
                 if (!$result) {
                       throw new Exception("Database Error [{$q->errno}] {$q->error}");
                  } 

                $invoices = array();
                while($row=$result->fetch_assoc()) {
                    $invoiceX = array();
                    $invoiceX["rowNum"]     = $row["rowNumber"];
                    $invoiceX["Vendor"]     = $row["Vendor"];
                    $invoiceX["Due"]        = $row["Due"];
                    $invoiceX["Acct"]       = $row["Container Name"];//$row["Account Number"];
                    $invoiceX["container_id"]=$row["container_id"];
                    $invoiceX["provider_id"] =$row["provider_id"];
                    $invoiceX["Invoice"]    = $row["Invoice Number"];
                    $invoiceX["Location"]   = $row["Company"];
                    $invoiceX["FundedAmt"]  = $row["Funded Amount"];
                    $invoiceX["InvoiceID"]  = $row["Invoice ID"];
                    $invoiceX["Notes"]      = $row["Notes"];
                    //merge the notes with notes from payment history that matches the invoiceid


                    $invoices[] = $invoiceX;
                }

                //filter out invoices with all reference numbers accounted for and remaining to be paid is $0.00



                $returnPackage["data"] = $invoices;
                //print $result;
            } catch (Exception $e){
                $returnPackage["success"]=0;
                $returnPackage["message"]=$e.message;

            }

And the error I'm getting is: "
Fatal error: Call to a member function fetch_assoc() on a non-object in D:[path_to_the_file].php on line 116
"

Line 116 is the line of code

while($row=$result->fetch_assoc()) {

Please show your PHP code for executing the query, and exact error message you are getting. — ɴᴀᴛᴇ ᴅᴇᴠ
– ɴᴀᴛᴇ ᴅᴇᴠ, Commented Oct 17, 2013 at 14:25
I've tried it by other means, but it still uses the for xml path. Could that be throwing it off? — Andrew
– Andrew, Commented Oct 17, 2013 at 15:28

Willy_Sunny · Accepted Answer · 2017-11-29 05:54:20Z

1

Though this might come a little late, but try this SQL call instead.

select invoice_id, [Total Paid] RefNumCount,PayCount, MIN([rowNumber]) as rowNumber, 
       Vendor, Due, [Invoice Number], Company, MAX(Location) as Location,
       SUM([Funded Amount]) as [Funded Amount], [Company ID], [Invoice ID],
       provider_reference_number, reconcile_date, all_providers , 
       STUFF((select '|' + t1.[Container Name]
              from @t t1
              where t1.[Invoice Number] = t.[Invoice Number]
              order by t1.[Container Name]
              for XML PATH('')),1,1,'') as [Container Name] 
from @t t
group by invoice_id, [Total Paid], RefNumCount, PayCount, Vendor, Due, [Invoice Number], 
         Company, [Company ID], [Invoice ID], provider_reference_number, reconcile_date, 
         all_providers

The error occured when you added '|' at the select statement below which will strip away the xml tags, causing error when you use .value() trying to extract the data.

STUFF((select '|' + ... from ... for XML PATH(''), TYPE).value('.', 'nvarchar(100)'),1,1,'')

answered Nov 29, 2017 at 5:54

Willy_Sunny

1993 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Over a year ago

Man this post is old! I've even changed jobs since I posted this, so I have no idea if this will work or not. I'll mark this as the answer, however, since it seems right. Thanks for your time!

Collectives™ on Stack Overflow

Using MSSQL STUFF in query with PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related