My company has made an A-Z map of Bahrain. I have made a access table of all the roads. There are 5 fields; Block, Road No, Starting Page, Starting Grid, Ending Page, and Ending Grid. They are labeled as follows:
block
road_no
start_page
start_grid
end_page
end_grid
Im using PHP and COM to extract the data into HTML form with plans to turn it into PDF (then i can edit it in photoshop and give it the right layout for the published version).
Im able to extract the data no problem. But my problem comes when i try to order the data. I want to order it by block and then by road no. it should be easily done by this query:
SELECT * FROM roads ORDER BY block ASC, road_no DESC
But it doesn't work. i have tried all sorts of combinations, all to no avail.
another problem i have is this. the underscore symbol seems to throw everything off and produce errors.
Here is my entire code for the section in question:
// open up a connection to the database
$DB_Conn = new COM("ADODB.Connection") or die("Cannot start ADO");
$DB_Conn->Open("Indexing");
// execute a query
$RS_Record = $DB_Conn->Execute("SELECT * FROM roads ORDER BY block ASC, road_no DESC");
// iterate through the recordset
while (!$RS_Record->EOF)
{
// get the field data into variables
$block = $RS_Record->Fields('block');
$road_no = $RS_Record->Fields('road no');
$start_page = $RS_Record->Fields('start page');
$start_grid = $RS_Record->Fields('start grid');
$end_page = $RS_Record->Fields('end page');
$end_grid = $RS_Record->Fields('end grid');
echo "
<tr>
<td align=center>$block->value</td>
<td align=left>$road_no->value</td>
<td align=center>$start_page->value</td>
<td align=left>$start_grid->value</td>
";
if (!"$end_page->value") {
echo "
<td align=center>$start_page->value</td>
";
} else {
echo "
<td align=center>$end_page->value</td>
";
}
if (!"$end_grid->value") {
echo "
<td align=center>$start_grid->value</td>
";
} else {
echo "
<td align=center>$end_grid->value</td>
";
}
echo "
</tr>
";
// go to the next record
$RS_Record->MoveNext();
}
To show you what i mean, here are a few examples.
This line works:
$RS_Record = $DB_Conn->Execute("SELECT * FROM roads_named ORDER BY block ASC");
This line does not:
$RS_Record = $DB_Conn->Execute("SELECT * FROM roads ORDER BY block ASC, road_no DESC");
This works but doesn't order the roads:
$RS_Record = $DB_Conn->Execute("SELECT * FROM roads ORDER BY block ASC, 'road_no' DESC");
And this doesn't work (look for the underscore symbol in the while loop):
// execute a query
$RS_Record = $DB_Conn->Execute("SELECT * FROM roads ORDER BY block ASC");
// iterate through the recordset
while (!$RS_Record->EOF)
{
// get the field data into variables
$block = $RS_Record->Fields('block');
$road_no = $RS_Record->Fields('road_no');
$start_page = $RS_Record->Fields('start_page');
$start_grid = $RS_Record->Fields('start_grid');
$end_page = $RS_Record->Fields('end_page');
$end_grid = $RS_Record->Fields('end_grid');
Here are the errors i recieve when it doesn't work:
Warning: Invoke() failed: Exception occurred. in f:\websites\bahrainlocator\test\records.php on line 33
Fatal error: NULL pointer exception in f:\websites\bahrainlocator\test\records.php on line 33
So as you can see, these two problems are driving me insane. Not to mention the fact that the field in the database are labeled with an underscore symbol, but in the php code, it only works when i call the field without the underscore symbol. Any help would be greatlty appreciated. thanks in advance.
Mike
