SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    Byron Bay, NSW, Australia
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL query returning limited results

    Rudy, I hope you're out there cause I have a feeling you're the only one who could help me on this one!

    This query always worked fine UNTIL the pickup_locations table had a couple hundred rows added to it and the link table product_pickup therefore grew as each product now had more pickup_locations.

    Basically some products have over 300 pickup locations and when I run this query I get about 100 returned max. Any help would be appreciated. I have a feeling it might be a memory setting or not?

    Consider the following query:
    Code MySQL:
    SELECT products.id
    			, products.title
    			, product_locations.title as product_location
    			, products.cost
    			, products.extra_cost
    			, products.extra_cost_note
    			, products.saving_note
    			, products.picture_url
    			, COALESCE(l.locations,"") AS locations
    			, COALESCE(e.extras,"")    AS extras
    			, products.status
    	FROM products
    	LEFT OUTER JOIN 
    		( SELECT product_pickup.product_id
    				, GROUP_CONCAT(pickup_locations.location SEPARATOR "\n") AS locations
    	   			FROM product_pickup
    			INNER JOIN pickup_locations
    				ON pickup_locations.id = product_pickup.pickup_id
    			GROUP BY product_pickup.product_id ) AS l
    				ON l.product_id = products.id
    	LEFT OUTER JOIN
    		( SELECT product_extras.product_id
    				, GROUP_CONCAT(extras.title SEPARATOR "\n") AS extras
    			FROM product_extras
    			INNER JOIN extras
    				ON extras.id = product_extras.extras_id
    			GROUP BY product_extras.product_id ) AS e
    				ON e.product_id = products.id
    	LEFT OUTER JOIN
    		product_locations on products.product_location = product_locations.id
    			WHERE products.status IN ( 1 , 2 )';
    Madproject - I'm an amateur...
    What I learn from day to day

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Check out the manual. I quote
    The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.
    You could also choose to extract all locations without GROUP_CONCAT, but then you'll have to manage the "duplicate" product rows (1 for each location) in your script.
    Last edited by guido2004; Jul 5, 2011 at 00:29. Reason: typo

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    Byron Bay, NSW, Australia
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Guido! I looked everywhere for something along those lines, just didn't think of the group_concat being the limit.

    Can you re-explain what you're suggesting, I don't entirely understand in regards to extracting all locations....

    Cheers!
    Madproject - I'm an amateur...
    What I learn from day to day

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by EasyCo View Post
    Can you re-explain what you're suggesting, I don't entirely understand in regards to extracting all locations....

    Cheers!
    I wrote 'with GROUP_CONCAT' when I wanted to write 'without GROUP_CONCAT'.
    Code:
    SELECT products.id
                , products.title
                , product_locations.title as product_location
                , products.cost
                , products.extra_cost
                , products.extra_cost_note
                , products.saving_note
                , products.picture_url
                , COALESCE(l.location,"") AS locations
                , COALESCE(e.extras,"")    AS extras
                , products.status
        FROM products
        LEFT OUTER JOIN 
            ( SELECT product_pickup.product_id
                    , pickup_locations.location 
              FROM product_pickup
                INNER JOIN pickup_locations
                    ON pickup_locations.id = product_pickup.pickup_id
            ) AS l
                    ON l.product_id = products.id
        LEFT OUTER JOIN
            ( SELECT product_extras.product_id
                    , GROUP_CONCAT(extras.title SEPARATOR "\n") AS extras
                FROM product_extras
                INNER JOIN extras
                    ON extras.id = product_extras.extras_id
                GROUP BY product_extras.product_id ) AS e
                    ON e.product_id = products.id
        LEFT OUTER JOIN
            product_locations on products.product_location = product_locations.id
                WHERE products.status IN ( 1 , 2 )';
    As you can see I eliminated the GROUP_CONCAT from the first subquery. It means that if a product has two pickup locations, now it will have two rows in the query result. All columns of those two rows will have the same values (product id, title, ecc) except for the locations column. So to display the product info like you did before, you'll have to handle this new situation (more than 1 row for 1 product).

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    Byron Bay, NSW, Australia
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah gotcha... Hmm, now I've got dilemma. Either I get the data without group_concat and somehow filter is with PHP or I do two different queries and build an array...
    Madproject - I'm an amateur...
    What I learn from day to day

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    I'd go with the first solution. You have to handle it in your script anyway, so why make a second query?

    By the way, there still is the possibility of increasing the max string value for GROUP_CONCAT, isn't there? Did you try it?

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    Byron Bay, NSW, Australia
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yah, that's the way I went. I was a bit hesitant modifying default values since they're usually defaulted to a certain value for a reason.

    The particular table the group_concat is working on is 65kB so I did 100kB * 1024 and that's the max I'm going with.

    I chucked this snippet in my DB connection class.
    Code PHP:
    //Need to set the buffer higher for group_concat or else the results are truncated
    if (!mysqli_query($link, 'SET SESSION group_concat_max_len = 102400;')) {
    	$error = 'Error setting group_concat_max_len: ' . mysqli_error($link);
    	include $_SERVER['DOCUMENT_ROOT'].'/error.html.php';
    	exit();
    }

    Thank you very much for your help!
    Madproject - I'm an amateur...
    What I learn from day to day

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by EasyCo View Post
    Rudy, I hope you're out there ...
    fast asleep when you called, sorry

    besides, guido is easily as good, if not better
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    fast asleep when you called, sorry
    So you do sleep sometimes
    besides, guido is easily as good, if not better
    Thanks for the compliment, but I strongly disagree. I still have a long way to go to reach your level


Bookmarks

Posting Permissions

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