Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

A Double CONCAT Probelm

Posted on 2013-02-06
11
Medium Priority
?
409 Views
Last Modified: 2013-02-10
I have query that has a double CONCAT statement in it.  Its suppose to have Option Groups with Options and each option will have a count on it of how many there are of each in the current record set.  Definitly doing something wrong!

<select name="DETAILS">
<?php
include("config.php");
$sql = mysql_query("SELECT tblDetails.DetailID as DID, tblDetails.DetailType as type,
		GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName ASC SEPARATOR '|') AS DetailName FROM tblDetails 
		CONCAT(tblDetails.DetailName,'    (', Count(tblLocDet.DetailsID),')') as DOG
		FROM (tblLocations INNER JOIN tblLocDet ON tblLocations.LocationID = tblLocDet.LocationID)
		INNER JOIN tblDetails ON tblLocDet.DetailsID = tblDetails.DetailID
		GROUP BY tblDetails.DetailType, tblDetails.DetailName, tblLocations.CityID, 
		tblLocations.AreaID, tblLocations.CuisineID
		HAVING tblLocations.CityID='16'
		AND tblLocations.AreaID='131'
		AND tblLocations.CuisineID='3'
		ORDER BY tblDetails.DetailType, tblDetails.DetailName");
$result = mysql_query($sql) or die(mysql_error());  

while ($row = mysql_fetch_assoc($result)) { 
   echo "<optgroup label='{$row['type']}'>"; 
   $DetailNames = explode('|', $row['DOG']); 
   foreach($DetailNames as $DetailName) { 
      echo "<option value='".$DetailName."'>".$DetailName."</option>"; 
   } 
   echo "</optgroup>"; 
}  
?>
</select> 

Open in new window

0
Comment
Question by:DS928
  • 7
  • 4
11 Comments
 
LVL 1
ID: 38861887
Your query has two FROM clauses.

Did you mean:

SELECT
      tblDetails.DetailID as DID,
      tblDetails.DetailType as type,
      GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName ASC SEPARATOR '|') AS DetailName,
      CONCAT(tblDetails.DetailName,'    (', Count(tblLocDet.DetailsID),')') as DOG
FROM tblLocations
        INNER JOIN tblLocDet ON tblLocations.LocationID = tblLocDet.LocationID
      INNER JOIN tblDetails ON tblLocDet.DetailsID = tblDetails.DetailID
      GROUP BY tblDetails.DetailType, tblDetails.DetailName, tblLocations.CityID,
      tblLocations.AreaID, tblLocations.CuisineID
      HAVING tblLocations.CityID='16'
      AND tblLocations.AreaID='131'
      AND tblLocations.CuisineID='3'
      ORDER BY tblDetails.DetailType, tblDetails.DetailName
0
 

Author Comment

by:DS928
ID: 38861922
I know this one is a mouthful!  It should look like this......

FEATURES
     Buffet (1)
     Wi-Fi (3)
     Waterfront (2)
MEAL PERIOD
    Lunch (2)
    Dinner(1)
    etc...

These are the two queries that I am trying to combine, maybe it will help.
Query 1
<select name="DETAILS">
<?php
include("config.php");



$sql = "SELECT DetailType AS type, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName ASC SEPARATOR '|') AS DetailName FROM tblDetails GROUP BY DetailType"; 
$result = mysql_query($sql) or die(mysql_error());  

while ($row = mysql_fetch_assoc($result)) { 
   echo "<optgroup label='{$row['type']}'>"; 
   $DetailNames = explode('|', $row['DetailName']); 
   foreach($DetailNames as $DetailName) { 
      echo "<option value='".$DetailName."'>".$DetailName."</option>"; 
   } 
   echo "</optgroup>"; 
}  
?>
</select> 

Open in new window


