SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with SQL query syntax

    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/itine...a/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.

  2. #2
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've nearly got it working using:

    Code:
    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/itine...a/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:

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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by johngordon View Post
    Except that its displaying duplicate Itineraries, one for each country.
    a bit hard to debug this, when country is not mentioned in any of the tables in post #1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for having a look.

    You can grab the tables here:

    http://www.handprintwebdesign.co.uk/...ies_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:

    Code:
    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.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    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
         , GROUP_CONCAT(activities.activity) 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
    tested okay -- see itinerary 247
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you.

    I tried that out, adding in

    Code:
    activities.ActivityID, activities.Icon, activities.Activity,
    to the SQL.

    That looks like this:

    http://www.goodsafariguide.net/itine...a/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:

    Code:
    <?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)); ?>

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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/itine...a/index802.php

  11. #11
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried that in the SQL tab in phpMyAdmin, and see how that's working now.

    So using:

    Code:
    <?php echo $row_SecurityAssisttradeusers['countries']; ?>
    I can get that to output on the page like this:

    http://www.goodsafariguide.net/itine...a/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
    ------------------------------------
    Country Flag.gif | country

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, i cannot help you with your php, i'm not a php guy (in coldfusion it's trivial)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •