is GROUP BY suitable for this?

schwim

Hi there everyone!

I've got records with three types(1, 2, 3) and I need to list the 5 latest records.

$query = "SELECT * FROM table WHERE TYPE = '1' ORDER BY date_added DESC LIMIT 5";
$result=mysql_query($query) or die('MySQL error: Failed to retrieve latest 5 records. <br />' . mysql_error() . '<hr />Query: ' . $query);
$num=mysql_num_rows($result);
if($num != '0'){
	while ($row = mysql_fetch_assoc($result)) {
		$name = $row['name'];
		$content = $row['content'];

	$display_member = $display_member . "Name: ".$name." <br />Content: ".$content."<br /><hr />";
}
}

$query2 = "SELECT * FROM table WHERE TYPE = '2' ORDER BY date_added DESC LIMIT 5";
$result2=mysql_query($query2) or die('MySQL error: Failed to retrieve latest 5 records. <br />' . mysql_error() . '<hr />Query: ' . $query2);
$num2=mysql_num_rows($result2);
if($num2 != '0'){
	while ($row2 = mysql_fetch_assoc($result2)) {
		$name = $row2['name'];
		$address = $row2['address'];
		$date_occuring = $row2['date_occuring'];

	$display_event = $display_event . "Name: ".$name." <br />Address: ".$address."<br />Date occuring: ".$date_occuring."<hr />";
}
}

$query3 = "SELECT * FROM table WHERE TYPE = '3' ORDER BY date_added DESC LIMIT 5";
$result3=mysql_query($query3) or die('MySQL error: Failed to retrieve latest 5 records. <br />' . mysql_error() . '<hr />Query: ' . $query3);
$num3=mysql_num_rows($result3);
if($num3 != '0'){
	while ($row3 = mysql_fetch_assoc($result3)) {
		$name = $row3['name'];
		$address = $row3['address'];

	$display_poi = $display_poi . "Name: ".$name." <br />Address: ".$address."<br /><hr />";
}
}

echo("

<table>
	<tr>
		<td>".$dislpay_member."</td>
		<td>".$display_event."</td>
		<td>".$display_poi."</td>
	</tr>
</table>

");

From what I understand, consolidating repetitive queries such as this is the way to go, but I just can't figure out how to do that. I thought GROUP BY would be the way to go, but Tizag and W3C's tutorials don't use it the way it seems I need to.

I thought of putting a condition inside the while loop to sort the results, but if I do that, I wouldn't be able to limit the results correctly.

$query = "SELECT * FROM table WHERE type = '1' OR type = '2' OR type = '3' ORDER BY date_added DESC LIMIT 5";
$result=mysql_query($query) or die('MySQL error: Failed to retrieve latest 5 records. <br />' . mysql_error() . '<hr />Query: ' . $query);
$num=mysql_num_rows($result);
if($num != '0'){
	while ($row = mysql_fetch_assoc($result)) {
		$type = $row['type'];

	if($type == '1'){
		$name = $row['name'];
		$content = $row['content'];

		$display_member = $display_member . "Name: ".$name." <br />Content: ".$content."<br /><hr />";
	}elseif($type == '2'){
		$name = $row['name'];
		$address = $row['address'];
		$date_occuring = $row['date_occuring'];

		$display_event = $display_event . "Name: ".$name." <br />Address: ".$address."<br />Date occuring: ".$date_occuring."<hr />";
	}elseif($type == '3'){
		$name = $row['name'];
		$address = $row['address'];

		$display_poi = $display_poi . "Name: ".$name." <br />Address: ".$address."<br /><hr />";
	}
}
}

Could someone help me figure out how to display 5 results of each type using a single query?

Thanks for your time!

traq

something like

      (SELECT * FROM `table` WHERE `type`=1 ORDER BY `date_added` DESC)
UNION (SELECT * FROM `table` WHERE `type`=2 ORDER BY `date_added` DESC)
UNION (SELECT * FROM `table` WHERE `type`=3 ORDER BY `date_added` DESC);

schwim

Thanks a bunch for taking the time to help.

Is that a more efficient method of handling queries or does it only serve to clean up the mess in my code? Google has given me an equal amount of differing answers to this.

schwim

It seems I'm having a problem implementing UNION:

$query_timebased = ("SELECT lat, lng, username, type FROM ".$ss_mm_prefix."mapdata a INNER JOIN ".$phpbb_prefix."users b USING(user_id) WHERE type ='member' AND marker_active = '1' ORDER BY marker_placed DESC LIMIT 5
					UNION
					SELECT id, name, type FROM ".$ss_poi_prefix."data WHERE type ='event' AND active = '1' ORDER BY created DESC LIMIT 5
					UNION 
					SELECT id, name, type FROM ".$ss_poi_prefix."data WHERE type ='poi' AND active = '1' ORDER BY created DESC LIMIT 5");

I get "Incorrect usage of UNION and ORDER BY". UNION ALL results in the same error.

If I bracket each separate query:

$query_timebased = ("(SELECT lat, lng, username, type FROM ".$ss_mm_prefix."mapdata a INNER JOIN ".$phpbb_prefix."users b USING(user_id) WHERE type ='member' AND marker_active = '1' ORDER BY marker_placed DESC LIMIT 5)
					UNION ALL
					(SELECT id, name, type FROM ".$ss_poi_prefix."data WHERE type ='event' AND active = '1' ORDER BY created DESC LIMIT 5)
					UNION ALL
					(SELECT id, name, type FROM ".$ss_poi_prefix."data WHERE type ='poi' AND active = '1' ORDER BY created DESC LIMIT 5)");

Then I get "The used SELECT statements have a different number of columns".

Could someone help me figure out what I'm screwing up?

schwim

Well, thanks to Google and SO, I think I've realized that UNION does not solve my issue due to differing number and types of columns.

Is there any other method of cutting down my queries that I can use or do I just need to stick with separate queries?

thanks for your time!

traq

schwim;11035905 wrote:
Is that a more efficient method of handling queries or does it only serve to clean up the mess in my code? Google has given me an equal amount of differing answers to this.

Depends. Are you really only getting 3 sets of 5 records? Have you properly indexed the columns involved? Shouldn't be a problem.

schwim;11035911 wrote:
It seems I'm having a problem implementing UNION: … I get "The used SELECT statements have a different number of columns".

As you discovered, you have to have the same columns in each statement in order to do this.

(I had assumed that you did, since your example used "SELECT *" and all the records appeared to be from the same table.)

schwim;11035913 wrote:
Is there any other method of cutting down my queries that I can use or do I just need to stick with separate queries?

You could assemble all of the records into a temporary table (I'll post an example if I can figure it out). Another option would be to use a stored procedure on the DB.

I'd probably stick with separate queries, though. If you switched to mysqli you could issue all three queries at once, and get them back from the DB in a single round-trip.