Solved

A Double CONCAT Probelm

Posted on 2013-02-06
11
385 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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now