Two queries in one

Databases
1

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
$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 
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() . "\
";

    $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

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? :slight_smile:

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

3

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

that’s a good rule to try to follow, yes

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