MySQL does a nice job of finding strings within columns (fields) using SELECT, LIKE and “%” for “pattern matching”.
Details are here:
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html
If you are using something other than Version 5.0 of MySQL, you can find the right version’s manual on the same site.
Here is a SQL query that should do what you want:
SELECT p . *
FROM wp_posts p
WHERE p.post_status = 'publish'
AND p.post_type = 'post'
AND p.ID IN (
SELECT tr.object_id
FROM wp_term_relationships tr, wp_term_taxonomy tt
WHERE tt.term_id =14
AND tt.taxonomy = 'category'
AND tt.term_taxonomy_id = tr.term_taxonomy_id
)
AND (
(p.post_content LIKE '%xyz%'
OR p.post_title LIKE '%xyz%')
OR (p.id IN (
SELECT tr.object_id
FROM wp_term_relationships tr, wp_term_taxonomy tt, wp_terms t
WHERE t.name LIKE '%xyz%'
AND t.term_id = tt.term_id
AND tt.taxonomy = 'post_tag'
AND tt.term_taxonomy_id = tr.term_taxonomy_id
)
)
)
Thread Starter
mcl
(@mcl)
Wow – Many Thanks.
I will give that a try as soon as I can and when I find out how to use direct Select statements with WordPress.
When I get this working, it will open up a whole new way of working with WordPress, as I have a problem, which would be better solved by using an external MySQL Database.
Many, many thanks.
In case you are interested, here is the php I use to access an external MySQL database (ebr_ebc, in this case) from within a page template:
# wpdb is a global WordPress class based on ezSQL and used for Database manipulation
global $wpdb;
$wpdb->show_errors = true;
$wpdb->select("ebr_ebc");
$memberlist = $wpdb->get_results("SELECT * FROM people WHERE member ORDER BY namelast");
$output = "<ul>";
foreach ($memberlist as $member) {
$output .= "<li><b>$member->nameprefix $member->namefirst $member->namemiddle $member->namelast $member->namesuffix</b> ";
$output .= ".</li>\n";
};
$output .= "</ul>";
echo $output;
# Connect back to wordpress
$wpdb->select(DB_NAME);
Oops, I forgot to mention one important requirement. For this approach to work, you must Connect the database user ID specified in wp-config.php to your external database with at least SELECT access.
The WordPress database user ID is specified in this statement in wp-config.php:
define('DB_USER', 'ebr_wrdp1');
And one restriction: you cannot access the WordPress database until after the $wpdb->select(DB_NAME) statement. I get around this by storing anything I need in variables before the $wpdb->select("ebr_ebc") statement.
