Help with SQL query syntax

johngordon · June 16, 2013, 9:24am

Just looking for a bit of help with an SQL query.

The table structure is:

table.Itineraries
ItineraryID, Itinerary, etc

table.Activities
ActivityID, Activity, etc

And an interlinking table

table.ItineraryActivities
ItineraryID, ActivityID

And I have a page here, listing Itineraries:

http://www.goodsafariguide.net/itineraries_beta/index101.php

That I would like to include some of the Activities on.

If it was just the first two tables, and tableActivities had an ItineraryID field, I assume it would be:

SELECT * FROM Itineraries INNER JOIN Activities ON Itineraries.ItineraryID = Activities.ItineraryID

But I’m not sure what the syntax would be to achive the same thing with the interlinking table as well.

Hope that makes sense.

Thanks.

johngordon · June 16, 2013, 12:17pm

I’ve nearly got it working using:


SELECT * FROM itineraries  INNER JOIN ItineraryActivities ON ItineraryActivities.ItineraryID = itineraries.ItineraryID INNER JOIN activities ON activities.ActivityID = ItineraryActivities.ActivityID WHERE Publish_GSG = 'Yes' AND Category_Order = 1

This is displaying the Acivities I want (in this case countries) here:

http://www.goodsafariguide.net/itineraries_beta/index501.php

Except that its displaying duplicate Itineraries, one for each country.

So all I need to do is get the little table with the countries to repeat within each itinerary, but usint DW’s repeat region it doesn’t like it because its nested.

Should I be able to somehow add a repeat region around:

 
<table>
      <tr>
           <td><img src="../../itinerary_resources/icons/<?php echo $row_SecurityAssisttradeusers['Icon']; ?>" width="30" height="20" alt="Country Flag" /></td>
           <td><h2><?php echo $row_SecurityAssisttradeusers['Activity']; ?></h2></td>
      </tr>
</table>

r937 · June 16, 2013, 2:41pm

a bit hard to debug this, when country is not mentioned in any of the tables in post #1

johngordon · June 16, 2013, 2:50pm

Sorry - ‘countries’ are basically listed in the ‘activities’ table. Originally I just had a ‘country’ field in my Itineraries table, but realised that wasn’t going to work so well if an itinerary included multiple countries.

So I added them to my activities table, which I use to create a sort of profile.

If you look here:

http://www.goodsafariguide.net/itineraries_beta/

And then click on ‘Full Details’ you can see how this is working on the itinerary details pages.

My activities table has the fields:

ActivityID
Category (Countries Visited, Itinerary Categories, Accommodation, Game Parks etc)
Activity (Kenya, Tanzania, Safari Lodge, Hotel etc)
Category_Order (Because the Categories needed to be listed in a particular order)

In hindsight ‘activities’ could have been called something more like ‘itinerary_profile_keywords’ to encompass countries, accommodation, game parks etc.

Does that make more sense?

r937 · June 16, 2013, 4:26pm

now i’m even more confused

perhaps you might be so kind as to provide a mysqldump of the tables, including enough rows to illustrate the problem you’re having, and indicate which results you want to return

johngordon · June 16, 2013, 4:50pm

Thank you for having a look.

You can grab the tables here:

http://www.handprintwebdesign.co.uk/itineraries_tables.htm

Basically the main table is the Itineraries table.

Then I have an Activities table, along with an interlinking table, ItineraryActivities (literally just ItineraryID and ActivityID)

What I’m trying to achieve, is a list of Itineraries, that also includes some Activities. (the countries).

So like this:

http://www.goodsafariguide.net/itineraries_beta/

But instead of ‘Test Itinerary’ being listed twice, it would be listed once, and display Tanzania and Kenya on the right hand side.

The query I have that has gotten me this far (although its obviously wrong) is:


SELECT * FROM itineraries INNER JOIN ItineraryActivities ON ItineraryActivities.ItineraryID = itineraries.ItineraryID INNER JOIN activities ON activities.ActivityID = ItineraryActivities.ActivityID WHERE itineraries.Publish_GSG = 'Yes' AND activities.Category = 'Countries Visited'

