is GROUP BY suitable for this?
Results 1 to 6 of 6

Thread: is GROUP BY suitable for this?

  1. #1
    Closet Nice Guy schwim's Avatar
    Join Date
    Jun 2006
    Location
    State of confusion
    Posts
    705

    is GROUP BY suitable for this?

    Hi there everyone!

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

    Code:
    $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.

    Code:
    $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!
    Warning: If you are taking advice from me, then you are in really bad shape. Let's both hope for your code's sake that someone comes along and pulls us out of this fix that I've gotten us into.

  2. #2
    Senior Member traq's Avatar
    Join Date
    Jun 2011
    Location
    so.Cal
    Posts
    949
    something like
    Code:
          (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);
    ?

  3. #3
    Closet Nice Guy schwim's Avatar
    Join Date
    Jun 2006
    Location
    State of confusion
    Posts
    705
    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.
    Warning: If you are taking advice from me, then you are in really bad shape. Let's both hope for your code's sake that someone comes along and pulls us out of this fix that I've gotten us into.

  4. #4
    Closet Nice Guy schwim's Avatar
    Join Date
    Jun 2006
    Location
    State of confusion
    Posts
    705
    It seems I'm having a problem implementing UNION:

    Code:
    $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:

    Code:
    $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?
    Last edited by schwim; 12-04-2013 at 09:34 AM.
    Warning: If you are taking advice from me, then you are in really bad shape. Let's both hope for your code's sake that someone comes along and pulls us out of this fix that I've gotten us into.

  5. #5
    Closet Nice Guy schwim's Avatar
    Join Date
    Jun 2006
    Location
    State of confusion
    Posts
    705
    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!
    Warning: If you are taking advice from me, then you are in really bad shape. Let's both hope for your code's sake that someone comes along and pulls us out of this fix that I've gotten us into.

  6. #6
    Senior Member traq's Avatar
    Join Date
    Jun 2011
    Location
    so.Cal
    Posts
    949
    Quote Originally Posted by schwim View Post
    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.

    Quote Originally Posted by schwim View Post
    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.)

    Quote Originally Posted by schwim View Post
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •