Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    419
    Thanks
    18
    Thanked 2 Times in 2 Posts

    query to figure out

    I'm so damn close, but not quite where I want this to be. I'm trying to get pull region / country / state locations, but my result is a step or two away from what I want.

    I have this query:
    Code:
    SELECT DISTINCT continents.Name AS continent1, continent_regions.Name, country.Name AS country1, GROUP_CONCAT( states.Name
    SEPARATOR ', ' ) AS state1, item_locations_link.item_id
    FROM item_locations_link
    JOIN continents ON item_locations_link.continents = continents.ID
    JOIN continent_regions ON item_locations_link.continent_regions = continent_regions.ID
    JOIN country ON item_locations_link.country = country.ID
    JOIN states ON item_locations_link.state = states.ID
    WHERE item_locations_link.item_id =83335
    This gives me :

    Code:
    continent1      |   Name 	  |   country1  |   state1 	               | plant_id
    ----------------+-----------------+-------------+------------------------------+---------------
    N. America      | N. America      |  U.S.   | Arizona, Texas, Sonora, Sinaloa  | 83335
    What I want is:

    Code:
    continent1      |   Name 	  |   country1 |   state1 	        | plant_id
    ----------------+-----------------+-------------+-----------------------+---------------
    N. America      | N. America      | U.S. 	| Arizona, Texas        | 83335
    N. America      | N. America      | Mexico 	| Sonora, Sinaloa       | 83335

    hopefully that makes sense?

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,108
    Thanks
    2
    Thanked 326 Times in 318 Posts
    Because your query doesn't contain a GROUP BY term and you are using GROUP_CONCAT, all the rows in the result set are being consolidated into one row.

    To do what you show, you need to add GROUP BY country.Name to form a group for each different country name.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    turpentyne (12-14-2013)

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    419
    Thanks
    18
    Thanked 2 Times in 2 Posts
    aha!

    That did it! Thanks so much.


  •  

    Posting Permissions

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