1

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.

  • I should add that when it produces data from the "rogue" WP table, it chucks away all the page content display that would normally come before the WHILE loop (or foreach if I change it to such), but if I remove the loop, then the top part of the page displays OK, but no data table (of course) appears – tjb Mar 6 '16 at 0:04
  • The constants you are trying to use are already defined, those are your WordPress DB details. Also mysql_fetch_array isn't necessary, get_results returns an object or array that you can iterate over directly. I also recommend making your own plugin and adding this as a Shortcode or filter on the_content rather than use the exec-php plugin. – Milo Mar 6 '16 at 0:14
  • The table I'm accessing is in a different database to the WP database albeit on the same host, maybe I would make it easier by creating the table in the WP database, but for now (as a learning exercise) I'd rather stick with the table I already have outside the WP database. So I believe I need to redefine these ? . – tjb Mar 6 '16 at 1:14
  • You can't redefine them, that's the problem. Use unique names or variables, or just put the details directly inside wpdb(). – Milo Mar 6 '16 at 1:20
  • Got you now. Thanks.. Also I had assumed the mysql_fetch_array was incorrect but everything else I have tried hasn't worked either....you mean iterate using something like -- while ($row['col2'] <> "" ? (tho maybe thats also related to the first problem) – tjb Mar 6 '16 at 1:28
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 ';
?>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.