Solved

A Double CONCAT Probelm

Posted on 2013-02-06
11
397 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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 …

624 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