Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

A Double CONCAT Probelm

Posted on 2013-02-06
11
Medium Priority
?
403 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

705 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