MSSQL UDF and PHP

[ideasworking]

Hello,

I created a table valued function in MSSQL and now I would like to pull that data into PHP to display on the page. Is this possible? If so, how? This is what I have tried so far and it didn't work.
[COLOR=blue gray]
$db = @mssql_connect("MSSQL","me","mypassword") or die("Unable to connect to server");
mssql_select_db("Reporting");
$Monthlyqry = mssql_query("SELECT * FROM dbo.TotalsSummary($FirstofMonth,$CurrentDate)")
mssql_data_seek($Monthlyqry, $monthly_count);
$Monthly = mssql_fetch_array($Monthlyqry);
[/color]

 

[sleipnir214]

Insufficient data for a meaningful answer.

"it didn't work" is very vague.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[ideasworking]

Oh I'm sorry... The query dies and there is no data returned. I changed my code a little to show if the query dies. And it the query did die.

Anyway this is the latest attempt, do you see anything wrong?

	$db = @mssql_connect("MSSQL","me","mypassword") or die("Unable to connect to server");
    mssql_select_db("Reporting");
    $Monthlyqry = mssql_query("EXEC dbo.TotalsSummary($FirstofMonth,$CurrentDate)");
    mssql_data_seek($Monthlyqry, $monthly_count) or die ("Query doesn't work");
    $Monthly = mssql_fetch_array($Monthlyqry);

 

[sleipnir214]

As a piece of general advice, don't use PHP's "@" error-supression operator in development code. You want to see all errors in development code.

For debugging purposes, I'd also change this line:

$Monthlyqry = mssql_query("EXEC dbo.TotalsSummary($FirstofMonth,$CurrentDate)");

to read:

$query = "EXEC dbo.TotalsSummary($FirstofMonth,$CurrentDate)";
print $query;
$Monthlyqry = mssql_query($query);

so that I can verify I'm producing a good query. You can copy-and-past the query into an MSSQL management tool that allows you to run ad hoc queries.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[ideasworking]

Thanks for the suggestions.

I changed the code as follows and now it doesn't die... however it also doesn't return any values.

	$Monthlyqry = mssql_query("SELECT * FROM dbo.TotalsSummary('$FirstofMonth','$CurrentDate')");
	mssql_data_seek($Monthlyqry, $monthly_count)or die ("Query doesn't work");
	$Monthly = mssql_fetch_array($Monthlyqry);

When the query prints it looks right. I can copy it into SQL and it executes just fine. So I think it's down to... the query returning something other than an array of values from the function. Any thoughts?

 

[ideasworking]

Oh.... MAN!!!

It was a typo.... I had echo $monthly["GTG"] instead of echo $Monthly["GTG"]

Thanks for your help