Filling the grid using PHP and SQL Server 2000

Hi guys.

How can I get to fill a grid using data from a SQL Server 2000 database and PHP? I've got one example accessing MySql, I tried to use this one but I couldn't get good results.

In this MySql example there are those code lines:
<?
// grid object name
$name = "obj";

// SQL query
$query = "select * from `table` limit 0,20";

// database connection
$connection = mysql_connect("localhost", "user", "password");
mysql_select_db("database");

// query results
$data = mysql_query($query, $connection);

// add grid to the page
echo activewidgets_grid($name, $data);
?>

And it works very well. I try to change the code as follows:

<?
// grid object name
$name = "obj";

// SQL query
$query = "select * from `table`";

// database connection
$connection = mssql_pconnect("localhost", "user", "password");
mssql_select_db("database");

// query results
$data = mssql_query($query, $connection);

// add grid to the page
echo activewidgets_grid($name, $data);
?>

But, the grid always shows the message:
No data found.

Any ideas ???

Same with Ingres on Linux with PHP. What is interesting - I can retrieve names of columns and it is shown but no actual data returned to the page (no rows). Any hints here?

in activewidgets.php

activewidgets_grid function has

mysql_num_rows
mysql_num_fields
mysql_field_name
mysql_fetch_array

that has to be translated to its similiar in mssql (i would prefer to rewrite all this to work with Adodb)

Anyone doing this? I would really apreciate if somebody share this effort

Blogs El Salvador - http://ro.nuestroblog.com

I finally got this to work using PHP 4.0.2 and MS SQL 2000 (connection via FreeTDS 5.1.

The problem exists with the mssql_field_name function not being supported. Switch this funtion to mssql_fetch_field and voila...it works perfectly!

I found a problem with using the above function that I posted earlier today. Fortunately I have fixed it as well. First to get this to work with MS SQL 2000 you need to change every function in activewidgets.php from the mysql to mssql equivalent. Then you need to change mssql_field_name to mssql_fetch_field and use the name property of this function.

the code to do this loks like the following:

$columns = "var ".$name."_columns = [\n";
for ($i=0; $i < $column_count; $i++) {
$field = @mssql_fetch_field($data, $i);
$columns .= "\"".$field->name."\", ";
}
$columns .= "\n];\n";

the complete code to get this to work (and it is working perfectly on my system) is below:

<?php

/*
include this file into your PHP page
usage example:
*/

function activewidgets_grid($name, &$data){

$row_count = @mssql_num_rows($data);
$column_count = @mssql_num_fields($data);

$columns = "var ".$name."_columns = [\n";
for ($i=0; $i < $column_count; $i++) {
$field = @mssql_fetch_field($data, $i);
$columns .= "\"".$field->name."\", ";
}
$columns .= "\n];\n";

$rows = "var ".$name."_data = [\n";
while ($result = @mssql_fetch_array($data)) {
$rows .= "[";
for ($i=0; $i < $column_count; $i++) {
$rows .= "\"".activewidgets_html($result[$i])."\", ";
}
$rows .= "],\n";
}
$rows .= "];\n";

$html = "<"."script".">\n";
$html .= $columns;
$html .= $rows;
$html .= "try {\n";
$html .= " var $name = new Active.Controls.Grid;\n";
$html .= " $name.setRowCount($row_count);\n";
$html .= " $name.setColumnCount($column_count);\n";
$html .= " $name.setDataText(function(i, j){return ".$name."_data[i][j]});\n";
$html .= " $name.setColumnText(function(i){return ".$name."_columns[i]});\n";
$html .= " document.write($name);\n";
$html .= "}\n";
$html .= "catch (error){\n";
$html .= " document.write(error.description);\n";
$html .= "}\n";
$html .= "</"."script".">\n";

return $html;
}

function activewidgets_html($msg){

$msg = addslashes($msg);
$msg = str_replace("\n", "\\n", $msg);
$msg = str_replace("\r", "\\r", $msg);
$msg = htmlspecialchars($msg);

return $msg;
}

?>

------------------

This is all of the code in my activewidgets.php file.

I don't get any output, all blank. But if I look at the row/column data it is all correct.
If I remove the <script> and </script> first and last in the $html-variable, all data is there, very nice.
Anyone out there that can give me a hint?