Help with INNER JOIN query

Hope someone can help with this, as this is quite an involved project for me, and I’m just about there with it.

My table structure is :

table_packages

packageID
package
packageDetails
etc

table_destinations

destinationID
destination
destinationDetails
etc

table_packagedestinations

packageID
destinationID

…so nothing that complicated.

So the idea is that I can have a package details page which shows the details of the package, along any destinations linked to that package via the packagedestinations table.

So this is the last part really - to get the page to display the destinations, but I’m missing something along the way…

This is the PHP from the header, including my INNER JOIN query…


<?php
$ParampackageID_WADApackages = "-1";
if (isset($_GET['packageID'])) {
  $ParampackageID_WADApackages = (get_magic_quotes_gpc()) ? $_GET['packageID'] : addslashes($_GET['packageID']);
}
$ParamSessionpackageID_WADApackages = "-1";
if (isset($_SESSION['WADA_Insert_packages'])) {
  $ParamSessionpackageID_WADApackages = (get_magic_quotes_gpc()) ? $_SESSION['WADA_Insert_packages'] : addslashes($_SESSION['WADA_Insert_packages']);
}
$ParampackageID2_WADApackages = "-1";
if (isset($_GET['packageID'])) {
  $ParampackageID2_WADApackages = (get_magic_quotes_gpc()) ? $_GET['packageID'] : addslashes($_GET['packageID']);
}
mysql_select_db($database_connPackages, $connPackages);
$query_WADApackages = sprintf("SELECT packageID, package, costperpax, duration, baselocation, category, dateadded, agerange, hotel, educational_tours, field_trips, corporate_outings, plant_visits, budget_package, rollingtours, teambuilding, description, offer FROM packages WHERE packageID = %s OR ( -1= %s AND packageID= %s)", GetSQLValueString($ParampackageID_WADApackages, "int"),GetSQLValueString($ParampackageID2_WADApackages, "int"),GetSQLValueString($ParamSessionpackageID_WADApackages, "int"));
$WADApackages = mysql_query($query_WADApackages, $connPackages) or die(mysql_error());
$row_WADApackages = mysql_fetch_assoc($WADApackages);
$totalRows_WADApackages = mysql_num_rows($WADApackages);

$colname_educationalDestinations = "1";
if (isset($_GET['PackageID'])) {
  $colname_educationalDestinations = (get_magic_quotes_gpc()) ? packageID : addslashes(packageID);
}
mysql_select_db($database_connPackages, $connPackages);
$query_educationalDestinations = sprintf("SELECT * FROM destinations INNER JOIN (packages INNER JOIN packagedestinations ON packages.packageID = packagedestinations.packageID) ON destinations.destinationID = packagedestinations.destinationID WHERE packages.packageID = %s ORDER BY destination ASC", GetSQLValueString($colname_educationalDestinations, "int"));
$educationalDestinations = mysql_query($query_educationalDestinations, $connPackages) or die(mysql_error());
$row_educationalDestinations = mysql_fetch_assoc($educationalDestinations);
$totalRows_educationalDestinations = mysql_num_rows($educationalDestinations);
?>

And where I’m trying to display the destinations themselves, I have :


<table>
        <tr>
       		<td>Destinations :</td>
       	</tr>
      	<?php do { ?>
        <tr>
        	<td><?php echo $row_educationalDestinations['destination']; ?></td>
        </tr>
        <?php } while ($row_educationalDestinations = mysql_fetch_assoc($educationalDestinations)); ?>
</table>

If anyone could have a quick look and help me out that would be much appreciated - not sure if its my SQL at the top, or the PHP in the page, but either way it would be good to get it working.

Thanks.

  1. That’s a hangover from test data - once its all working I’ll clear out a lot of this stuff - but presumably it wasn’t affecting any of this.

  2. Not intentional - it may be that DW created it.

  3. Again - not sure - I might pin that one on DW too.

  4. Yup - there’s a loop in the page that displays the destinations.

  5. Sorry - may have gotten confused when I thought you mentioned earlier about taking it out, as it duplicated the ON part.

  6. Didn’t realise that - I think I was originally just making sure the code was the same for each query. I follow the syntax of actual queries OK, but am less sure about the PHP syntax around them.

  7. I think its helped a great deal, even if it was a dumbass typo that started all of this!

do you mind if i make a comment about this?

you start with destinations, then join to the relationship table packagedestinations, then join to the packages table

this is the same thing i did earlier, right?

both of us should have done better :slight_smile:

we should have built up our FROM clause starting with the packages table

why? because it reinforces what the query is doing: getting destinations for a specific package (see the WHERE clause)

furthermore, you reversed the sequence of columns on the two ON clauses

my rule of thumb is:

...
INNER
  JOIN table_to_be_joined
    ON table_to_be_joined.somecolumn = table_already_mentioned.somecolumn

so the revised query would look like this –

  FROM [COLOR="Red"]packages[/COLOR]
INNER 
  JOIN [COLOR="Blue"]packagedestinations [/COLOR]
    ON [COLOR="blue"]packagedestinations[/COLOR].packageID = [COLOR="Red"]packages[/COLOR].packageID
INNER 
  JOIN [COLOR="Green"]destinations [/COLOR]
    ON [COLOR="green"]destinations[/COLOR].destinationID = [COLOR="blue"]packagedestinations[/COLOR].destinationID

WHERE [COLOR="red"]packages.packageID = 137[/COLOR]

I’ve attached a copy of the database if that helps - there’s not much in there yet.

Sorry - missed that last one - the PHP at the top of the page currently looks like :


<?php
$ParampackageID_WADApackages = "-1";
if (isset($_GET['packageID'])) {
  $ParampackageID_WADApackages = (get_magic_quotes_gpc()) ? $_GET['packageID'] : addslashes($_GET['packageID']);
}
$ParamSessionpackageID_WADApackages = "-1";
if (isset($_SESSION['WADA_Insert_packages'])) {
  $ParamSessionpackageID_WADApackages = (get_magic_quotes_gpc()) ? $_SESSION['WADA_Insert_packages'] : addslashes($_SESSION['WADA_Insert_packages']);
}
$ParampackageID2_WADApackages = "-1";
if (isset($_GET['packageID'])) {
  $ParampackageID2_WADApackages = (get_magic_quotes_gpc()) ? $_GET['packageID'] : addslashes($_GET['packageID']);
}
mysql_select_db($database_connPackages, $connPackages);
$query_WADApackages = sprintf("SELECT packageID, package, costperpax, duration, baselocation, category, dateadded, agerange, hotel, educational_tours, field_trips, corporate_outings, plant_visits, budget_package, rollingtours, teambuilding, description, offer FROM packages WHERE packageID = &#37;s OR ( -1= %s AND packageID= %s)", GetSQLValueString($ParampackageID_WADApackages, "int"),GetSQLValueString($ParampackageID2_WADApackages, "int"),GetSQLValueString($ParamSessionpackageID_WADApackages, "int"));
$WADApackages = mysql_query($query_WADApackages, $connPackages) or die(mysql_error());
$row_WADApackages = mysql_fetch_assoc($WADApackages);
$totalRows_WADApackages = mysql_num_rows($WADApackages);

mysql_select_db($database_connPackages, $connPackages);
$query_educationalDestinations = "SELECT destinations.destinationID, destination
FROM destinations
INNER JOIN packagedestinations ON packagedestinations.destinationID = destinations.destinationID
INNER JOIN packages ON packagedestinations.packageID = packages.packageID";
$educationalDestinations = mysql_query($query_educationalDestinations, $connPackages) or die(mysql_error());
$row_educationalDestinations = mysql_fetch_assoc($educationalDestinations);
$totalRows_educationalDestinations = mysql_num_rows($educationalDestinations);
?>

Just to say, I really appreciate you helping out with this. :slight_smile:

The problem is you’re not actually joining packagedestinations and packages but taking their Carthesion product (since there is no ON clause to describe how the two tables relate). Try this:

SELECT
   something
 , anything
 , just_not_the_dreaded_star
FROM
   destinations
INNER JOIN
   packagedestinations
   ON
   packagedestinations.destinationID = destinations.destinationID
INNER JOIN
   packages
   ON
  packagedestinations.packageID = packages.packageID
WHERE
  packages.packageID = 128

OK - this is very confusing - I’ve tried entering :

SELECT * FROM destinations INNER JOIN packagedestinations ON packagedestinations.destinationID = destinations.destinationID INNER JOIN packages ON packages.packageID = packagedestinations.packageID

into the SQL directly in phpMyAdmin and it returns destinations 15, 16 and 32 for either package with any destinations attached.

I tried changing it to


SELECT * FROM destinations INNER JOIN packagedestinations ON packagedestinations.destinationID = destinations.destinationID INNER JOIN packages ON packages.packageID = 128

and it still returned three records, even though there are only two with a packageID of 128 in the packagedestinations table…

:confused:

Yup. Why didn’t I think of that!? :blush:

@duklaprague the URL you gave only shows a table with


128  	Educational Tour 1  	Baguio  	Budget  	 	
137 	Educational Tour 2 	Baguio 	Budget

Are you sure you posted the correct URL?

Going back to the original query, if I put :


SELECT * FROM destinations INNER JOIN (packages INNER JOIN packagedestinations ON packages.packageID = packagedestinations.packageID) ON destinations.destinationID = packagedestinations.destinationID WHERE packages.packageID = packagedestinations.packageID ORDER BY destination ASC

into the SQL, it returns the three records instead of the correct ones…

but if I change that to


SELECT * FROM destinations INNER JOIN (packages INNER JOIN packagedestinations ON packages.packageID = packagedestinations.packageID) ON destinations.destinationID = packagedestinations.destinationID WHERE packages.packageID = 128 ORDER BY destination ASC

It returns the correct two records…

But if I enter :

Going back to the original query, if I put :


SELECT * FROM destinations INNER JOIN (packages INNER JOIN packagedestinations ON packages.packageID = packagedestinations.packageID) ON destinations.destinationID = packagedestinations.destinationID WHERE packages.packageID = packagedestinations.packageID ORDER BY destination ASC

into the SQL, it returns the three records instead of the correct ones…

but if I change that to


SELECT * FROM destinations INNER JOIN (packages INNER JOIN packagedestinations ON packages.packageID = packagedestinations.packageID) ON destinations.destinationID = packagedestinations.destinationID WHERE packages.packageID = 137 ORDER BY destination ASC

It only returns one of the two correct records…

With the WHERE part still included, and the packagedestinations table looking like :

packageID, destinationID

128, 15
128, 16
139, 16
139, 31
140, 15
140, 16
140, 31
140, 33

And the query looking like :


SELECT destinations.destinationID, destination
FROM destinations
INNER JOIN packagedestinations ON packagedestinations.destinationID = destinations.destinationID
INNER JOIN packages ON packagedestinations.packageID = packages.packageID
WHERE packages.packageID = packagedestinations.packageID

It returns each record from the packagedestinations table for every package :

http://www.travel7107.com/packageDetailsTest201.php?packageID=128

or

http://www.travel7107.com/packageDetailsTest201.php?packageID=137

Although if I replace the last packagedestinations.packageID for 128, 139 or 140 then it returns the correct records.

So its just not filtering them out on the page.

With the WHERE part removed, the results of the


SELECT destinations.destinationID, destination
FROM destinations
INNER JOIN packagedestinations ON packagedestinations.destinationID = destinations.destinationID
INNER JOIN packages ON packagedestinations.packageID = packages.packageID



query are the same on the web page, but if I replace the last ID with, eg, 128, it outputs :

16
15
16
15
16
15
16
15
16
15
16
15
16
15
16
15
16
15
16
15
16
15
16
15

Which seems a bit odd?

Then you’ve got the wrong query on your page :cool:

Could you post the code as it currently is?

That indicates that there are no destinations with destinationID 30 and 32. If they existed your query would have returned those as well.

Also, if you use INNER JOIN packages ON packagedestinations.packageID = packages.packageID there is no need to also WHERE packages.packageID = packagedestinations.packageID because those two things do exactly the same and therefore the WHERE is redundant :slight_smile:

I’ve now deleted the 137 record, so essentially its now back at the situation where each package is listing all the records from the packagedestinations table, rather than just the records where the packageIDs match.

http://www.travel7107.com/packageDetailsTest201.php?packageID=128

I tried adding a third package, so the packagedestinations table looks like :

packageID, destinationID

128, 15
128, 16
137, 30
137, 32
139, 16
139, 31


SELECT destinations.destinationID, destination
FROM destinations
INNER JOIN packagedestinations ON packagedestinations.destinationID = destinations.destinationID
INNER JOIN packages ON packagedestinations.packageID = packages.packageID
WHERE packages.packageID = packagedestinations.packageID

now returns :

15
16
16
31

So that’s what gets displayed for each package on the web page.


SELECT destinations.destinationID, destination
FROM destinations
INNER JOIN packagedestinations ON packagedestinations.destinationID = destinations.destinationID
INNER JOIN packages ON packagedestinations.packageID = packages.packageID
WHERE packages.packageID = 128

returns 15 and 16


SELECT destinations.destinationID, destination
FROM destinations
INNER JOIN packagedestinations ON packagedestinations.destinationID = destinations.destinationID
INNER JOIN packages ON packagedestinations.packageID = packages.packageID
WHERE packages.packageID = 137

draws a blank…


SELECT destinations.destinationID, destination
FROM destinations
INNER JOIN packagedestinations ON packagedestinations.destinationID = destinations.destinationID
INNER JOIN packages ON packagedestinations.packageID = packages.packageID
WHERE packages.packageID = 139

returns 16 and 31

Oh, absolutely - am sure it is only spitting out what I’ve put in. With the above, I don’t think its necessarily a case of it working for one value, but not the other, and probably more to do with the absence of one record.

It does seem that it pretty much should be working, but is doing odd things.

I’ve just deleted the records from the packagedestinations table, and created new records.

So it now looks like :

packageID, destinationID

128, 15
128, 16
137, 30
137, 32

So package 128 should list destinations 15 & 16
and package 137 should list destinations 30 & 32

When I enter the query into SQL :


SELECT destinations.destinationID, destination
FROM destinations
INNER JOIN packagedestinations ON packagedestinations.destinationID = destinations.destinationID
INNER JOIN packages ON packagedestinations.packageID = packages.packageID
WHERE packages.packageID = packagedestinations.packageID

it returns records 15 and 16, when presumably it should return all four? (weird that its now returning two instead of three records…)

If I put in


SELECT destinations.destinationID, destination
FROM destinations
INNER JOIN packagedestinations ON packagedestinations.destinationID = destinations.destinationID
INNER JOIN packages ON packagedestinations.packageID = packages.packageID
WHERE packages.packageID = 128

It returns the two expected results.

If I put in


SELECT destinations.destinationID, destination
FROM destinations
INNER JOIN packagedestinations ON packagedestinations.destinationID = destinations.destinationID
INNER JOIN packages ON packagedestinations.packageID = packages.packageID
WHERE packages.packageID = 137

It draws a blank…

I’m not sure what else I can check…?

A query either works or it doesn’t. It is hardly ever the case that it works for one value but not for another. Are you sure what you’re seeing is the query’s fault, or could it be that your expectations and the actual data are not on the same page (ie. you think a certain record exists while it doesn’t or vice versa)?

No offense, but this has lead me astray more than once, so I’m just checking :slight_smile:

there are no glaring errors in this query –

SELECT packageID
     , package
     , costperpax
     , duration
     , baselocation
     , category
     , dateadded
     , agerange
     , hotel
     , educational_tours
     , field_trips
     , corporate_outings
     , plant_visits
     , budget_package
     , rollingtours
     , teambuilding
     , description
     , offer 
  FROM packages 
 WHERE packageID = &#37;s 
    OR ( -1= %s 
     AND packageID= %s 
       )

as for the second query,

SELECT * 
  FROM destinations 
INNER 
  JOIN ( 
       packages 
INNER 
  JOIN packagedestinations 
    ON packages.packageID = packagedestinations.packageID
       ) 
    ON destinations.destinationID = packagedestinations.destinationID 
 WHERE packages.packageID = %s 
ORDER 
    BY destination ASC

i personally wouldn’t write nested joins that way, but they do work

so when i said you should test them outside of php, i meant that this is the best way to determine whther these queries are actually producing the results you want, rather than trying to figure this out from looking at the output produced by your php logic

That gives the same results as per my post above - ie with ‘128’ it returns the two correct records, but with ‘137’ it only returns one of the two correct records.

Changing it to the generic

...WHERE packages.packageID = packagedestinations.packageID

returns three out of the four records from the packagedestinations table.

  1. Make sure the many-to-many tables are correct. The packagedestinations table is correct, but the packagefeatures is not, since it references to packages 132 and 138 which do not exist …

  2. Why do you have $ParampackageID_WADApackages and
    $ParampackageID2_WADApackages? Aren’t these exactly the same? I don’t really see the need for the second variable

  3. What do you intent to do with “WHERE packageID = %s OR ( -1= %s AND packageID= %s)”? It’s very unreadable …

  4. What are the mysql_fetch_assoc’s doing in the code you posted? I assume you will have a while loop with mysql_fetch_assoc later in the page, am I correct? If so, remove them from the first part. Otherwise PHP will already set the first row to “read” so it won’t show up in the while() loop later on.

  5. Why is there no WHERE packages.packageID in the second query? That’s what you need to filter the results …

  6. You only need to perform mysql_select_db once, not for every query, unless you want to query another DB that you did for the previous query/queries.

Hope that helps :slight_smile:

@r937, you’re right, that does look a lot better!

neither are we, and you’re in the best position to find out :wink:

figure out what the variable values should be, code them into the SQL… and then run the SQL outside of php

if the query produces the data you want, then your problem is php

:slight_smile: