0

I am using the following PHP code to fetch data from my mssql table:

$query2 = "select kArtikel, cArtNr, fVKNetto, fUVP from tartikel";
$result = mssql_query($query2);
$a= array();
while ($row = mssql_fetch_assoc($result)) {
    $a['data'][] = $row;
}
echo json_encode($a);

Works perfectly fine as a first test. Now I modify the query to a more complex one:

$query2 = "
SELECT ABP.cArtNr AS 'Artikelnummer',
       MAX(SUBSTRING(ABP.cName,8,50)) AS 'Beschreibung',
       SUM(ABP.nQuantityPurchased) AS 'Anzahl',
       SUM(ABP.nQuantityPurchased) * MAX(AA.fPrice) AS 'Brutto-Umsatz'
FROM pf_amazon_bestellung
INNER JOIN pf_amazon_bestellungpos ABP ON pf_amazon_bestellung.kAmazonBestellung = ABP.kAmazonBestellung
INNER JOIN pf_amazon_angebot AA ON ABP.cArtNr = AA.cSellerSKU
WHERE dPurchaseDate >= DateAdd(DAY, DateDiff(DAY, 0, getDate()), 0)
  AND pf_amazon_bestellung.cOrderStatus <> 'Canceled'
GROUP BY ABP.cArtNr
";

Now with this query, I don't get any results. Just white page. First test of the small snipped "select kArtikel, cArtNr, fVKNetto, fUVP from tartikel" was perfectly fine, the longer query doesn't work.

I already deleted the aliases, put everything in one line... nothing helped.

Of course, the query is correct and when performing it in SSMS everything is working as it should.

What can I do to get this larger query working here in PHP?

Thank you for your help!

thowi
  • 35
  • 7
  • 3
    If it works in SQL directly it is not the query then. Are you getting an error? What is the error? Did you try debugging and get the value from the $query2 variable and see if it is what you expect in there and run whats in the variable in SQL? – Brad Sep 19 '18 at 12:31
  • 2
    just getting a white page almost certainly indicates that a) your code threw and error and b) you don't have error reporting switched on in PHP. Either switch on error reporting (you can google how to do it) or check your logs, if PHP logs exceptions. Then you'll know the exact nature of the error and can investigate. You could also try adding an extra "echo" statement somewhere just to check that it's not simply a case of returning no results at all. – ADyson Sep 19 '18 at 12:32
  • It is good to check mssql_query() result, or put mssql_get_last_message(); after mssql_query(...) to get additional information. – Zhorov Sep 19 '18 at 12:43
  • Okay, I added a few debug lines to see what's going on: https://pastebin.com/YRMDGJ6D This is the result: https://www.dropbox.com/s/9k38nk6o1d52qm5/result.jpg?dl=0 The Query result of "Resource id #3" is kind of confusing to me... – thowi Sep 19 '18 at 12:48
  • mssql_get_last_message() after mssql_query() shows following information: Changed database context to 'Mandant_1'. – thowi Sep 19 '18 at 12:52
  • At this link you can see the live result: http://stats.valonic.com/server_processing.php – thowi Sep 19 '18 at 12:53
  • What is `$result`? How are you setting that variable. That appears to be just an mssql resource/object here, not something that can be echo'd which is why you are getting that `Resource id #3` response. – JNevill Sep 19 '18 at 13:01
  • $result = mssql_query($query); << so $result will contain the whole query result of the database. Good to know that this is not echo-able. Mh :/ – thowi Sep 19 '18 at 13:06
  • Okay, just tried the echo of $result with the working small sql query - echo is finely working and showing a result @JNevill – thowi Sep 19 '18 at 13:12

0 Answers0