A Double CONCAT Probelm

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

DS928Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
thisstupidservicewontletmeusemynameConnect With a Mentor Commented:
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
 
thisstupidservicewontletmeusemynameCommented:
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
 
DS928Author Commented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
DS928Author Commented:
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
 
DS928Author Commented:
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
 
thisstupidservicewontletmeusemynameCommented:
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
 
DS928Author Commented:
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
 
thisstupidservicewontletmeusemynameCommented:
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
 
DS928Author Commented:
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
 
DS928Author Commented:
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
 
DS928Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.