Group_concat

I’m getting so close to getting things the way I want to see it and yet I’m now at a point where I don’t think I can go any further without help.

I have three tables; prices, hotelpricelink & hotel. I originally just had two, but then I had to deal with the fact that each price schedule could have multiple hotels attached to it. Thus, I made a third table to link prices and hotels togethers.

The result I’m looking to see is:

Rating | hotel1, hotel2, hotel3 | season | 3Xcost | 2Xcost | 1Xcost | Minorcost

The problem I’m having is getting hotel1, hotel2, and hotel3 to appear in one table cell as I’m getting three seperate entries instead with all the rest of the data just being duplicated.

From what I researched, I believe it’s GROUP_CONCAT that I"m needing to use. I’ve tried implementing this in the code as shown below. However, it works at combining the three entries, but the hotel name no long appears.

Hopefully that all make sense and I’m hoping there’s some expert out there who would be able to give some help.

<?php
$package_id=$_GET['package_id']; 
require_once('connectvars.php');
$dbh = mysql_connect(hostname, username, password)
	or die("Unable to connect to MySQL");
mysql_select_db ("morenotravel", $dbh);

$query = sprintf("SELECT 
prices.price_id, 
prices.package_id, 
prices.season, 
prices.triplecost, 
prices.doublecost, 
prices.singlecost, 
prices.minorcost, 
hotelpricelink.price_id, 
hotelpricelink.hotel_id, 
hotel.hotel_id,
prices.rating, 
GROUP_CONCAT(hotel.hotel_name)
FROM hotelpricelink
JOIN hotel USING (hotel_id)
JOIN prices USING (price_id)
WHERE prices.package_id='$package_id'");

$result = mysql_query($query);

if (!$result) {

    $message  = 'Invalid query: ' . mysql_error() . "\
";

    $message .= 'Whole query: ' . $query;

    die($message);

}


while ($row = mysql_fetch_assoc($result)) {


echo "
<td ><img src=\\"/moreno/v1/img/".$row['rating']."star.png\\"></td>
<td align=\\"left\\" ><a href=\\"/moreno/v1/hotel.php?hotel_id=".$row['hotel_id']."\\" target=\\"_blank\\">".$row['hotel_name']."</a></td>
<td  align=\\"center\\">".$row['season']."</td>
<td  width=\\"100px\\" align=\\"center\\">".$row['triplecost']." </td>
<td  width=\\"100px\\" align=\\"center\\">".$row['doublecost']." </td>
<td  width=\\"100px\\" align=\\"center\\">".$row['singlecost']."</td>
<td  width=\\"100px\\" align=\\"center\\">".$row['minorcost']." </td></tr>";

}
?>

Here’s a 2 entry example before Group_concat:

and after:

Try adding a GROUP_BY clause at the end of your query

Thanks for the reply. I tried changing the query to:

$query = sprintf(“SELECT
prices.price_id,
prices.package_id,
prices.season,
prices.triplecost,
prices.doublecost,
prices.singlecost,
prices.minorcost,
hotelpricelink.price_id,
hotelpricelink.hotel_id,
hotel.hotel_id,
prices.rating,
hotel.hotel_name
FROM hotelpricelink
JOIN hotel USING (hotel_id)
JOIN prices USING (price_id)
WHERE prices.package_id=‘$package_id’
GROUP BY hotelpricelink.price_id”);

But that’s just taking one of the entries and listing it.

Of course, you eliminated the GROUP_CONCAT. Put it back.

I actually had tried it with leaving it in, but that didn’t get me any different result than the first attempt, so I took it out. Here it is with both Group_Concat and Group by:

$query = sprintf("SELECT 
prices.price_id, 
prices.package_id, 
prices.season, 
prices.triplecost, 
prices.doublecost, 
prices.singlecost, 
prices.minorcost, 
hotelpricelink.price_id, 
hotelpricelink.hotel_id, 
hotel.hotel_id,
prices.rating, 
GROUP_CONCAT(hotel.hotel_name)
FROM hotelpricelink
JOIN hotel USING (hotel_id)
JOIN prices USING (price_id)
WHERE prices.package_id='$package_id'
GROUP BY hotelpricelink.price_id");

newbird, if you want real help with your query, would you please

  1. explain what each table is for, and, more importantly, describe the one-to-many relationships between the tables

  2. explain why you want a concatenated list of hotel names for each different price, when it appears that your output is designed to show one row per hotel

  1. Here’s my desired outcome (as created with an image editor). For every price package, there can be more than one hotel name within.

  1. Here is the table structure. Many ‘hotel’ to one ‘prices’.

Let me know if that info helps, or if you need anything further.

Could it just be how I present the data in the cell of my table?


<td align=\\"left\\" >
<a href=\\"/moreno/v1/hotel.php?hotel_id=".$row['hotel_id']."\\" target=\\"_blank\\">
".$row['hotel_name']."</a></td>

okay, i think i get it now :slight_smile:

SELECT prices.price_id
     , prices.package_id
     , prices.season
     , prices.triplecost
     , prices.doublecost
     , prices.singlecost
     , prices.minorcost
     , prices.rating
     , GROUP_CONCAT(hotel.hotel_name) AS hotels
  FROM prices
INNER
  JOIN hotelpricelink
    ON hotelpricelink.price_id = prices.price_id
INNER
  JOIN hotel 
    ON hotel.hotel_id = hotelpricelink.hotel_id
 WHERE prices.package_id = '$package_id'
GROUP
    BY prices.price_id

Awesome! That works like a charm. Thank you so much. I totally get it too =)

Thank you thank you!