Accessing data from a non-WP database/table within a page content

Question

I'm trying to convert a home built (PHP / MySQL) set of web pages to WP. I've installed the theme I want and have some basic pages working OK.

However, I have a set of data within a MySQL database table (separate to the WP database) and want to query that, extracting a number of rows (50 in the example below) and displaying them in date order in a table in the WP web page content.

I've installed the exec-PHP plugin and proved that a simple works as expected.

To extract the data from my own table in my own database (on the same host system as the WP database) I'm using the following PHP. It might not be pretty but it worked fine when coded purely in php on my home grown web site (old details as shown below).

It doesn't produce anything under WP ...or it produces something totally unrelated from a WP database table for the theme (different columns, different DB table) Can anyone throw any light on what's wrong? (NB I have tried various combinations to get the loop I want working - its then that I get a load of data from a WP database table.

<table>

  <!-- Retrieve the data from the table -->
  <?php
  DEFINE ('DB_USER','a_db_user_with select_permissions');
  DEFINE ('DB_PASSWORD','users_passwd');
  DEFINE ('DB_HOST','localhost');
  DEFINE ('DB_NAME','database_name');
//****old php connection details
//$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not connect to MySQL: ' . mysql_error() );
//@mysql_select_db (DB_NAME) OR die ('Could not select the database: ' . mysql_error() );

// new connection stuff for the WP page version
$my_db = new wpdb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
$query = "SELECT col1, col2, col3, col4, col5, col6 FROM tablename ORDER BY Date_presented DESC LIMIT 50";

// Old query execution and results collections
//$result = @mysql_query ($query); 
//$num = mysql_num_rows ($result);

// New details for WP page   
$result = $my_db->get_results($query);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
 echo '<tr><td align="left" style="width: 6%; font-size: 9pt">' .  $row['col1'] . '</td>
<td align="left" style="width: 18%; font-size: 9pt">' . $row['col2'] . '</td>
<td align="left" style="width: 38%; font-size: 9pt">' . $row['col3'] . '</td>
<td align="left" style="width: 25%; font-size: 9pt">' . $row['col4'] . '</td>
<td align="left" style="width: 8%; font-size: 9pt">' .  $row['col5'] .  '</td>
</tr>';
 }
  ?>

</table>

Update #1:

Ignoring the iteration for now - I now have a simplified version but it still returns "0 in table" rather than the 2 I'd expect:

<?php 
 $my_db = new wpdb('fred','bloggs','database','localhost');
 $query = "SELECT DATE_FORMAT(col1, '%d-%b') AS Pdate, DATE_FORMAT(col1, '%Y') AS Pyear, col2, col3, col4, col5 AS fname FROM $my_db->Table ORDER BY col1 DESC LIMIT 50";
$result = $my_db->get_results($query);
echo $my_db->num_rows . ' in table ';
?>

Update #2:

OK so I found I could show_errors using:

$my_db->show_errors();

and with that can see that it doesn't like:

"FROM $my_db->table" and prefers 
"FROM table" only

and I'm getting the 2 rows I know are there.

Answer 1

<?php 
  $mydb = new wpdb('uid','passwd','database','localhost');
  $mydb->show_errors();
  $query = "SELECT DATE_FORMAT(col1, '%d-%b') AS Pdate, DATE_FORMAT(col1, '%Y') AS Pyear, col2, col3, col4, col5 AS fname FROM table ORDER BY col1 DESC LIMIT 50";
 $result = $mydb->get_results($query);
 echo $mydb->num_rows . ' in table ';
?>