Dynamic Query to List of Values

[PCHomepage]

I'm rusty at this and, although it's related to MSSQL, I think the answer is PHP. I HAD a query that was simply bringing up a single column of values but I just reworked it to be more dynamic and I am not sure who to present the results. Since the names and values each have their own CSS styles, I need two separate columns which was easy when one was hard-coded but now that it's not, I'm stuck. Can anyone help? Here is what I have (it's inside a function, by the way):

$tsql = "Declare @_tbl table(
Condition varchar(50),
Value decimal(10,0));
DECLARE @YearVal varchar(30);
SET @YearVal = 2010;

INSERT INTO @_tbl Select 'Infectious Disease', SUM([INFECT-DISEASE]) FROM [Section 3-5] WHERE [Section 3-5].OSHPD_ID IN (SELECT OSHPD_ID FROM PARTNER_CLINICS) AND REPT_YEAR = @YearVal
INSERT INTO @_tbl Select 'Neoplasms', SUM(NEOPLSMS) FROM [Section 3-5] WHERE [Section 3-5].OSHPD_ID IN (SELECT OSHPD_ID FROM PARTNER_CLINICS) AND REPT_YEAR = @YearVal
INSERT INTO @_tbl Select 'Endocrine', SUM(ENDOCRN) FROM [Section 3-5] WHERE [Section 3-5].OSHPD_ID IN (SELECT OSHPD_ID FROM PARTNER_CLINICS) AND REPT_YEAR = @YearVal
INSERT INTO @_tbl Select 'Blood Disease', SUM(BLOOD_DIS) FROM [Section 3-5] WHERE [Section 3-5].OSHPD_ID IN (SELECT OSHPD_ID FROM PARTNER_CLINICS) AND REPT_YEAR = @YearVal
INSERT INTO @_tbl Select 'Mental Disorders', SUM(MENTAL) FROM [Section 3-5] WHERE [Section 3-5].OSHPD_ID IN (SELECT OSHPD_ID FROM PARTNER_CLINICS) AND REPT_YEAR = @YearVal
INSERT INTO @_tbl Select 'Nervous System', SUM(NERVIOUS) FROM [Section 3-5] WHERE [Section 3-5].OSHPD_ID IN (SELECT OSHPD_ID FROM PARTNER_CLINICS) AND REPT_YEAR = @YearVal

SELECT TOP 5 Condition, Value FROM @_tbl ORDER BY Value Desc";

	
// Execute query.
$stmt = sqlsrv_query($conn, $tsql);

// Present results
$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC);

$Values = "\n\n<div class=\"DiagnosisTitle\">Top 5 Encounters by Diagnosis</div>\n\n".

"<div class=\"DiagColumn1\">Endocrine<br>\n".
"Circulatory<br>\n".
"Respiratory<br>\n".
"Child Preventative Services<br>\n".
"Nervous System</div>\n\n".

"<div class=\"DiagColumn2\">".
"<span class=\"RightAlign\">".number_format($row[0])."</span><br>\n".
"<span class=\"RightAlign\">".number_format($row[1])."</span><br>\n".
"<span class=\"RightAlign\">".number_format($row[2])."</span><br>\n".
"<span class=\"RightAlign\">".number_format($row[3])."</span><br>\n".
"<span class=\"RightAlign\">".number_format($row[4])."</span>\n".
						"</div>\n\n";
return $Values;

 

[vacunita]

So what does your query return now? Your SQL statement does snot match what your code does So I'm not sure what is really happening.

I would assume your SELECT statement: "SELECT TOP 5..." would return 5 rows. However your PHP seems to work off of a single row with 5 fields.







----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[PCHomepage]

No, the lower part (HTML) is leftover from the original code so, of course, does nothing. What I'm looking for is something like this but so far have been unable to get it to work:

	$stmt = sqlsrv_query($conn, $tsql);

$Values = "\n\n<div class=\"DiagnosisTitle\">Top 5 Encounters by Diagnosis</div>\n\n";

$Values = $Values . "<div class=\"DiagColumn1\">";
while ($col1 = sqlsrv_fetch_array($stmt)) {
		$Values = $Values. $col1['Condition']."\n<br>";
	}
$Values = $Values . "</div>\n\n";
	
$Values = $Values . "<div class=\"DiagColumn2\">";
while ($col2= sqlsrv_fetch_array($stmt)) {
		$Values = $Values. "<span class=\"RightAlign\">".number_format($col2['Value'])."</span><br>\n";
	}
$Values = $Values . "</div>\n\n";

 

[PCHomepage]

I think I am confused about how much of the SQL to actually execute in order to get results! I presume all of it but maybe the "virtual" part has to be run separately. The code I posted above is presuming that the entire query is run as one but I am getting no data from it. The divs, spans and other HTML are there but no results. Can anyone help? Thanks.

By the way, I've modified it slightly after realizing that the sample from which it was based was using the mssql functions rather than the sqlsrv functions. This is what I have now:

function diagnosis($GetYear, $GetClinic="") {
	$serverName = "(local)";
	$connectionInfo = array("Database"=>"OSHPD");

	$conn = sqlsrv_connect($serverName, $connectionInfo);

	// Creates a "virtual" table with two columns; limits to top 5
	$tsql = "Declare @_tbl table(
		Condition varchar(50),
		Value decimal(10,0))

INSERT INTO @_tbl Select 'Infectious Disease', SUM([INFECT-DISEASE]) FROM [Section 3-5] WHERE [Section 3-5].OSHPD_ID IN (SELECT OSHPD_ID FROM PARTNER_CLINICS) " . diagwhereval($GetYear, $GetClinic) .

