SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2009
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    two queries in one

    I'm having trouble wrapping my brain around this so hopefully someone can help get me on the right track. Here's what I'm trying to do. I have a table of tour packages and a table of categories. I then have a third table to link the two together. They look like this:




    Now for every tour package, there will be a tab on the website that will say "similar trips". This will (1) identify the category and/or categories associated with the selected package and (2)list other packages that are also assigned to that same category/categories.

    To start, I was able to get the step (1) above with the following code:

    PHP Code:
    <?php
    $package_id
    =$_GET['package_id'];
    require_once(
    'connectvars.php');
    $dbh mysql_connect(hostnameusernamepassword)
        or die(
    "Unable to connect to MySQL");
    mysql_select_db ("morenotravel"$dbh);
    $query sprintf("SELECT 
    categorypackagelink.package_id,
    categorypackagelink.category_id,
    packages.package_name,
    packages.package_id,
    category.category_id,
    category.category
    FROM category
    INNER JOIN categorypackagelink ON categorypackagelink.category_id = category.category_id
    INNER JOIN packages ON packages.package_id = categorypackagelink.package_id
    WHERE packages.package_id='
    $package_id'");


    $result mysql_query($query);

    if (!
    $result) {

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

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

        die(
    $message);

    }


    while (
    $row mysql_fetch_assoc($result)) {

    echo 
    $row['category'];
    }
    ?>
    This displays the category which is associated with that package just perfectly. My problem then comes with step (2) which is to list out other packages with that same category. I actually have no idea how to incorporate a second query based on the first.

    Anybody able to give me a little bit of direction? If any other info is needed, just let me know. Thank you in advance!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you want a couple more joins

    your requirement starts with "for every package, find related categories..."

    this is FROM packages INNER JOIN categorypackagelink INNER JOIN category

    then you want "other packages in those categories"

    you must extend the above join with INNER JOIN categorypackagelink INNER JOIN packages

    using aliases is mandatory and the rest is straightforward, i'll do the entire FROM clause and you work out the rest, okay?
    Code:
    SELECT ...
      FROM packages AS these
    INNER 
      JOIN categorypackagelink AS theselink
        ON theselink.package_id = these.package_id
    INNER 
      JOIN category
        ON category.category_id = theselink.category_id
    INNER 
      JOIN categorypackagelink AS thoselink
        ON thoselink.category_id = category.category_id
    INNER 
      JOIN packages AS those
        ON those.package_id = thoselink.package_id 
       AND those.package_id <> these.package_id
    you can actually bypass joining to the category table if you don't need to pull any column from it

    by the way, your catpkg_id column is unnecessary and should be removed
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2009
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As much as that makes sense, I think my head is going to explode lol. I'll give it a shot and let you know how I make out. Thank you so much for the quick reply and help. You rock! =)

    if catpkg_id is not needed, what would end up being primary key? There needs to be one set right?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Newbird View Post
    if catpkg_id is not needed, what would end up being primary key? There needs to be one set right?
    that's a good rule to try to follow, yes
    Code:
    CREATE TABLE categorypackagelink 
    ( category_id INTEGER NOT NULL
    , package_id INTEGER NOT NULL
    , PRIMARY KEY ( category_id, package_id )
    , INDEX reversi ( package_id, category_id )
    );
    with both of these indexes, you will actually never need to retrieve the rows of the table, as all the information you can possibly need is in each index, so they are both covering indexes
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •