3 replies to this topic

[PaulD]

From the documentation

getExtTableRows($host="", $user="", $pass="", $dbase="", $fields="*", $from="", $where="", $sort="", $dir="ASC", $limit="", $push=true)

// function to get table rows from an external MySQL database
// Performance is identical to getIntTableRows plus additonal information regarding the external database.
// $host is the hostname where the MySQL database is located: $host="localhost"
// $user is the MySQL username for the external MySQL database: $user="username"
// $pass is the MySQL password for the external MySQL database: $pass="password"
// $dbase is the MySQL database name to which you wish to connect: $dbase="extdata"
// $fields should be a comma delimited string: $fields="name,email,age"
// $from is the name of the External database table that data rows will be selected from: $from="contacts"
// $where can be any optional WHERE clause: $where="parent=10 AND published=1 AND type='document'"
// $sort can be set to whichever field you wish to sort by: $sort="id"
// $dir can be set to ASCending or DESCending sort order
// $limit can be set to limit results returned: $limit="3" or $limit="10,3"
// $push = ( true = [default] array_push results into a multi-demensional array | false = return MySQL resultset )
// Returns FALSE on failure.

Here is an example to get the latest five posts from a Wordpress template.

// set the options for the query
$host="localhost"; 
$user="MY USER NAME";
$pass="MY PASSWORD";
$dbase="`MY DATABASE NAME`";
$fields="*";
$from="wp_posts";
$where="post_status='publish'"; 
$sort="post_date";
$dir="DESC";
$limit=5;
$push=true;

// run the query
$results = $etomite->getExtTableRows($host,$user,$pass,$dbase,$fields,$from,$where,$sort,$dir,$limit,$push);

Once the data is in the $results multi-dimensional array you can access it anyway you choose but an example is here.

if($results)
  {
         foreach ($results as $post)
        {
                $output.='<h2>'.$post['post_title'].'</h2>';
                $output.='<p>'.$post['post_content'].'</p>';
         }
  }
else
  {
  $output .= 'No posts found';
  }        

return $output;

There are many columns in this particular wordpress table and since the $fields is set to '*' they are all returned.

Be aware though that if you upgrade wordpress and the database structure or naming conventions change you will have to update your snippet as well. This is a bit of a pain but there isn't much you can do about that.

Hope this helps,

Paul.

PS Note that the following

$dbase="`MY DATABASE NAME`";

is encased in backticks, not " or ' but `. This is a mysql thing. My database name was of the form my-database-name and the '-' signs were being interpreted as actual 'Minus' functions, causing an error. By encasing them in backticks the name was correctly interpretted. If you do not have any strange characters in your database name, you do not need the backticks.

PPS The following bit makes sure only published posts are returned, not drafts or private posts.

$where="post_status='publish'";

Edited by PaulD, 14 July 2009 - 12:38 PM.

[Ralph]

You can also just use if($results){} as getExtTableRows() returns FALSE on failure... Not directly related to the function itself but it does help streamline the code logic...

[PaulD]

You can also just use if($results){} as getExtTableRows() returns FALSE on failure... Not directly related to the function itself but it does help streamline the code logic...

Yes, much better. Thank you.

Actually makes it a lot easier to read than the clunky !='' bit.

Have edited the above post.

Paul.

[PaulD]

The example of using this function to get wordpress posts is now a bit out of date. For wordpress 2.9.2 the 'where' statement needs the additional post_type or it will return other post types such as pages and revisions.

The where statement in here...

// set the options for the query
$host="localhost";
$user="MY USER NAME";
$pass="MY PASSWORD";
$dbase="`MY DATABASE NAME`";
$fields="*";
$from="wp_posts";
$where="post_status='publish'";
$sort="post_date";
$dir="DESC";
$limit=5;
$push=true;

Should now be

$where="post_status='publish' AND post_type='post'";

Just in case anyone tries it. I only noticed when I reused this code from here myself on a new site. Just thought I would mention it.

Paul.