efficient query

[MrXHellboy]

Hi All,

 

Currently we have a little wordpress network.... We want to get all the latest network comments.

I have stored the db prefixes in an array

$prefixx = array('delo_', 'dna_', 'lcp_', 'os_', 'wp_');

 

Now i have this loop:

	foreach ($prefixx as $prefix)
	{ 
		$comments = $wpdb->get_results("
		SELECT ". $prefix ."comments.comment_author AS author, ". $prefix ."posts.post_title AS title,".$prefix."posts.guid AS guid, ".$prefix."comments.comment_date AS date
		FROM ". $prefix ."comments, ". $prefix ."posts
		WHERE ". $prefix ."comments.comment_post_ID = ". $prefix ."posts.ID
		AND ". $prefix ."posts.post_status = 'publish'
		AND ". $prefix ."comments.comment_approved = 1
		ORDER BY ". $prefix ."comments.comment_date
		DESC
		", ARRAY_A);

		foreach ($comments as $c)
		{
			$arr[$c['date']] = $c;
		}
	}

 

The goal to get the latest 10 comments (total) from all the tables. Like this, it works fine as i get all the comments, then sort them on key... But i get them ALL.

 

Is there a more efficient way  like:

select ......

FROM table1, table2, table3 etc etc ?

[MrXHellboy]

Can someone tell me what is NOT good with this query ?

		$comments = $wpdb->get_results("
		SELECT os_comments.comment_author AS author, os_posts.post_title AS title,os_posts.guid AS guid, os_comments.comment_date AS date
		FROM os_comments, os_posts
		WHERE os_comments.comment_post_ID = os_posts.ID
		AND os_posts.post_status = 'publish'
		AND os_comments.comment_approved = 1
		ORDER BY os_comments.comment_date
		DESC
		UNION ALL
		SELECT dna_comments.comment_author AS author, dna_posts.post_title AS title,dna_posts.guid AS guid, dna_comments.comment_date AS date
		FROM dna_comments, dna_posts
		WHERE dna_comments.comment_post_ID = dna_posts.ID
		AND dna_posts.post_status = 'publish'
		AND dna_comments.comment_approved = 1
		ORDER BY dna_comments.comment_date
		DESC
		", ARRAY_A);

[fenway]

Don't double-post.