And to this I am trying to add the count and the criteria of City, Area and Cuisine
The Second Query
$sql = mysql_query("SELECT tblDetails.DetailID as DID, tblDetails.DetailType,
		CONCAT(tblDetails.DetailName,'    (', Count(tblLocDet.DetailsID),')') as DOG
		FROM (tblLocations INNER JOIN tblLocDet ON tblLocations.LocationID = tblLocDet.LocationID)
		INNER JOIN tblDetails ON tblLocDet.DetailsID = tblDetails.DetailID
		GROUP BY tblDetails.DetailID, tblDetails.DetailType, tblDetails.DetailName, tblLocations.CityID, 
		tblLocations.AreaID, tblLocations.CuisineID
		HAVING tblLocations.CityID='$Doggie'
		AND tblLocations.AreaID='$Kitty'
		AND tblLocations.CuisineID='$Pig'
		ORDER BY tblDetails.DetailType, tblDetails.DetailName");

Open in new window

0
 

Author Comment

by:DS928
ID: 38861948
OK this is giving me everything but the count.  How do I jam that in there?

<?php
include("config.php");
$sql = "SELECT DetailType AS type, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName ASC SEPARATOR '|')
AS DetailName FROM (tblLocations INNER JOIN tblLocDet ON tblLocations.LocationID = tblLocDet.LocationID)
INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
GROUP BY tblDetails.DetailType"; 
$result = mysql_query($sql) or die(mysql_error());  

while ($row = mysql_fetch_assoc($result)) { 
   echo "<optgroup label='{$row['type']}'>"; 
   $DetailNames = explode('|', $row['DetailName']); 
   foreach($DetailNames as $DetailName) { 
      echo "<option value='".$DetailName."'>".$DetailName."</option>"; 
   } 
   echo "</optgroup>"; 
}  
?>

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DS928
ID: 38861966
Tried This, No return......
<?php
include("config.php");
$sql = "SELECT DetailType AS type, GROUP_CONCAT(DISTINCT DetailName,'    (', Count(tblLocDet.DetailID),')') ORDER BY DetailName ASC SEPARATOR '|')
AS DetailName FROM (tblLocations INNER JOIN tblLocDet ON tblLocations.LocationID = tblLocDet.LocationID)
INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
GROUP BY tblDetails.DetailType"; 
$result = mysql_query($sql) or die(mysql_error());  

while ($row = mysql_fetch_assoc($result)) { 
   echo "<optgroup label='{$row['type']}'>"; 
   $DetailNames = explode('|', $row['DetailName']); 
   foreach($DetailNames as $DetailName) { 
      echo "<option value='".$DetailName."'>".$DetailName."</option>"; 
   } 
   echo "</optgroup>"; 
}  
?>

Open in new window

0
 
LVL 1
ID: 38861978
The CONCAT to add the count needs to be outside the GROUP_CONCAT. I don't have your database., but the query below should work:

SELECT DetailType AS type, CONCAT(GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName ASC SEPARATOR '|'), '  (', CAST(COUNT(DISTINCT DetailName) AS char(8)), ')') AS DetailName
FROM tblLocations
INNER JOIN tblLocDet ON tblLocations.LocationID = tblLocDet.LocationID
INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
GROUP BY tblDetails.DetailType
0
 

Author Comment

by:DS928
ID: 38861996
Almost.  It's giving a count, but a total like this....

FEATURES
     Buffet
     Wi-Fi
     Waterfront (3)
MEAL PERIOD
    Lunch
    Dinner(2)

It seems to be giving the total of the number of options in each group as opposed to the number of each option in the record set.

FEATURE
  Fireplace (1)
  Private Room(s) (1)
  Raw Bar (1)

MEAL PERIOD
  Brunch (1)
  Happy Hour (2)
 

SERVICE
  BYOB (1)
  Credit Cards (1)
  Delivery (1)
0
 
LVL 1
ID: 38862085
Okay, in that case we need a subquery. You can use a subquery like you would a table in a MySQL5 query.

The query would probably be something like this (assuming DetailType is in table tblLocDet and DetailId is in table tblDetail):

SELECT tblLocDet.DetailType AS type, GROUP_CONCAT(sqDetails.NumDetails ORDER BY sqDetails.NumDetails ASC SEPARATOR '|')
FROM tblLocations
INNER JOIN tblLocDet ON tblLocations.LocationID = tblLocDet.LocationID
INNER JOIN (SELECT DetailType, CONCAT(DetailName, '  (', CAST(COUNT(Distinct DetailId) AS char(8)), ')') AS NumDetails FROM tblDetails GROUP BY DetailType) AS sqDetails ON sqDetails.DetailType=tblLocDet.DetailType


Where sqDetails is a subquery delivering the DetailType and item count.
0
 

Author Comment

by:DS928
ID: 38862113
I better give you the tables.
tblLocDet
LocationID
DetailID