The main repeating table is itineraries, which I’m trying to nest the list of countries inside.

So that the results look like:

Itinerary 1
Summary
Country 1
Country 2

Rather than

Itinerary 1
Summary
Country 1

Itinerary 1
Summary
Country 2

Thanks again.

r937 · June 16, 2013, 7:42pm

technically speaking, your query is just fine, and the process to show each itinerary only once, combining the countries underneath, should actually be done by your application layer language (php or whatever)

it is also possible to do it with mysql, but only when there is a single one-to-many relationship involved, and when the many data is limited to one or two columns of data…

… which is the case here, so you’re in luck

SELECT itineraries.ItineraryID
     , itineraries.UserID
     , itineraries.LodgeID
     , itineraries.Itinerary
     , itineraries.Summary
     , itineraries.Day_1
     , itineraries.Info_1
     , itineraries.Night_1
     , itineraries.Day_2
     , itineraries.Info_2
     , itineraries.Night_2
     , itineraries.Day_3
     , itineraries.Info_3
     , itineraries.Night_3
     , itineraries.Day_4
     , itineraries.Info_4
     , itineraries.Night_4
     , itineraries.Day_5
     , itineraries.Info_5
     , itineraries.Night_5
     , itineraries.Day_6
     , itineraries.Info_6
     , itineraries.Night_6
     , itineraries.Day_7
     , itineraries.Info_7
     , itineraries.Night_7
     , itineraries.Day_8
     , itineraries.Info_8
     , itineraries.Night_8
     , itineraries.Arrive
     , itineraries.Depart
     , itineraries.Duration
     , itineraries.Customise
     , itineraries.Country
     , itineraries.Airfare
     , itineraries.Supplement
     , itineraries.Persons
     , itineraries.Children
     , itineraries.Accommodation
     , itineraries.Game_Parks
     , itineraries.Currency
     , itineraries.Price
     , itineraries.Price_Supplement
     , itineraries.Details
     , itineraries.Valid_From
     , itineraries.Valid_To
     , itineraries.Publish
     , itineraries.Publish_GSG
     , itineraries.Publish_SB
     , itineraries.Publish_SL
     , itineraries.Image1_title
     , itineraries.Image2_title
     , itineraries.Image3_title
     , itineraries.Image4_title
     , itineraries.Image1
     , itineraries.Image2
     , itineraries.Image3
     , itineraries.Image4
     , itineraries.Itinerary_PDF
     , [B][COLOR="#0000FF"]GROUP_CONCAT(activities.activity) AS countries[/COLOR][/B]
  FROM itineraries 
INNER 
  JOIN ItineraryActivities 
    ON ItineraryActivities.ItineraryID = itineraries.ItineraryID 
INNER 
  JOIN activities 
    ON activities.ActivityID = ItineraryActivities.ActivityID 
   AND activities.Category = 'Countries Visited'
 WHERE itineraries.Publish_GSG = 'Yes' 
[B][COLOR="#0000FF"]GROUP
    BY itineraries.ItineraryID[/COLOR][/B]

tested okay – see itinerary 247

johngordon · June 16, 2013, 8:06pm

Thank you.

I tried that out, adding in


activities.ActivityID, activities.Icon, activities.Activity,

to the SQL.

That looks like this:

http://www.goodsafariguide.net/itineraries_beta/index801.php

So its showing the right number of itineraries, but still not showing both Tanzania and Kenya under the test itinerary ID 247.

How should I be getting it to display those?

At the minute I just have this for the output:


