Columns Will Not Appears

[likelylad]

Hi

I have the following code which isn't working properly and I can't figure out what might be happening.

$myServer = "server";
$myUser = "user";
$myPass = "password";
$myDB = "db"; 
//create an instance of the  ADO connection object
$conn = new COM ("ADODB.Connection") or die("Cannot start ADO");
//define connection string, specify database driver
$connStr = "PROVIDER=SQLOLEDB;SERVER=".$myServer.";UID=".$myUser.";PWD=".$myPass.";DATABASE=".$myDB; 
  $conn->open($connStr); //Open the connection to the database
//declare the SQL statement that will query the database
$query = "SELECT * FROM tablename WHERE CUSTOMER_NUMBER=10000000";

//execute the SQL statement and return records
$rs = $conn->execute($query);

$num_columns = $rs->Fields->Count();
//echo $num_columns . "<br>";  

for ($i=0; $i < $num_columns; $i++) {

    $fld[$i] = $rs->Fields($i);
echo $fld[$i];
}

echo "<table border=2>";

while (!$rs->EOF)  //carry on looping through while there are records
{
    echo "<tr>";
echo "<td align=right>" . $fld[0]. "</td>";
echo "<td align=right>" . $fld[1]. "</td>";
echo "<td align=right>" . $fld[2]. "</td>";
echo "<td align=right>" . $fld[3]. "</td>";
echo "<td align=right>" . $fld[4]. "</td>";
echo "<td align=right>" . $fld[9]. "</td>";

    echo "</tr>";
    $rs->MoveNext(); //move on to the next record
}


echo "</table>";

//close the connection and recordset objects freeing up resources 
$rs->Close();
$conn->Close();

$rs = null;
$conn = null;

The problem is that only the first 3 columns will be produced on the web page.

If I make the following change

echo $num_columns . "<br>";

Then it shows 34 columns, which is correct, anyone got any ideas?

 

[likelylad]

OK, just a bit more information. I have figured out why it is happening but not how to solve the issue.

The 4th column in this instance is "NULL

 

[jpadie]

are you not getting columns and rows muddled up? although you are looping over the recordset you are not internally changing the $fld data.. anyway try this

<?php
$myServer = "server";
$myUser = "user";
$myPass = "password";
$myDB = "db"; 
//create an instance of the  ADO connection object
$conn = new COM ("ADODB.Connection") or die("Cannot start ADO");
//define connection string, specify database driver
$connStr = "PROVIDER=SQLOLEDB;SERVER=".$myServer.";UID=".$myUser.";PWD=".$myPass.";DATABASE=".$myDB; 
  $conn->open($connStr); //Open the connection to the database
//declare the SQL statement that will query the database
$query = "SELECT * FROM tablename WHERE CUSTOMER_NUMBER=10000000";

//execute the SQL statement and return records
$rs = $conn->execute($query);

$num_columns = $rs->Fields->Count();
//echo $num_columns . "<br>";  



echo "<table border=\"2\">\r\n";
$desiredFields = array(0, 1, 2, 3, 4, 9); //these are the fields that you want to output

while (!$rs->EOF) {//carry on looping through while there are records
	echo "\t<tr>\r\n";
	for ($i=0; $i < $num_columns; $i++) {
    	if (in_array($i, $desiredFields)){
    		echo "\t\t<td align=\"right\">" . $rs->Fields($i) . "</td>\r\n";
    	}
	}
    echo "\t</tr>\r\n";
    $rs->MoveNext(); //move on to the next record
}
echo "</table>\r\n";

//close the connection and recordset objects freeing up resources 
$rs->Close();
$conn->Close();

$rs = null;
$conn = null;
?>

 

[likelylad]

I don't know whether this was the right thing to do but I solved my problem by changing the query.

I removed the "*" and put in the fields that I needed. The fields that could have null values, I changed to

CASE WHEN fieldname Is Null THEN ' ' ELSE fieldname END AS 'fieldname'

 

[jpadie]

i can't see how that fixes things for you, given your original question. but i'm glad you've sorted things out to your satisfaction.

 

[likelylad]

Any field that the database was returning as NULL was stopping any information after it from being displayed.
In my test case the 4th record was NULL and therefore only displayed the 1st 3 records.

If I choose a different test number and the 10th field was NULL then only the first 9 records will display.

 

[jpadie]

i do not see how a null field in your database could have affected output, given the code you posted. however as i pointed out, your code was wrong in any event as you were not pulling in new data each row.