Little MySQL help

[MrXHellboy]

Little MySQL help needed... Nobody is answering on the MySQL part on the forums

 

I have this query

(
		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
		LIMIT 0,10
		)UNION(
		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
		LIMIT 0,10
		)

 

But now i want 10 total instead of 10 of each.....

 

Can someone help me out herE ?

[DavidAM]

Take the limits out of the individual queries. Then put the limit OUTSIDE the last closing parenthesis

 

(SELECT stuff FROM somewhere ...) 
UNION
(SELECT more_stuff FROM somewhere ...)
LIMIT 10;

[MrXHellboy]

I've tried already this

                         "(
		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(
		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

		)
                        LIMIT 10
		"

 

But somehow, now, it will only take the last 10 of os_ prefix. Another idea or am i doing something wrong ?

[fenway]

You want LIMIT 10 in each, and then LIMIT 10 on the outside.

[MrXHellboy]

No, i want a total of 10. sorted on date. But i did read something about a bug with UNION+LIMIT. I've tested it, and it seems that the limit outside the parentheses only affects the first SELECT query.

 

All i want is the LAST 10 (total) comments from both tables. I dont need 10 of each, just a total of 10, sorted on date.

 

[fenway]

Oh -- then the query you had is fine, but you're missing the order by with the limit in the end.

[MrXHellboy]

I am sorry to ask, but maybe you want to alter the it and show me how to do it ? Because i've tried several options and the order by outside the select query's results in a empty result set. Could you please show me ? Thanks.

[fenway]

Sure -- the idea is that you want no more than 10 from each table, and then when you have a max of 20 records, re-sort and limit again.

 

(
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 LIMIT 10
)
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 LIMIT 10
)
ORDER BY comment_date DESC LIMIT 10

[MrXHellboy]

Hi Fenway,

 

Unfortunately this is not working. Its returns a empty result set.

This is the code i have right now.

 

The if construct in the foreach just takes care that the limit is 10, because i have 20 in total. I want the if within the foreach gone and just get 10 results total. I use the ksort because somehow ORDER BY ist really working with me

		$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
		LIMIT 10
		)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
		LIMIT 10
		)
		", ARRAY_A);

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

	#print_r($arr);
	krsort($arr);
	$Count = 0;
		$CommentsList = '<ul>';
		foreach ($arr as $comment):
			if ($Count == $instance['amount'])
				break;
			$CommentsList .= '<li>'. $comment['author'] .' left a comment on <a href="'. htmlspecialchars($comment['guid']) .'">'. $comment['title'].'</a></li>';
			$Count++;
		endforeach;
		$CommentsList .= '</ul>';

	return $CommentsList;

 

Please read this if you want. http://www.mail-archive.com/sqlite-users@sqlite.org/msg55669.html

 

[fenway]

Forget about the PHP -- does the mysql query not return 10 results?

[MrXHellboy]

Unfortunately not. Like i have it now, its 20. Yours returned an empty result set and my first 20 as well.

[fenway]

What do you mean "and my first 20" -- there is no "my", there's only a single query.

 

I'm very confused.

[MrXHellboy]

I meant the first piece of code. . . . My bad...

 

However, i checked again the mysql manual, but according to them, you would be able to use the LIMIT outside the parentheses. I've tried those, i even tried to perform it on a very small table with just 2 fields but still the same result.

[fenway]

There's no possible way that a UNION of 10 + 10 with an outer limit of 10 can't return 10 records.

[MrXHellboy]

But how to do it ? I just want 10 records total. No 20, they are useless records, the time wont make the difference here with just 10 records, but i don't want wasted records.

[fenway]

But how to do it ? I just want 10 records total. No 20, they are useless records, the time wont make the difference here with just 10 records, but i don't want wasted records.

You have no choice -- you need 10 of each to get 10 combined & sorted correctly.  Don't worry about it.

[MrXHellboy]

Ok, thank you! Cheers!