" INSERT INTO @_tbl Select 'Neoplasms', SUM(NEOPLSMS) FROM [Section 3-5] WHERE [Section 3-5].OSHPD_ID IN (SELECT OSHPD_ID FROM PARTNER_CLINICS) " . diagwhereval($GetYear, $GetClinic) .

" INSERT INTO @_tbl Select 'Endocrine', SUM(ENDOCRN) FROM [Section 3-5] WHERE [Section 3-5].OSHPD_ID IN (SELECT OSHPD_ID FROM PARTNER_CLINICS) " . diagwhereval($GetYear, $GetClinic) .

" INSERT INTO @_tbl Select 'Blood Disease', SUM(BLOOD_DIS) FROM [Section 3-5] WHERE [Section 3-5].OSHPD_ID IN (SELECT OSHPD_ID FROM PARTNER_CLINICS) " . diagwhereval($GetYear, $GetClinic) .

" INSERT INTO @_tbl Select 'Mental Disorders', SUM(MENTAL) FROM [Section 3-5] WHERE [Section 3-5].OSHPD_ID IN (SELECT OSHPD_ID FROM PARTNER_CLINICS) " . diagwhereval($GetYear, $GetClinic) .

" INSERT INTO @_tbl Select 'Nervous System', SUM(NERVIOUS) FROM [Section 3-5] WHERE [Section 3-5].OSHPD_ID IN (SELECT OSHPD_ID FROM PARTNER_CLINICS) " . diagwhereval($GetYear, $GetClinic) .

" INSERT INTO @_tbl Select 'Circulatory', SUM(CIRCULATORY) FROM [Section 3-5] WHERE [Section 3-5].OSHPD_ID IN (SELECT OSHPD_ID FROM PARTNER_CLINICS) " . diagwhereval($GetYear, $GetClinic) .

" SELECT TOP 5 Condition, Value FROM @_tbl ORDER BY Value Desc";
	
	// Execute query.
	$stmt = sqlsrv_query($conn, $tsql);

	// Present results
$Values = "\n\n<div class=\"DiagnosisTitle\">Top 5 Encounters by Diagnosis</div>\n\n";

$Values = $Values . "<div class=\"DiagColumn1\">";

while ($col1 = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
	$Values = $Values. $col1['Condition']."\n<br>";
}

$Values = $Values . "</div>\n\n";

$Values = $Values . "<div class=\"DiagColumn2\">";

while ($col2 = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
	$Values = $Values. "<span class=\"RightAlign\">".number_format($col2['Value'])."</span><br>\n";
}

$Values = $Values . "</div>\n\n";

return $Values;

}

Note: diagwhereval($GetYear, $GetClinic) is a function that provides the specific WHERE values needed by the site's search.

 

[vacunita]

I'd something along the lines of:

  $stmt = sqlsrv_query($conn, $tsql);

while ($col1 = sqlsrv_fetch_array($stmt)) {
        $Values = "<div><span>" . $col1['Condition']."</span><span>" . number_format($col2['Value'] . "</span></div>";
    }

This will give you 2 columns. You can add give then any css styling you want.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[PCHomepage]

Thanks but the problem apparently has something to do with the sqlsrv_query($conn, $tsql) and sqlsrv_fetch_array($stmt) not getting results. According to the PHP log file, there is something wrong with it:

sqlsrv_fetch_array() expects parameter 1 to be resource, boolean

Unfortunately I'll need to loop through the two columns separately as col1 does not have line-by-line divs (it has a single one surrounding the column) while the other, col2, has spans on each value.

 

[SunnyByfleet]

Never used sqlsrv but in mysql an error like that indicated that there wasn't any data to return.

 

[PCHomepage]

Yes, thanks, I know but I am trying to determine why it is returning no data. In the SQL Server analyzer, it shows the values that it should but I am not sure how to apply such code to the PHP script. Having SQL create a dynamic table is something I've not done before.

 

[SunnyByfleet]

Ah ok, never used virtual tables. I would suggest however that while you are trying to get it to work you strip it to a bare minimum, and create a virtual table with a single row. If nothing else it will make it easier for other people to follow.

Also, you seem to be sending multiple queries in one go. I know that when using mysql_query, you can only send one query at a time. And every example of sqlsrv_query I have seen (admitedly only looking in the last five minutes) has only had one query. So perhaps you just need to break your $tsql down into separate queries and run them one at a time, or see if there is a different sqlsrv way of running multiple queries at a time.

 

[vacunita]

I haven't used sqlsrv either so not sure whether it supports multiple queries, perhaps displaying any errors may be of use.

$errors=sqlsrv_errors();
echo "<pre>";
print_r($errors);
echo "</pre>";

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[SunnyByfleet]

http://social.msdn.microsoft.com/Fo.../thread/0e2a6caa-2fc6-49ca-a93d-bb0b4d78a042/

This link indicates that multiple queries are definately allowed. You might find it useful.

 

[jpadie]

the instance of the error being at the fetch* point suggests that one or more of the queries executed have errors, and therefore a boolean false is returned rather than a resource.

I know nothing about sql server. Might you have to separate queries by a semicolon? otherwise how does the interpreter know when one query stops and another starts?

Also in your 'diagwhereval' functions you are using php variables. Are you sure that you should not be enquoting these variables for use in the function? or does the function itself return appropriately quoted text (you have not supplied the function).

in your select statements you are referencing a column using single quotes. Is this the correct nomenclature in sqlserver? If so how does the driver distinguish between a string literal and a column declarant I wonder?

in the meantime I suggest splitting the queries into individuals and then checking for errors against each as vacunita suggests.