<?php if ($totalRows_SecurityAssisttradeusers > 0) { // Show if recordset not empty ?>
    
    	<?php do { ?>
        
        <div id="itineraries_top">
		<h1>Itinerary ID:<?php echo($row_SecurityAssisttradeusers['ItineraryID']); ?>&nbsp;<?php echo($row_SecurityAssisttradeusers['Itinerary']); ?></h1>
        </div>
        
        <div id="itineraries_bottom">

    	<table border="0" cellpadding="0" cellspacing="0" width="940px">
        <tr>
            	<td class="photo"><a href="../itinerary_info/index.php?ItineraryID=<?php echo(rawurlencode($row_SecurityAssisttradeusers['ItineraryID'])); ?>"><img src="../itinerary_resources/images_3/<?php echo $row_SecurityAssisttradeusers['Image3']; ?>" alt="<?php echo $row_SecurityAssisttradeusers['Image3_title']; ?>" title="<?php echo $row_SecurityAssisttradeusers['Image3_title']; ?>" width="120" height="90" /></a></td>
            	<td class="summary">
	        
<?php echo($row_SecurityAssisttradeusers['Summary']); ?>
               
             	<!--
                <p><a href="../itinerary_info/index.php?ItineraryID=<?php echo(rawurlencode($row_SecurityAssisttradeusers['ItineraryID'])); ?>" class="fulldetails">Full Details</a></p>
                -->
                
                </td>
            	<td class="info"><?php echo($row_SecurityAssisttradeusers['Duration']); ?> days<br /><?php echo($row_SecurityAssisttradeusers['Currency']); ?><?php echo($row_SecurityAssisttradeusers['Price']); ?></td>
            	<td class="countries">
                
                	
                    <table>
                   		<tr>
                        	<td><img src="../../itinerary_resources/icons/<?php echo $row_SecurityAssisttradeusers['Icon']; ?>" width="30" height="20" alt="Country Flag" /></td>
                          	<td><h2><?php echo $row_SecurityAssisttradeusers['Activity']; ?></h2></td>
                      	</tr>
                 	</table>
                    
                    
                    
                </td>
            	
          	</tr>
       	
      	</table>
        </div>
        <br />
        <?php } while ($row_SecurityAssisttradeusers = mysql_fetch_assoc($SecurityAssisttradeusers)); ?>

r937 · June 16, 2013, 8:39pm

“adding in” those extra columns broke the query

do some research on the GROUP_CONCAT function

basically, you’ll want to handle all the activities columns that you need inside the GROUP_CONCAT

perhaps something like this –

SELECT itineraries.ItineraryID
     , itineraries.Itinerary
     , 'other columns'
     , GROUP_CONCAT(
          CONCAT_WS(':',activities.ActivityID,activities.icon,activities.activity) 
          SEPARATOR ';'
                   ) AS countries
  FROM itineraries 
INNER 
  JOIN ItineraryActivities 
    ON ItineraryActivities.ItineraryID = itineraries.ItineraryID 
INNER 
  JOIN activities 
    ON activities.ActivityID = ItineraryActivities.ActivityID 
   AND activities.Category = 'Countries Visited'
 WHERE itineraries.Publish_GSG = 'Yes' 
GROUP
    BY itineraries.ItineraryID
    
ItineraryID  Itinerary            other columns   countries
   247       Test Itinerary  ...  other columns   57:tanzania.gif:Tanzania;56:kenya.gif:Kenya
   350       Family Self Driv...  other columns   59:namibia.gif:Namibia
   351       Luxury South Afr...  other columns   62:south_africa.gif:South Africa
   352       Escorted Wine To...  other columns   62:south_africa.gif:South Africa
   353       Zimbabwe Tailor ...  other columns   64:zimbabwe.gif:Zimbabwe

make sure you understand the two different separators

johngordon · June 16, 2013, 10:13pm

Ah, OK. I just added them, because I couldn’t see how those fields were being output on the page, which looked like this:

http://www.goodsafariguide.net/itineraries_beta/index802.php

johngordon · June 16, 2013, 10:24pm

I tried that in the SQL tab in phpMyAdmin, and see how that’s working now.

So using:


<?php echo $row_SecurityAssisttradeusers['countries']; ?>

I can get that to output on the page like this:

http://www.goodsafariguide.net/itineraries_beta/index901.php

Although if that groups those fields together, does that make it tricky to display the countries in a tabular form?

ie

Country Flag.gif | country

r937 · June 16, 2013, 10:28pm

i’m sorry, i cannot help you with your php, i’m not a php guy (in coldfusion it’s trivial)

johngordon · June 17, 2013, 7:20am

No worries - even with this, I can get it to show the countries, even if its just a comma separated list, which is the main thing.

Really appreciated you help, thank you.