tblDetails
DetailID
DetailName
DetailType

tblLocations
LocationID
CityID
AreaID
CuisineID

I did this...
<?php
include("config.php");
$sql = "SELECT tblDetails.DetailType AS type, GROUP_CONCAT(sqDetails.NumDetails ORDER BY sqDetails.NumDetails ASC SEPARATOR '|')
FROM tblLocations
INNER JOIN tblLocDet ON tblLocations.LocationID = tblLocDet.LocationID
INNER JOIN (SELECT DetailType, CONCAT(DetailName, '  (', CAST(COUNT(Distinct DetailId) AS char(8)), ')') AS NumDetails FROM tblDetails GROUP BY DetailType) AS sqDetails ON sqDetails.DetailType=tblDetails.DetailType";

Open in new window


I'm not to sure about the sub-query.  Should I leave it or change the name to actual tables.  In over my head on this one!

IN PHPAdmin this gives me all but the grouping.
SELECT tblDetails.DetailType, tblDetails.DetailName,' ', Count(tblLocDet.DetailID) AS CountOfDetailID
FROM tblLocations INNER JOIN (tblLocDet INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID) ON tblLocations.LocationID = tblLocDet.LocationID
GROUP BY tblDetails.DetailType, tblDetails.DetailName, tblLocations.CityID, tblLocations.AreaID, tblLocations.CuisineID
HAVING (((tblLocations.CityID)=16) AND ((tblLocations.AreaID)=131) AND ((tblLocations.CuisineID)=3));

Open in new window

0
 
LVL 1

Accepted Solution

by:
thisstupidservicewontletmeusemyname earned 1500 total points
ID: 38862175
Yeah, the grouping is a bit difficult because of the table design, I guess. The LocDet table linking locations to details and not DetailType is what makes this a bit of a challenge.

My last try before I head to bed (using a subquery to list DetailType per Location):

SELECT tblDetails.DetailType, sqDetail.DetailNameCount
FROM tblLocations
INNER JOIN tblLocDet ON tblLocations.LocationID = tblLocDet.LocationID
INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
INNER JOIN (SELECT DetailType, CONCAT(DetailName, ' (', CAST(COUNT(DISTINCT DetailId) AS char(8)), ')') AS DetailNameCount FROM tblDetails as tD2 INNER JOIN tblLocDet as tLD2 ON tLD2.DetailID=tD2.DetailId GROUP BY tLD2.LocationID, tD2.DetailType ORDER BY tD2.DetailType) AS sqDetail ON sqDetail.DetailType = tblDetails.DetailType
ORDER BY tblDetails.DetailType, sqDetail.DetailNameCount

I apologise if this doesn't work.. Can't keep my eyes open much longer :) I hope someone else will be able to help with this.
0
 

Author Comment

by:DS928
ID: 38862187
Not working.  The problem is I need that table to hold all of the options.  Each place can have many options, well actually one of each 42 in all.  Listen thank you for your help.  Sleep tight!
0
 

Author Comment

by:DS928
ID: 38862234
Tried This.  I am getting the numbers and the grouping kinda.

FEATURES
    Wi-Fi (1)
FEATURES
   Waterfront (2)

<select name="DETAILS">
<?php
include("config.php");
$sql = "SELECT tblDetails.DetailType AS type, CONCAT(tblDetails.DetailName,' (', Count(tblLocDet.DetailID),')') AS DetailName
FROM tblLocations INNER JOIN (tblLocDet INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID) ON tblLocations.LocationID = tblLocDet.LocationID
GROUP BY tblDetails.DetailType, tblDetails.DetailName, tblLocations.CityID, tblLocations.AreaID, tblLocations.CuisineID
HAVING (((tblLocations.CityID)=16) AND ((tblLocations.AreaID)=131) AND ((tblLocations.CuisineID)=3));";


$result = mysql_query($sql) or die(mysql_error());  

while ($row = mysql_fetch_assoc($result)) { 
   echo "<optgroup label='{$row['type']}'>"; 
   $DetailNames = explode('|', $row['DetailName']); 
   foreach($DetailNames as $DetailName) { 
      echo "<option value='".$DetailName."'>".$DetailName."</option>"; 
   } 
   echo "</optgroup>"; 
}  
?>
</select> 

Open in new window

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question